PostgreSQL 2203C Error: Causes and Solutions Complete Guide

PostgreSQL Error 2203C: sql_json_object_not_found

PostgreSQL error 2203C (sql_json_object_not_found) is raised when a SQL/JSON path expression cannot locate the specified key, element, or object within a JSON document. This error typically surfaces when using SQL/JSON functions like JSON_VALUE(), JSON_QUERY(), or jsonb_path_query() with strict mode or ERROR ON EMPTY behavior enabled. Since PostgreSQL 14 expanded its SQL-standard JSON function support, this error has become increasingly common in production environments.

---

Top 3 Causes

1. Using ERROR ON EMPTY in JSON_VALUE or JSON_QUERY

When you explicitly set ERROR ON EMPTY, PostgreSQL raises 2203C instead of returning NULL for missing paths.

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

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

-- Or simply use NULL ON EMPTY
SELECT JSON_VALUE(
    '{"name": "PostgreSQL"}'::json,
    '$.version' NULL ON EMPTY
);
-- Returns: NULL

2. Using strict Mode in JSON Path Expressions

In strict mode, PostgreSQL does not silently handle missing keys or out-of-bounds array indexes — it throws an error instead.

-- Triggers 2203C with strict mode
SELECT jsonb_path_query(
    '{"product": {"name": "Laptop"}}'::jsonb,
    'strict $.product.price'
);

-- Fix: Use lax mode (default behavior)
SELECT jsonb_path_query(
    '{"product": {"name": "Laptop"}}'::jsonb,
    'lax $.product.price'
);
-- Returns: (no rows) — no error

-- Safer alternative with COALESCE
SELECT COALESCE(
    jsonb_path_query_first(
        '{"product": {"name": "Laptop"}}'::jsonb,
        'lax $.product.price'
    )::text::numeric,
    0
) AS price;
-- Returns: 0

3. Array Filter Returning No Matches with ERROR ON EMPTY

When using the filter operator ? in a JSON path and no elements satisfy the condition, combined with ERROR ON EMPTY, the error is raised.

-- Triggers 2203C — no "completed" orders exist
SELECT JSON_QUERY(
    '{"orders": [{"id": 1, "status": "pending"}]}'::json,
    '$.orders[*] ? (@.status == "completed")' ERROR ON EMPTY
);

-- Fix: Check existence first
SELECT
    CASE
        WHEN jsonb_path_exists(
            data,
            '$.orders[*] ? (@.status == "completed")'
        )
        THEN jsonb_path_query_array(
            data,
            '$.orders[*] ? (@.status == "completed")'
        )
        ELSE '[]'::jsonb
    END AS completed_orders
FROM customer_data;

---

Quick Fix Solutions

• Replace ERROR ON EMPTY with NULL ON EMPTY or DEFAULT value ON EMPTY — the safest and most common fix.
• Switch from strict to lax path mode — lax tolerates missing keys gracefully.
• Use jsonb_path_exists() before querying — validate path existence before extracting values.
• Wrap extractions in a safe helper function for consistent error handling across your codebase:

CREATE OR REPLACE FUNCTION safe_json_value(
    p_json jsonb,
    p_path text,
    p_default text DEFAULT NULL
)
RETURNS text AS $$
BEGIN
    RETURN COALESCE(
        jsonb_path_query_first(p_json, p_path::jsonpath)::text,
        p_default
    );
EXCEPTION
    WHEN OTHERS THEN
        RETURN p_default;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage
SELECT safe_json_value(data, '$.user.email', 'N/A') FROM user_events;

---

Prevention Tips

Validate JSON structure at insert time using CHECK constraints to ensure required fields are always present:

ALTER TABLE user_events
ADD CONSTRAINT chk_required_json_fields
CHECK (
    jsonb_path_exists(data, '$.user.id') AND
    jsonb_path_exists(data, '$.event_type')
);

Default to lax mode and NULL ON EMPTY in all JSON path queries unless you have a specific reason to enforce strict validation. Reserve ERROR ON EMPTY only for data quality assertions in controlled ETL pipelines where missing fields represent a genuine data integrity violation.

---

Related Errors

Code	Name	Description
2203F	sql_json_array_not_found	Expected an array but none was found at the path
2203G	sql_json_scalar_required	JSON_VALUE received an object/array instead of a scalar
2203W	sql_json_item_cannot_be_cast_to_target_type	Found value cannot be cast to the specified type

---

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