PostgreSQL 2203A Error: Causes and Solutions Complete Guide

PostgreSQL Error 2203A: sql_json_member_not_found

PostgreSQL error code 2203A (sql_json_member_not_found) occurs when a SQL/JSON path expression attempts to access a key (member) that does not exist within a JSON object. This error is typically raised in strict mode path evaluation, where PostgreSQL enforces that every referenced member must exist — unlike lax mode, which silently returns NULL for missing members. If you've recently upgraded to PostgreSQL 14+ or started using the new JSON_VALUE / JSON_QUERY standard functions, you're likely encountering this error more often.

---

Top 3 Causes

1. Using strict mode with missing JSON keys

When you explicitly use strict in a JSONPath expression, PostgreSQL throws 2203A instead of returning NULL for non-existent members.

-- Triggers 2203A
SELECT jsonb_path_query(
    '{"name": "Alice"}'::jsonb,
    'strict $.address.city'
);
-- ERROR:  2203A: SQL/JSON member not found

-- Fix: switch to lax mode
SELECT jsonb_path_query_first(
    '{"name": "Alice"}'::jsonb,
    'lax $.address.city'
);
-- Result: NULL (no error)

2. Inconsistent JSON structures across rows

Real-world JSON data is often schema-less — not every row has the same keys. Querying a key that exists in some rows but not others will cause this error in strict path evaluation.

-- Sample data with inconsistent structure
CREATE TABLE logs (id SERIAL, payload JSONB);
INSERT INTO logs (payload) VALUES
    ('{"user": {"name": "Bob", "role": "admin"}}'),
    ('{"user": {"name": "Carol"}}'),   -- no "role" key
    ('{"action": "ping"}');             -- no "user" key at all

-- Safe extraction using lax mode + COALESCE
SELECT
    id,
    COALESCE(
        jsonb_path_query_first(payload, 'lax $.user.name')::text,
        'unknown'
    ) AS username,
    COALESCE(
        jsonb_path_query_first(payload, 'lax $.user.role')::text,
        'guest'
    ) AS role
FROM logs;

3. Missing error-handling clauses in JSON_VALUE / JSON_QUERY

PostgreSQL 14+ introduced SQL-standard JSON_VALUE and JSON_QUERY functions. By default, these functions raise an error when the path is not found, unlike the older ->> operator which returns NULL.

-- Triggers 2203A with JSON_VALUE
SELECT JSON_VALUE('{"name": "Dave"}'::jsonb, '$.age');
-- ERROR:  2203A

-- Fix: add ON EMPTY and ON ERROR clauses
SELECT JSON_VALUE(
    '{"name": "Dave"}'::jsonb,
    '$.age'
    DEFAULT 0 ON EMPTY
    DEFAULT 0 ON ERROR
) AS age;
-- Result: 0

-- Fix for JSON_QUERY
SELECT JSON_QUERY(
    '{"name": "Dave"}'::jsonb,
    '$.address'
    NULL ON EMPTY
    NULL ON ERROR
) AS address;
-- Result: NULL

---

Quick Fix Solutions

Option 1 – Use lax mode universally for uncertain JSON structures:

SELECT jsonb_path_query_first(data, 'lax $.some.nested.key')::text
FROM your_table;

Option 2 – Check key existence before accessing:

SELECT
    CASE
        WHEN jsonb_path_exists(payload, '$.user.email')
        THEN payload #>> '{user,email}'
        ELSE 'N/A'
    END AS email
FROM logs;

Option 3 – Create a reusable safe extraction function:

CREATE OR REPLACE FUNCTION safe_jsonb_value(
    p_data JSONB,
    p_path TEXT,
    p_default TEXT DEFAULT NULL
) RETURNS TEXT LANGUAGE plpgsql AS $$
BEGIN
    RETURN COALESCE(
        jsonb_path_query_first(p_data, p_path::jsonpath)::text,
        p_default
    );
EXCEPTION
    WHEN sqlstate '2203A' THEN RETURN p_default;
    WHEN OTHERS THEN RETURN p_default;
END;
$$;

-- Usage
SELECT safe_jsonb_value(
    '{"user": {"name": "Eve"}}'::jsonb,
    '$.user.email',
    '[e-mail]'
);

---

Prevention Tips

1. Enforce JSON structure at insert time using CHECK constraints:

ALTER TABLE logs
ADD CONSTRAINT chk_user_key
CHECK (payload ? 'user' AND (payload -> 'user') ? 'name');

This guarantees structural consistency at the data layer, eliminating the root cause of 2203A at query time.

2. Standardize on lax mode and defensive defaults in your team's SQL guidelines:

Always prefer lax mode for JSON path queries unless you have a specific reason to enforce strict structure. Add COALESCE, DEFAULT ... ON EMPTY, and DEFAULT ... ON ERROR clauses as mandatory patterns in code reviews. Reserve strict mode only for validated, fully controlled JSON sources where you can guarantee schema consistency.

---

Related Errors

Error Code	Name	Brief Description
22032	invalid_json_text	Malformed JSON string at parse time
2203B	sql_json_array_not_found	Expected an array but none found at path
2203C	sql_json_scalar_required	Path returned object/array instead of scalar
2203D	sql_json_item_cannot_be_cast_to_target_type	JSON value cannot be cast to target 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.