PostgreSQL 22035 Error: Causes and Solutions Complete Guide

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.