more than one SQL/JSON item

Category: Data ExceptionVersions: Postgres 14+

What this means

SQLSTATE 22034 is raised when a SQL/JSON path expression used in a scalar context returns more than one item. The expression is expected to produce a single value but the path matches multiple elements.

Why it happens

1Using jsonb_path_query_first() or a scalar SQL/JSON context where the path matches multiple array elements

How to reproduce

SQL/JSON scalar query matching multiple elements.

trigger — this will ERROR

SELECT jsonb_path_query_first('[1,2,3]'::jsonb, '$[*]');
-- $[*] matches all 3 elements

ERROR: more than one SQL/JSON item

Fix 1: Use jsonb_path_query() to retrieve all matching items

When the path may match multiple elements.

fix

SELECT * FROM jsonb_path_query('[1,2,3]'::jsonb, '$[*]');

Why this works

jsonb_path_query() returns a set of rows, one per match, rather than requiring a single result.

Fix 2: Narrow the path expression to match a single element

When exactly one result is expected.

fix

SELECT jsonb_path_query_first('[1,2,3]'::jsonb, '$[0]'); -- first element

Why this works

Specifying a concrete index or a more specific path ensures at most one match.

Version notes

Postgres 14+SQL/JSON path functions available from Postgres 12; improved in 14+.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 22 — Data Exception

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE for SQL/JSON cardinality. Stable since Postgres 14.