\n<\/p>\n
PostgreSQL Error 22034: more than one sql json item<\/p>\n
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.<\/p>\n
Top 3 Causes<\/p>\n
1. Wildcard path in JSON_VALUE() returning multiple results<\/p>\n
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.<\/p>\n
— Triggers 22034
\nSELECT JSON_VALUE(‘{“fruits”: (“apple”, “banana”, “cherry”)}’, ‘$.fruits’);<\/p>\n
— Fix: specify an explicit index
\nSELECT JSON_VALUE(‘{“fruits”: (“apple”, “banana”, “cherry”)}’, ‘$.fruits(0)’);
\n— Result: “apple”<\/p>\n
— Fix: suppress the error gracefully
\nSELECT JSON_VALUE(
\n ‘{“fruits”: (“apple”, “banana”, “cherry”)}’,
\n ‘$.fruits’
\n NULL ON ERROR
\n);
\n— Result: NULL<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
2. JSON_QUERY() without WITH ARRAY WRAPPER on multi-value paths<\/p>\n
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.<\/p>\n
— Triggers 22034
\nSELECT JSON_QUERY(‘{“scores”: (95, 87, 76)}’, ‘$.scores’);<\/p>\n
— Fix: wrap results into a JSON array
\nSELECT JSON_QUERY(
\n ‘{“scores”: (95, 87, 76)}’,
\n ‘$.scores’
\n WITH ARRAY WRAPPER
\n);
\n— Result: (95, 87, 76)<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
3. Navigating nested array structures with simple path expressions<\/p>\n
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.<\/p>\n
— Sample nested data
\nWITH doc AS (
\n SELECT ‘{“orders”: ({“id”:1}, {“id”:2}, {“id”:3})}’::jsonb AS data
\n)<\/p>\n
— Triggers 22034 (multiple ids returned)
\n— SELECT JSON_VALUE(data::json, ‘$.orders.id’) FROM doc;<\/p>\n
— Fix: use jsonb_path_query() to return a set of rows
\nSELECT jsonb_path_query(data, ‘$.orders.id’)
\nFROM doc;<\/p>\n
— Fix: use jsonb_array_elements() for row-by-row processing
\nSELECT elem->>’id’ AS order_id
\nFROM doc, jsonb_array_elements(data->’orders’) AS elem;<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
Quick Fix Solutions<\/p>\n
Scenario
\nRecommended Fix<\/p>\n
Need only the first value
\nUse $.array(0) explicit index<\/p>\n
Need all values as JSON array
\nJSON_QUERY(… WITH ARRAY WRAPPER)<\/p>\n
Need all values as rows<\/p>\n
jsonb_path_query() or jsonb_array_elements()<\/p>\n
Want to avoid query failure
\nAdd NULL ON ERROR clause<\/p>\n
Complex nested structures
\nUse JSON_TABLE() (PostgreSQL 17+)<\/p>\n
— JSON_TABLE() for structured unnesting (PostgreSQL 17+)
\nSELECT *
\nFROM JSON_TABLE(
\n ‘{“orders”: ({“id”:1,”amt”:100},{“id”:2,”amt”:250})}’::json,
\n ‘$.orders’
\n COLUMNS (
\n order_id INT PATH ‘$.id’,
\n amount INT PATH ‘$.amt’
\n )
\n) AS jt;<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
Prevention Tips<\/p>\n
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.<\/p>\n
— Pre-flight cardinality check
\nSELECT jsonb_array_length(
\n jsonb_path_query_array(your_column, ‘$.some.path’)
\n)
\nFROM your_table
\nLIMIT 10;<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
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 \u2014 especially critical when handling externally sourced JSON data.<\/p>\n
SELECT JSON_VALUE(
\n payload::json,
\n ‘$.event.type’
\n NULL ON EMPTY
\n NULL ON ERROR
\n)
\nFROM event_log;<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
Related Errors<\/p>\n
22033 \u2013 invalid sql json subscript: bad array index in path expression<\/p>\n
22032 \u2013 invalid json text: malformed JSON, often encountered before 22034<\/p>\n
22035 \u2013 no sql json item: the opposite of 22034; path matches nothing<\/p>\n
2203A \u2013 sql json scalar required: path returns an object\/array where a scalar is expected<\/p>\n
\ud83d\udcd6 Want a more detailed guide?Check out the full in-depth version (Korean) on oraerror.com \u2014 includes detailed analysis, additional SQL examples, and prevention tips.<\/p>\n