PostgreSQL 22038 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22038: singleton sql json item required

PostgreSQL error code 22038 (singleton sql json item required) occurs when a SQL/JSON path expression returns multiple items or a non-scalar value in a context where exactly one scalar value is expected. This most commonly happens with JSON_VALUE() and similar SQL/JSON functions introduced or enhanced in PostgreSQL 14+. Understanding this error is essential for anyone working with JSON data in modern PostgreSQL environments.

---

Top 3 Causes and Fixes

Cause 1: Path expression returns multiple values (wildcard on arrays)

Using [*] or any path that matches multiple array elements inside JSON_VALUE() triggers this error immediately.

-- ERROR: triggers 22038
SELECT JSON_VALUE('{"tags": ["postgres", "json", "sql"]}', '$.tags[*]');
-- ERROR:  singleton SQL/JSON item required

-- FIX: Specify an explicit index
SELECT JSON_VALUE('{"tags": ["postgres", "json", "sql"]}', '$.tags[0]');
-- Result: postgres

-- FIX: Use NULL ON ERROR to suppress and handle gracefully
SELECT JSON_VALUE(
    '{"tags": ["postgres", "json", "sql"]}',
    '$.tags[*]'
    NULL ON ERROR
);
-- Result: NULL (no error thrown)

Cause 2: Using JSON_VALUE() to extract an object or array

JSON_VALUE() is strictly for scalar values only. Trying to extract a JSON object or array with it causes error 22038. Use JSON_QUERY() instead for non-scalar results.

-- ERROR: trying to extract an object with JSON_VALUE
SELECT JSON_VALUE('{"user": {"name": "Alice", "age": 25}}', '$.user');
-- ERROR:  singleton SQL/JSON item required

-- FIX: Use JSON_QUERY for objects and arrays
SELECT JSON_QUERY('{"user": {"name": "Alice", "age": 25}}', '$.user');
-- Result: {"name": "Alice", "age": 25}

-- FIX: Use JSON_QUERY with ARRAY WRAPPER for multiple results
SELECT JSON_QUERY(
    '{"scores": [90, 85, 78]}',
    '$.scores[*]'
    WITH UNCONDITIONAL ARRAY WRAPPER
);
-- Result: [90, 85, 78]

Cause 3: Missing ON ERROR / ON EMPTY clause with unpredictable data

When JSON data comes from user input or external sources, its structure may vary. Without explicit error-handling clauses, any path returning multiple or zero items crashes with 22038.

-- RISKY: No error handling, will crash on bad data
SELECT JSON_VALUE(raw_json, '$.config.timeout')
FROM incoming_events;

-- SAFE: Always include ON ERROR and ON EMPTY
SELECT
    id,
    JSON_VALUE(raw_json, '$.config.timeout'
        DEFAULT '30' ON EMPTY
        DEFAULT '30' ON ERROR
    ) AS timeout,
    JSON_VALUE(raw_json, '$.user.name'
        NULL ON EMPTY
        NULL ON ERROR
    ) AS username
FROM incoming_events;

-- Alternative: Use native jsonb operators to avoid strict SQL/JSON rules
SELECT
    id,
    (raw_json::jsonb ->> 'timeout') AS timeout,
    (raw_json::jsonb -> 'tags') AS tags_array
FROM incoming_events;

---

Quick Fix Decision Tree

Goal	Function to Use
Extract a scalar (string, number, bool)	JSON_VALUE() with explicit index
Extract an object or array	JSON_QUERY()
Extract multiple values as array	JSON_QUERY() + WITH ARRAY WRAPPER
Avoid crashes on unpredictable data	Add NULL ON ERROR or DEFAULT x ON ERROR

---

Prevention Tips

1. Always specify array indexes and include ON ERROR clauses. Never use wildcard paths ([*]) inside JSON_VALUE(). Establish a team coding standard: JSON_VALUE() for scalars only, JSON_QUERY() for structures, and always pair them with ON ERROR / ON EMPTY to prevent runtime crashes in production.

2. Prefer native jsonb operators for flexibility. PostgreSQL's -> and ->> operators are more forgiving than strict SQL/JSON standard functions and work seamlessly with jsonb_array_elements() for multi-value extraction. Use SQL/JSON standard functions when SQL portability matters, but default to jsonb operators for internal application logic where control over the data structure is limited.

---

Related Errors

• 22032 (invalid_json_text): Malformed JSON input string.
• 22033 (invalid_sql_json_subscript): Array subscript out of range or invalid.
• 22035 (no_sql_json_item): Path expression matched nothing; handle with ON EMPTY.
• 22034 (more_than_one_sql_json_item): Similar to 22038; catch both codes in error handling logic.

---

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