The error occurs when a PostgreSQL SQL/JSON query expected a single JSON item but produced multiple items.
more_than_one_sql_json_item appears when a SQL/JSON path expression in PostgreSQL returns more than one item where only one is allowed. Trim the path or wrap results in an array to ensure a single item is produced, then rerun the query.
more_than_one_sql_json_item
PostgreSQL raises SQLSTATE 22034 when a SQL/JSON constructor such as JSON_VALUE or JSON_QUERY executes with a clause that permits only one item but the evaluated path returns more than one element.
The error protects data integrity by enforcing the SQL-JSON rule that JSON scalars must be unambiguous.
Applications that ignore this check might store or display wrong information, so fixing it quickly is essential.
JSON_VALUE with WITHOUT ARRAY WRAPPER expects exactly one scalar.
If the path matches two or more nodes PostgreSQL throws the error immediately.
JSON_QUERY with WITHOUT ARRAY WRAPPER SINGLE behaves similarly, failing when multiple JSON objects satisfy the path.
Implicit casting from JSON to text inside a SELECT list can surface the error when the path picks multiple values.
Queries generated by ORMs or AI agents often forget to limit paths, leading to multiple-item results.
First confirm how many items the path returns using JSON_QUERY with WITH ARRAY WRAPPER to inspect the full set.
Rewrite the JSON path to target a single node, or add a predicate that narrows matches to one element only.
If multiple items are valid, switch to JSON_QUERY WITH ARRAY WRAPPER or JSON_ARRAY to collect them safely.
Validate the fix by rerunning the query in Galaxy or psql; the error should disappear and results should be stable.
Accessing an array element without specifying an index often returns many nodes.
Add [0] or another index to fetch exactly one.
Filtering on a non-unique attribute such as $.users[*].role?(@ == "admin") can yield several admins. Append an additional predicate like && $.id == 1.
Using JSON_VALUE on a path that may be absent returns NULL or error depending on STRICT/DEFAULT. Wrap it in COALESCE and use DEFAULT NULL ON EMPTY.
When grouping results, unintended duplicates can map to one cell.
Use DISTINCT ON or LIMIT 1 to force a single value.
Design JSON schemas with unique keys so path expressions naturally map to single nodes.
Always test new JSON paths with JSON_QUERY and WITH CONDITIONAL WRAPPER in Galaxy before putting them in production code.
Add CHECK constraints that validate jsonb_array_length() where singleton objects are expected.
Enable statement logging in staging so repeated SQLSTATE 22034 events surface during QA, not in production.
SQLSTATE 22035 no_sql_json_item appears when the path returns zero items; supply DEFAULT ON EMPTY to avoid it.
SQLSTATE 22036 singleton_sql_json_item_required triggers when a scalar is required but an array is found; wrap the array or cast appropriately.
SQLSTATE 22037 invalid_sql_json_path_literal fires when the JSON path is malformed; correct the syntax or quote identifiers properly.
.
Yes. SQL/JSON functions operate on json and jsonb alike, and singleton rules apply equally.
No. SQL-standard behavior is enforced. Use array wrappers or rewrite the path.
No. SQL/JSON constructors were introduced in PostgreSQL 15. Earlier versions use different JSON functions.
Galaxy highlights SQLSTATE codes inline, previews JSON results, and suggests wrapping paths, reducing the chance of committing faulty queries.
.avif)
.avif){kind=logo}
