EC
22034
PostgreSQLERRORNotableData ExceptionHIGH confidence

more than one SQL/JSON item

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
  1. 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
trigger — this will error
SELECT jsonb_path_query_first('[1,2,3]'::jsonb, '$[*]');
-- $[*] matches all 3 elements

expected output

ERROR:  more than one SQL/JSON item

Fix 1

Use jsonb_path_query() to retrieve all matching items

WHEN When the path may match multiple elements.

Use jsonb_path_query() to retrieve all matching items
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 When exactly one result is expected.

Narrow the path expression to match a single element
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 documentation ↗

Class 22 — Data Exception

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors