This error occurs when a JSON_VALUE() function or similar SQL/JSON scalar function returns multiple items instead of the expected single value. It typically happens when your JSON path expression matches multiple array elements or objects.
PostgreSQL's SQL/JSON functions like JSON_VALUE() are designed to extract and return a single scalar value from a JSON document. When the path expression you provide matches more than one item in the JSON structure—such as multiple array elements—PostgreSQL raises this error because it cannot coerce multiple values into a single scalar result. This is a safety mechanism to prevent data loss and ambiguous operations. The error code 22034 (more_than_one_sql_json_item) is part of PostgreSQL's SQL/JSON error handling system, which distinguishes between structural errors, type mismatches, and item count violations.
First, understand what your JSON data looks like and which path expression is causing the error. For example:
-- This will fail: "$[*]" matches ALL array elements
SELECT JSON_VALUE(
'[1, 2, 3]'::jsonb,
'$[*]' -- Returns 3 items, but JSON_VALUE expects 1
);
-- ERROR: More than one SQL/JSON itemDetermine whether you actually need a single value or if you need to handle multiple values.
If you only need one specific element from an array, use explicit array indexing instead of the wildcard operator:
-- Correct: Get the first element
SELECT JSON_VALUE(
'[1, 2, 3]'::jsonb,
'$[0]' -- Returns the first item only
);
-- Result: 1
-- Get a specific index
SELECT JSON_VALUE(
'[{"name": "Alice"}, {"name": "Bob"}]'::jsonb,
'$[1].name' -- Returns just Bob
);If you need to retrieve multiple items or complex structures, use JSON_QUERY() instead of JSON_VALUE():
-- JSON_VALUE: Single scalar value only
SELECT JSON_VALUE(
'[1, 2, 3]'::jsonb,
'$[0]' -- Works: returns 1
);
-- JSON_QUERY: Any JSON structure
SELECT JSON_QUERY(
'[1, 2, 3]'::jsonb,
'$[*]' -- Works: returns [1,2,3]
);JSON_QUERY() can return objects, arrays, and multiple values without throwing errors.
For extracting multiple items as separate rows, use jsonb_path_query() or jsonb_path_query_array():
-- Returns multiple rows, one per array element
SELECT jsonb_path_query(
'[{"id":1}, {"id":2}, {"id":3}]'::jsonb,
'$[*].id'
);
-- Result: 1, 2, 3 (3 separate rows)
-- For set-of operations
SELECT jsonb_path_query(
'["apple", "banana", "cherry"]'::jsonb,
'$[*]',
silent => true -- Suppress errors if needed
);These functions return a SET, not a single value, so they're appropriate for multiple items.
If you're uncertain whether your path will return single or multiple items, use the ON ERROR clause:
-- Return default value on error
SELECT JSON_VALUE(
'[1, 2, 3]'::jsonb,
'$[*]',
DEFAULT -1 ON ERROR
);
-- Instead of raising error, returns -1
-- Return NULL on error (default behavior)
SELECT JSON_VALUE(
'[1, 2, 3]'::jsonb,
'$[*]',
NULL ON ERROR
);This allows your application to handle edge cases without crashing.
PostgreSQL's SQL/JSON path language has two modes: lax (default) and strict. In lax mode, structural errors are suppressed and converted to empty sequences. In strict mode, errors are raised immediately. You can use strict mode to catch these issues early:
SELECT JSON_VALUE(
'[1, 2, 3]'::jsonb,
'strict $[*]'
);
-- Explicitly throws error in strict modeWhen dealing with nested JSON structures, remember that wildcards like $[*] and $.* always return multiple items. Use explicit indexing or the jsonb_path_query() family of functions instead. For complex filtering, consider whether you actually need SQL/JSON path expressions or if simpler operators like -> and ->> would suffice. Also note that JSON_VALUE() always removes quotes from returned scalar strings (equivalent to OMIT QUOTES), while JSON_QUERY() can preserve them with KEEP QUOTES.
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