22034 — more_than_one_sql_json_item

Source-backed reference for PostgreSQL SQLSTATE 22034.

22034

At a glance

A SQL/JSON query returned more than one item where the operation requires one. Fixed JSON_QUERY/JSON_VALUE paths report single-item or single-scalar requirements and may hint to use WITH WRAPPER.

Field	Value
SQLSTATE	`22034`
Condition	`more_than_one_sql_json_item`
Status	`active`
Known present by	`12.0`
Locked snapshots	`12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3`
Macros	`ERRCODE_MORE_THAN_ONE_SQL_JSON_ITEM`
Aliases	`—`

Messages

The no-wrapper cardinality guards use these primary texts:

Operation	Primary and hint
`JSON_QUERY` column without wrapper	`JSON path expression for column "%s" must return single item when no wrapper is requested`; HINT: `Use the WITH WRAPPER clause to wrap SQL/JSON items into an array.`
`JSON_QUERY` without a column name	`JSON path expression in JSON_QUERY must return single item when no wrapper is requested`; HINT: `Use the WITH WRAPPER clause to wrap SQL/JSON items into an array.`
`JSON_VALUE` with more than one item	`JSON path expression in JSON_VALUE must return single scalar item`

Meaning

The fixed JSON_QUERY path collects the complete SQL/JSON result list and counts it before applying the wrapper mode. Without a wrapper, more than one item raises 22034; unconditional or conditional WITH WRAPPER turns the sequence into an array according to its mode. JSON_VALUE uses the same code when more than one item is returned, because it requires one scalar. Exactly one non-scalar item is a separate 2203F condition, and zero items set the empty flag for ON EMPTY handling. Lax auto-unwrapping can change the result cardinality; silent or non-ERROR ON ERROR behavior can suppress an evaluation error before the cardinality guard is reported.

Diagnosis

Record the operation (JSON_QUERY or JSON_VALUE), wrapper clause, column name, and number of items produced by the path. For JSON_QUERY, use WITH WRAPPER only when an array result is intended; for JSON_VALUE, reduce the path to one scalar rather than wrapping. Distinguish multiple items (22034), one non-scalar item (2203F), and no items handled by ON EMPTY/22035. Check strict/lax and silent settings because they affect the sequence seen by this guard.

Response

Change the path or filter to return one required item, or add the documented wrapper when the consumer expects an array. For JSON_VALUE, also ensure the one item is scalar. If the operation uses an ON ERROR behavior, keep its fallback consistent with the intended cardinality rather than treating NULL as proof that the path returned one item. If an ERROR occurred in an explicit transaction, roll back or roll back to the existing savepoint before retrying; autocommit can retry the corrected expression.

Versions

The locked catalogue records this condition from 12.0; the fixed SQL/JSON cardinality paths here are PostgreSQL 18.6. No natural runtime observation is claimed for this page.

22033, 22035

Sources

JSON_QUERY wrapper and multi-item guards are src/backend/utils/adt/jsonpath_exec.c#L3880-4005. JSON_VALUE’s multiple-item and scalar distinction is #L4008-4070. The structured evidence record retains the wrapper, cardinality, and hint roles; no natural runtime was run.