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 no items in a context that strictly requires exactly one scalar or JSON value. This error is most commonly triggered by JSON_VALUE() and JSON_QUERY() functions introduced in PostgreSQL 15 as part of the SQL standard JSON support. Understanding this error is essential for anyone working with structured JSON data in modern PostgreSQL environments.

---

Top 3 Causes

1. Using a wildcard path [*] with JSON_VALUE()

JSON_VALUE() is designed to return exactly one scalar value. Using array wildcards produces multiple results, which violates the singleton requirement.

-- This will FAIL
SELECT JSON_VALUE('{"tags": ["postgresql", "json", "error"]}', '$.tags[*]');
-- ERROR: 22038: singleton SQL/JSON item required

-- FIXED: target a specific index
SELECT JSON_VALUE('{"tags": ["postgresql", "json", "error"]}', '$.tags[0]');
-- Result: postgresql

-- FIXED: use DEFAULT ON ERROR to handle gracefully
SELECT JSON_VALUE(
    '{"tags": ["postgresql", "json", "error"]}',
    '$.tags[*]'
    DEFAULT 'multiple values' ON ERROR
);

---

2. Using JSON_QUERY() without WITH ARRAY WRAPPER on multi-item paths

By default, JSON_QUERY() returns a single JSON object or array. When the path resolves to multiple separate items and no wrapper is specified, the singleton rule is violated.

-- This will FAIL
SELECT JSON_QUERY(
    '{"users": [{"id": 1}, {"id": 2}, {"id": 3}]}',
    '$.users[*]'
    WITHOUT ARRAY WRAPPER
);
-- ERROR: 22038: singleton SQL/JSON item required

-- FIXED: wrap results in an array
SELECT JSON_QUERY(
    '{"users": [{"id": 1}, {"id": 2}, {"id": 3}]}',
    '$.users[*]'
    WITH ARRAY WRAPPER
);
-- Result: [{"id": 1}, {"id": 2}, {"id": 3}]

---

3. Migrating from jsonb_path_query to SQL standard JSON functions

When converting legacy PostgreSQL-specific JSON queries to SQL standard functions, developers often overlook the strict singleton requirement of JSON_VALUE().

-- Legacy approach (works fine, returns multiple rows)
SELECT jsonb_path_query('{"scores": [10, 20, 30]}'::jsonb, '$.scores[*]');

-- Naive migration to JSON_VALUE (FAILS)
SELECT JSON_VALUE('{"scores": [10, 20, 30]}', '$.scores[*]');
-- ERROR: 22038: singleton SQL/JSON item required

-- Correct migration using jsonb_path_query_array
SELECT jsonb_path_query_array('{"scores": [10, 20, 30]}'::jsonb, '$.scores[*]');
-- Result: [10, 20, 30]

-- Or use JSON_QUERY with wrapper for SQL standard compliance
SELECT JSON_QUERY('{"scores": [10, 20, 30]}', '$.scores[*]' WITH ARRAY WRAPPER);
-- Result: [10, 20, 30]

---

Quick Fix Solutions

Goal	Recommended Function
Single scalar value	JSON_VALUE(doc, '$.key')
Single or wrapped JSON array	JSON_QUERY(doc, '$.key' WITH ARRAY WRAPPER)
All matching rows	jsonb_path_query(doc, '$.key[*]')
All results as one array	jsonb_path_query_array(doc, '$.key[*]')
First matching result	jsonb_path_query_first(doc, '$.key[*]')

Always add ON ERROR handling to prevent query failure from unexpected data:

SELECT JSON_VALUE(
    payload::json,
    '$.result'
    RETURNING TEXT
    NULL ON EMPTY
    DEFAULT 'error' ON ERROR
)
FROM incoming_events;

---

Prevention Tips

1. Always specify ON ERROR and ON EMPTY clauses
Never leave SQL/JSON functions without explicit error handling, especially when processing data from external sources. Use NULL ON EMPTY DEFAULT NULL ON ERROR as a safe baseline.

2. Choose the right function for the job
Before writing a JSON query, ask: "Will this path return exactly one item, or possibly many?" Use JSON_VALUE only for guaranteed singletons, JSON_QUERY with WITH ARRAY WRAPPER for potentially multiple results, and jsonb_path_query_array when you need all values as a native JSONB array. Adding this check to your team's code review checklist will prevent 22038 from ever reaching production.

---

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