PostgreSQL 22035 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22035: no sql json item — Causes, Fixes & Prevention

What Is This Error?

PostgreSQL error code 22035 (no_sql_json_item) occurs when a SQL/JSON path expression returns no items from the target JSON value. This typically happens with functions like jsonb_path_query, jsonb_path_value, JSON_VALUE, and JSON_QUERY when the specified path does not exist in the given JSON document. It is most commonly triggered in strict mode, where missing paths raise an error rather than returning an empty result.

---

Top 3 Causes

1. Accessing a Non-Existent Key in Strict Mode

In strict mode, referencing a missing key immediately throws error 22035 instead of returning NULL.

-- Triggers 22035: key 'age' does not exist in strict mode
SELECT jsonb_path_query(
    '{"name": "Alice"}'::jsonb,
    'strict $.age'
);
-- ERROR: no SQL/JSON item

-- Fix: Use lax mode (default) instead
SELECT jsonb_path_query_first(
    '{"name": "Alice"}'::jsonb,
    'lax $.age'
);
-- Result: NULL (no error)

2. Out-of-Bounds Array Index Access

Accessing an array index that doesn't exist in strict mode raises 22035. This is common when JSON arrays vary in length across rows.

-- Triggers 22035: index 5 doesn't exist in a 2-element array
SELECT jsonb_path_query(
    '{"items": [1, 2]}'::jsonb,
    'strict $.items[5]'
);
-- ERROR: 22035

-- Fix: Use lax mode to safely return NULL
SELECT jsonb_path_query_first(
    '{"items": [1, 2]}'::jsonb,
    'lax $.items[5]'
);
-- Result: NULL

-- Safe extraction from a table with variable-length arrays
SELECT
    id,
    jsonb_path_query_first(payload, 'lax $.tags[0]') AS first_tag,
    jsonb_path_query_first(payload, 'lax $.tags[1]') AS second_tag
FROM events;

3. JSON_VALUE / JSON_QUERY with ERROR ON EMPTY

In PostgreSQL 14+, JSON_VALUE and JSON_QUERY support an ON EMPTY clause. When ERROR ON EMPTY is set (explicitly or as default behavior in certain configurations), a missing path raises 22035.

-- Triggers 22035: 'age' key is absent, ERROR ON EMPTY is set
SELECT JSON_VALUE(
    '{"name": "Charlie"}'::json,
    '$.age'
    ERROR ON EMPTY
);
-- ERROR: no SQL/JSON item

-- Fix 1: Use NULL ON EMPTY
SELECT JSON_VALUE(
    '{"name": "Charlie"}'::json,
    '$.age'
    NULL ON EMPTY
) AS age;
-- Result: NULL

-- Fix 2: Provide a default value
SELECT JSON_VALUE(
    '{"name": "Charlie"}'::json,
    '$.age'
    DEFAULT '0' ON EMPTY
) AS age;
-- Result: '0'

-- Production-ready pattern for multiple fields
SELECT
    id,
    JSON_VALUE(profile::json, '$.name'  NULL ON EMPTY) AS name,
    JSON_VALUE(profile::json, '$.email' NULL ON EMPTY) AS email,
    JSON_VALUE(profile::json, '$.age'   DEFAULT '0' ON EMPTY) AS age
FROM users;

---

Quick Fix Summary

Scenario	Fix
Using strict mode unnecessarily	Switch to lax mode
Array index out of bounds	Use lax + check length first
ERROR ON EMPTY in JSON_VALUE	Replace with NULL ON EMPTY or DEFAULT ON EMPTY

---

Prevention Tips

1. Default to lax mode and combine with COALESCE for null safety.

Never assume JSON data is always complete in production. Use lax mode as your default and pair it with COALESCE to handle missing values gracefully.

-- Recommended null-safe pattern
SELECT
    id,
    COALESCE(
        jsonb_path_query_first(data, 'lax $.price')::numeric,
        0.00
    ) AS price
FROM products;

2. Enforce required JSON keys at the database level using CHECK constraints.

Prevent malformed JSON from entering the database in the first place.

-- Enforce required keys via CHECK constraint
ALTER TABLE orders
ADD CONSTRAINT chk_required_json_keys
CHECK (
    payload ? 'order_id'
    AND payload ? 'user_id'
    AND payload ? 'items'
);

---

Related Errors

• 22034 (more_than_one_sql_json_item) — The opposite of 22035; the path returns multiple items where only one is expected.
• 22032 (invalid_json_text) — The JSON string itself is malformed and cannot be parsed.
• 22033 (invalid_sql_json_subscript) — An invalid type is used as an array subscript in a JSON path expression.

Understanding these related error codes together will significantly speed up debugging JSON-related issues in PostgreSQL.

---

📖 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.