PostgreSQL Error 22034: more than one sql json item
PostgreSQL error code 22034 (more than one sql json item) occurs when a SQL/JSON function such as JSON_VALUE() or JSON_QUERY() encounters a JSON path expression that returns more than one item, while the function context expects exactly one. This error became more prevalent with the introduction of SQL-standard JSON functions in PostgreSQL 15 and later.
Top 3 Causes
1. Wildcard path in JSON_VALUE() returning multiple results
JSON_VALUE() strictly requires a single scalar return value. Using a wildcard like $across an array will match multiple elements and immediately trigger error 22034.
— Triggers 22034
SELECT JSON_VALUE(‘{“fruits”: (“apple”, “banana”, “cherry”)}’, ‘$.fruits’);
— Fix: specify an explicit index
SELECT JSON_VALUE(‘{“fruits”: (“apple”, “banana”, “cherry”)}’, ‘$.fruits(0)’);
— Result: “apple”
— Fix: suppress the error gracefully
SELECT JSON_VALUE(
‘{“fruits”: (“apple”, “banana”, “cherry”)}’,
‘$.fruits’
NULL ON ERROR
);
— Result: NULL
Enter fullscreen mode
Exit fullscreen mode
2. JSON_QUERY() without WITH ARRAY WRAPPER on multi-value paths
JSON_QUERY() also fails when a path resolves to multiple independent values and no wrapper option is provided to consolidate them into a single JSON array.
— Triggers 22034
SELECT JSON_QUERY(‘{“scores”: (95, 87, 76)}’, ‘$.scores’);
— Fix: wrap results into a JSON array
SELECT JSON_QUERY(
‘{“scores”: (95, 87, 76)}’,
‘$.scores’
WITH ARRAY WRAPPER
);
— Result: (95, 87, 76)
Enter fullscreen mode
Exit fullscreen mode
3. Navigating nested array structures with simple path expressions
Deeply nested JSON arrays compound the cardinality problem at every path step. Using JSON_VALUE() or JSON_QUERY() on paths that traverse multiple array levels without index constraints will almost always produce multiple results.
— Sample nested data
WITH doc AS (
SELECT ‘{“orders”: ({“id”:1}, {“id”:2}, {“id”:3})}’::jsonb AS data
)
— Triggers 22034 (multiple ids returned)
— SELECT JSON_VALUE(data::json, ‘$.orders.id’) FROM doc;
— Fix: use jsonb_path_query() to return a set of rows
SELECT jsonb_path_query(data, ‘$.orders.id’)
FROM doc;
— Fix: use jsonb_array_elements() for row-by-row processing
SELECT elem->>’id’ AS order_id
FROM doc, jsonb_array_elements(data->’orders’) AS elem;
Enter fullscreen mode
Exit fullscreen mode
Quick Fix Solutions
Scenario
Recommended Fix
Need only the first value
Use $.array(0) explicit index
Need all values as JSON array
JSON_QUERY(… WITH ARRAY WRAPPER)
Need all values as rows
jsonb_path_query() or jsonb_array_elements()
Want to avoid query failure
Add NULL ON ERROR clause
Complex nested structures
Use JSON_TABLE() (PostgreSQL 17+)
— JSON_TABLE() for structured unnesting (PostgreSQL 17+)
SELECT *
FROM JSON_TABLE(
‘{“orders”: ({“id”:1,”amt”:100},{“id”:2,”amt”:250})}’::json,
‘$.orders’
COLUMNS (
order_id INT PATH ‘$.id’,
amount INT PATH ‘$.amt’
)
) AS jt;
Enter fullscreen mode
Exit fullscreen mode
Prevention Tips
Always verify path cardinality before using scalar JSON functions.Before deploying queries with JSON path expressions into production, use jsonb_path_query_array() to check how many items a path returns. If the count exceeds one, switch to a set-returning function or add WITH ARRAY WRAPPER.
— Pre-flight cardinality check
SELECT jsonb_array_length(
jsonb_path_query_array(your_column, ‘$.some.path’)
)
FROM your_table
LIMIT 10;
Enter fullscreen mode
Exit fullscreen mode
Always declare explicit error and empty behavior clauses.Never rely on default behavior for SQL/JSON functions. Explicitly specifying NULL ON ERROR and NULL ON EMPTY prevents a single malformed or unexpectedly multi-valued JSON document from failing an entire query batch — especially critical when handling externally sourced JSON data.
SELECT JSON_VALUE(
payload::json,
‘$.event.type’
NULL ON EMPTY
NULL ON ERROR
)
FROM event_log;
Enter fullscreen mode
Exit fullscreen mode
Related Errors
22033 – invalid sql json subscript: bad array index in path expression
22032 – invalid json text: malformed JSON, often encountered before 22034
22035 – no sql json item: the opposite of 22034; path matches nothing
2203A – sql json scalar required: path returns an object/array where a scalar is expected
📖 Want a more detailed guide?Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.
Leave a Reply