PostgreSQL 2203C Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2203C: sql_json_object_not_found

PostgreSQL error code 2203C (sql_json_object_not_found) occurs when a SQL/JSON path expression fails to locate the specified key or object within a JSON document. This error is thrown by SQL/JSON functions such as JSON_VALUE, JSON_QUERY, JSON_TABLE, and related functions introduced in PostgreSQL 16 as part of the ISO SQL standard. It typically surfaces when the ERROR ON EMPTY behavior is triggered — either explicitly or as the function default.

Top 3 Causes

1. Accessing a Non-Existent Key with ERROR ON EMPTY

The most common cause is querying a JSON key that simply doesn't exist in the document while the function is configured to raise an error when nothing is found.

-- Triggers 2203C error
SELECT JSON_VALUE(
    '{"name": "Alice"}'::json,
    '$.age'
    ERROR ON EMPTY
);

-- Fix: Use NULL ON EMPTY or DEFAULT ON EMPTY
SELECT JSON_VALUE(
    '{"name": "Alice"}'::json,
    '$.age'
    NULL ON EMPTY
) AS age;
-- Returns: NULL

SELECT JSON_VALUE(
    '{"name": "Alice"}'::json,
    '$.age'
    DEFAULT '0' ON EMPTY
) AS age;
-- Returns: 0

2. Incorrect JSONPath Expression

Typos, wrong nesting levels, or out-of-bounds array indexes in a JSONPath expression will cause the path evaluation to return nothing, triggering the error.

-- Wrong path: actual key is 'addr', not 'address'
SELECT JSON_VALUE(
    '{"addr": {"city": "Seoul"}}'::json,
    '$.address.city'   -- typo here
    ERROR ON EMPTY
);
-- ERROR: SQL/JSON object not found

-- Fix: Verify path with JSON_EXISTS first
SELECT
    CASE
        WHEN JSON_EXISTS('{"addr": {"city": "Seoul"}}', '$.addr.city')
        THEN JSON_VALUE('{"addr": {"city": "Seoul"}}', '$.addr.city')
        ELSE 'Unknown'
    END AS city;

-- Out-of-bounds array index
SELECT JSON_VALUE(
    '{"items": [1, 2, 3]}'::json,
    '$.items[10]'
    NULL ON EMPTY   -- safe fallback
) AS item;
-- Returns: NULL

3. Missing Nested Objects in JSON_TABLE

When using NESTED PATH inside JSON_TABLE, rows where the nested path doesn't exist will trigger the error if no safe fallback is defined.

-- Problematic query
SELECT jt.*
FROM orders,
JSON_TABLE(
    order_data, '$'
    COLUMNS (
        order_id INT PATH '$.id',
        NESTED PATH '$.items[*]'
        COLUMNS (
            item_name TEXT PATH '$.name' ERROR ON EMPTY,
            qty       INT  PATH '$.qty'  ERROR ON EMPTY
        )
    )
) AS jt;

-- Safe version with NULL ON EMPTY
SELECT jt.*
FROM orders,
JSON_TABLE(
    order_data, '$'
    COLUMNS (
        order_id INT PATH '$.id' NULL ON EMPTY NULL ON ERROR,
        NESTED PATH '$.items[*]'
        COLUMNS (
            item_name TEXT PATH '$.name' NULL ON EMPTY,
            qty       INT  PATH '$.qty'  DEFAULT 1 ON EMPTY NULL ON ERROR
        )
    )
) AS jt
WHERE JSON_EXISTS(order_data, '$.items');

Quick Fix Solutions

Always specify NULL ON EMPTY or DEFAULT ... ON EMPTY in every SQL/JSON function call. Never rely on implicit defaults in production code.
Use JSON_EXISTS as a guard before calling JSON_VALUE or JSON_QUERY when you can't guarantee the JSON structure.
Pre-filter rows using WHERE JSON_EXISTS(column, '$.required_path') before passing data to JSON_TABLE.

-- Production-safe pattern
SELECT
    id,
    JSON_VALUE(payload, '$.user.name'  NULL ON EMPTY) AS name,
    JSON_VALUE(payload, '$.user.email' DEFAULT 'n/a' ON EMPTY) AS email,
    JSON_QUERY(payload, '$.metadata'   NULL ON EMPTY) AS metadata
FROM events;

Prevention Tips

1. Validate JSON schema at insert time using CHECK constraints

CREATE OR REPLACE FUNCTION check_required_keys(data jsonb)
RETURNS boolean AS $$
BEGIN
    RETURN data ? 'id' AND data ? 'name' AND data ? 'timestamp';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

ALTER TABLE events
ADD CONSTRAINT chk_event_schema
CHECK (check_required_keys(payload));

2. Establish a team coding convention: mandate explicit ON EMPTY and ON ERROR clauses in all SQL/JSON function calls during code review. This makes intent clear and prevents silent or loud failures when upstream JSON schemas evolve.

Related Errors

Code	Name	Description
`2203D`	`sql_json_member_not_found`	A specific member key was not found in a JSON object
`2203F`	`sql_json_array_not_found`	Expected a JSON array but none was present at the path
`2203G`	`sql_json_scalar_required`	A scalar was required but an object or array was returned
`22032`	`invalid_json_text`	The JSON document itself is malformed — check this first

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

DEV Community