Thrown when a STRICT SQL/JSON path expression finds no matching item.
PostgreSQL Error 22035 (no_sql_json_item) arises when a STRICT SQL/JSONPath query returns no row. Use the LAX modifier, supply a default with COALESCE, or verify the key index to resolve the issue.
PostgreSQL Error 22035 (no_sql_json_item)
Error 22035 appears when a SQL/JSONPath function running in STRICT mode cannot locate any JSON item that satisfies the path. PostgreSQL then raises the condition no_sql_json_item instead of returning NULL.
The failure often occurs inside jsonb_path_query_first, jsonb_path_exists, or jsonb_path_query when STRICT is specified implicitly or explicitly.
Understanding the strict-vs-lax behavior is key to resolving the problem quickly.
STRICT mode tells PostgreSQL to treat “no match” as an error rather than a benign NULL. If the target key, array index, or predicate does not match, the query aborts with 22035.
Typos in keys, out-of-range array indexes, or unexpected JSON shapes trigger the condition.
Query plans that assume every row contains a value are especially vulnerable.
Switch to LAX mode by adding the keyword lax before the path, or remove the strict keyword. LAX turns missing items into NULL, allowing the statement to continue.
Alternatively, wrap the JSONPath call in COALESCE or use jsonb_path_exists to test the path before retrieval.
Correcting spelling and verifying array bounds also resolves many occurrences.
Path to a missing object key: use lax $.key or provide a default value.
Array index out of bounds: check jsonb_array_length() first or catch the error in a PL/pgSQL block.
Favor LAX for ad-hoc reporting queries where absence is acceptable.
Use STRICT only when the value must exist.
Add CHECK constraints or NOT NULL filters on JSONPath predicates to guarantee presence before strict access.
22034 (more_than_one_sql_json_item) happens when STRICT expects one item but gets many. Validate cardinality with jsonb_path_query.
22032 (invalid_json_text) indicates malformed JSON input; validate data on ingest to avoid it.
.
The jsonb_path_* functions default to STRICT inside constructors or when the keyword strict precedes the path.
If no match exists, PostgreSQL raises 22035.
Queries like jsonb_path_query_first(data,'strict $.profile.email') fail when the email field is absent.
Accessing strict $[5] on a 3-element array triggers the error because the sixth element does not exist.
Supplying an object where the query expects an array or vice versa prevents matches, leading to 22035.
.
Rows lacking the requested key or index trigger STRICT mode errors. Switch to LAX or validate existence first.
LAX prevents crashes but can hide data quality issues. Use it for optional fields and keep STRICT for required ones.
No. SQL/JSONPath and error code 22035 were introduced in PostgreSQL 12.
Galaxy’s AI copilot autocompletes JSONPath and warns about missing keys, letting you test queries interactively before running them in production.
.avif)
.avif){kind=logo}
