How to fix Singleton SQL/JSON item required in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 92 views

PostgreSQL throws this error when JSON_QUERY or JSON_VALUE receives multiple items but expects a single item. Use WITH WRAPPER clause or filter your JSON path to return exactly one item.

Quick fix

Fixes 22038

SELECT my_json_column FROM my_table WHERE id = 1;
  1. Understand your JSON structure and query intent
  2. Add WITH WRAPPER to JSON_QUERY if you expect multiple results
  3. Refine your JSON path to return exactly one item
  4. Use jsonb_path_query functions with aggregation for complex cases
postgresql — error
SELECT my_json_column FROM my_table WHERE id = 1;
Singleton SQL/JSON item required

What this error means

This error occurs in PostgreSQL SQL/JSON functions when a JSON path expression returns multiple items but the function requires exactly one item. JSON_VALUE always expects a single scalar value, while JSON_QUERY without a wrapper also requires a singleton result. The error is PostgreSQL's way of preventing ambiguous JSON extraction that would lose data. When you query JSON data with a path expression that matches multiple elements (like using wildcard $[*] without aggregation), you need to either refine your query to select one item specifically, or wrap the results into an array.

Typical symptoms

  • ERROR: Singleton SQL/JSON item required when executing JSON_QUERY or JSON_VALUE
  • Query works with single JSON items but fails on arrays or objects with multiple matching fields
  • SQLSTATE 22038 error code in your database logs
  • Error occurs when using JSON path expressions with wildcards or multiple array indices

Common causes

  1. Using JSON_VALUE with a path expression that returns multiple items
  2. Using JSON_QUERY without WITH WRAPPER when the path matches multiple array elements
  3. Wildcard expressions ($[*] or $.*) without proper filtering or aggregation
  4. JSON path filter expressions that still return multiple results
  5. Using JSON_TABLE column definitions without WITH WRAPPER when expecting multiple matches

How to fix "Singleton SQL/JSON item required"

1Understand your JSON structure and query intent

First, examine your JSON data and what you're trying to extract. Use a simple SELECT to inspect the raw JSON:

sql
SELECT my_json_column FROM my_table WHERE id = 1;

This helps you understand whether your path expression truly should return one or multiple items.

2Add WITH WRAPPER to JSON_QUERY if you expect multiple results

If your path expression matches multiple items and you want to keep them all, wrap them in an array:

sql
-- Before (fails with singleton error)
SELECT JSON_QUERY(data, '$.items[*].name') FROM my_table;

-- After (succeeds, returns array)
SELECT JSON_QUERY(data, '$.items[*].name' WITH WRAPPER) FROM my_table;

WITH WRAPPER converts multiple results into a JSON array. Use WITH CONDITIONAL WRAPPER to only wrap if multiple items are returned.

3Refine your JSON path to return exactly one item

If you only need one specific item, use array indexing instead of wildcards:

sql
-- Before (wildcard returns all)
SELECT JSON_VALUE(data, '$.items[*].name') FROM my_table;

-- After (specific index)
SELECT JSON_VALUE(data, '$.items[0].name') FROM my_table;

Or use a filter expression:

sql
-- Filter to one result
SELECT JSON_QUERY(data, '$.items[*].name ? (@ like_regex "^A")' WITH WRAPPER) FROM my_table;
4Use jsonb_path_query functions with aggregation for complex cases

If you need multiple values processed individually, use jsonb_path_query_array instead:

sql
-- Returns array of values
SELECT jsonb_path_query_array(data, '$.items[*].name') FROM my_table;

-- Or use jsonb_path_query to get individual rows
SELECT jsonb_path_query(data, '$.items[*].name') FROM my_table;

These functions handle multiple results gracefully without requiring the singleton constraint.

5For JSON_TABLE, add WITH WRAPPER to column definitions

When using JSON_TABLE to extract multiple nested values per column:

sql
-- Before (fails if items[*].tags returns multiple)
SELECT jt.* FROM my_table,
 JSON_TABLE(data, '$.items[*]' COLUMNS (
   name text PATH '$.name',
   tags text PATH '$.tags[*]'
 )) AS jt;

-- After (wrap arrays)
SELECT jt.* FROM my_table,
 JSON_TABLE(data, '$.items[*]' COLUMNS (
   name text PATH '$.name',
   tags text PATH '$.tags[*]' WITH WRAPPER
 )) AS jt;

Advanced notes

The singleton constraint exists because SQL functions must return deterministic, non-ambiguous results. In strict mode (strict $...), PostgreSQL is stricter about structural requirements and will error earlier. In lax mode (lax $..., the default), PostgreSQL tries to adapt the structure but still enforces the singleton rule for JSON_VALUE and unwrapped JSON_QUERY. If you're unsure whether to use WITH WRAPPER or refine your query, test both approaches: WITH WRAPPER preserves all results as an array, while filtering preserves your original function contract but with fewer results. For performance-sensitive queries on large JSON documents, consider using jsonb_path_exists with @? or @@ operators first to verify results exist before querying.

Frequently asked questions

What does "22038" mean?
This error occurs in PostgreSQL SQL/JSON functions when a JSON path expression returns multiple items but the function requires exactly one item. JSON_VALUE always expects a single scalar value, while JSON_QUERY without a wrapper also requires a singleton result. The error is PostgreSQL's way of preventing ambiguous JSON extraction that would lose data.
How do I fix "22038"?
PostgreSQL throws this error when JSON_QUERY or JSON_VALUE receives multiple items but expects a single item. Use WITH WRAPPER clause or filter your JSON path to return exactly one item.
What causes "22038"?
Using JSON_VALUE with a path expression that returns multiple items Using JSON_QUERY without WITH WRAPPER when the path matches multiple array elements Wildcard expressions ($[] or $.) without proper filtering or aggregation
Is the "22038" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading