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.
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.
First, examine your JSON data and what you're trying to extract. Use a simple SELECT to inspect the raw JSON:
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.
If your path expression matches multiple items and you want to keep them all, wrap them in an array:
-- 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.
If you only need one specific item, use array indexing instead of wildcards:
-- 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:
-- Filter to one result
SELECT JSON_QUERY(data, '$.items[*].name ? (@ like_regex "^A")' WITH WRAPPER) FROM my_table;If you need multiple values processed individually, use jsonb_path_query_array instead:
-- 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.
When using JSON_TABLE to extract multiple nested values per column:
-- 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;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.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL