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

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]

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;

Quick Fix Solutions

-- 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;

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;

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;

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.