PostgreSQL Error 22035: no_sql_json_item
PostgreSQL error code 22035 (no_sql_json_item) occurs when a SQL/JSON path expression fails to find a matching item within the target JSON document. This typically happens when using JSON path functions like jsonb_path_value() or jsonb_path_query() with a path that doesn't exist in the given JSON data. It is most commonly triggered in strict mode or when mandatory single-value returns are expected.
Top 3 Causes
1. Accessing a Non-Existent JSON Key
Referencing a key or nested path that simply does not exist in the JSON document is the most frequent cause.
-- This will raise ERROR 22035
SELECT jsonb_path_value(
'{"user": {"name": "Alice"}}'::jsonb,
'$.user.address.city'
);
-- Safe fix: use jsonb_path_query_first (returns NULL instead of error)
SELECT jsonb_path_query_first(
'{"user": {"name": "Alice"}}'::jsonb,
'$.user.address.city'
) AS city;
-- Returns: NULL
-- Or check existence first
SELECT jsonb_path_exists(
'{"user": {"name": "Alice"}}'::jsonb,
'$.user.address.city'
) AS path_exists;
-- Returns: false
2. Out-of-Bounds Array Index Access
Accessing an array element beyond its actual length will trigger this error.
-- ERROR: array only has 3 elements, index 5 doesn't exist
SELECT jsonb_path_value(
'{"items": [10, 20, 30]}'::jsonb,
'$.items[5]'
);
-- Safe fix: use jsonb_path_query_first
SELECT jsonb_path_query_first(
'{"items": [10, 20, 30]}'::jsonb,
'$.items[5]'
) AS result;
-- Returns: NULL
-- Check array length before access
SELECT jsonb_array_length('{"items": [10, 20, 30]}'::jsonb -> 'items') AS len;
-- Returns: 3
3. Using strict Mode with Mismatched Structure
When strict mode is explicitly used, any structural mismatch in the path immediately raises an error instead of returning empty results.
-- ERROR in strict mode: treating an object as an array
SELECT jsonb_path_value(
'{"order": {"id": 42}}'::jsonb,
'strict $.order[*].id'
);
-- Fix: use lax mode (default behavior)
SELECT jsonb_path_query(
'{"order": {"id": 42}}'::jsonb,
'lax $.order[*].id'
) AS order_id;
-- Returns: 42
Quick Fix Solutions
The fastest and most reliable fix is to replace jsonb_path_value() with jsonb_path_query_first(), which returns NULL instead of raising an error when no item is found. For production systems, wrapping JSON path access in a reusable safe function is highly recommended:
CREATE OR REPLACE FUNCTION safe_json_value(
p_data jsonb,
p_path text
) RETURNS jsonb AS $$
BEGIN
RETURN jsonb_path_query_first(p_data, p_path::jsonpath);
EXCEPTION
WHEN SQLSTATE '22035' THEN RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Usage
SELECT safe_json_value('{"a": 1}'::jsonb, '$.b') AS result;
-- Returns: NULL (no error)
Prevention Tips
Use jsonb_path_query_first() as your default instead of jsonb_path_value() whenever a missing path is a realistic possibility. This single habit eliminates the majority of 22035 errors in production.
Enforce JSON structure at insert time using CHECK constraints to guarantee required keys always exist:
CREATE TABLE profiles (
id SERIAL PRIMARY KEY,
data jsonb NOT NULL,
CONSTRAINT chk_json_structure CHECK (
(data ? 'name') AND (data ? 'email')
)
);
By validating JSON structure on write rather than on read, you prevent malformed documents from ever entering your database, making downstream path queries far more predictable and reliable.
📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.













