PostgreSQL Error 2203F: sql_json_scalar_required
PostgreSQL error 2203F: sql_json_scalar_required occurs when a SQL/JSON function or path expression expects a scalar value (a single primitive such as a string, number, boolean, or null), but the evaluated JSON path returns a complex structure — an array or an object instead. This error is most commonly encountered with JSON_VALUE() and related SQL/JSON standard functions introduced prominently in PostgreSQL 16. In short, it's a type mismatch at the JSON navigation level.
Top 3 Causes
1. JSON_VALUE() Targeting an Array or Object
JSON_VALUE() strictly requires a single scalar result. If the JSON path points to an array or nested object, the error fires immediately.
-- ERROR: path returns an array
SELECT JSON_VALUE(data, '$.tags')
FROM articles;
-- ERROR: 2203F: sql_json_scalar_required
-- FIX 1: Target a specific array index
SELECT JSON_VALUE(data, '$.tags[0]')
FROM articles;
-- FIX 2: Use NULL ON ERROR as a safe fallback
SELECT JSON_VALUE(data, '$.tags' NULL ON ERROR)
FROM articles;
-- FIX 3: Use JSON_QUERY() for non-scalar results
SELECT JSON_QUERY(data, '$.tags')
FROM articles;
2. Wildcard Paths Returning Multiple Values
Wildcard expressions like $.* or $.items[*] return sequences of values. Feeding that sequence into a scalar-expecting function triggers 2203F.
-- ERROR: wildcard returns multiple values
SELECT JSON_VALUE(data, '$.items[*].price')
FROM orders;
-- ERROR: 2203F: sql_json_scalar_required
-- FIX 1: Pin to a specific index
SELECT JSON_VALUE(data, '$.items[0].price')
FROM orders;
-- FIX 2: Expand array into rows with JSON_TABLE()
SELECT t.price
FROM orders,
JSON_TABLE(
data,
'$.items[*]'
COLUMNS (price NUMERIC PATH '$.price')
) AS t;
-- FIX 3: Use jsonb_array_elements() for jsonb columns
SELECT (elem->>'price')::numeric AS price
FROM orders,
jsonb_array_elements(data->'items') AS elem;
3. Mixed JSON Types in a Column Without Type Guards
When a JSONB column stores a mix of scalars, arrays, and objects across rows, applying JSON_VALUE() blindly without filtering by type causes the error on rows containing non-scalar values.
-- Dangerous: mixed types in payload column
SELECT JSON_VALUE(payload, '$.result')
FROM api_logs;
-- Fails on rows where result is an array or object
-- FIX: Pre-filter using jsonb_typeof()
SELECT JSON_VALUE(payload, '$.result' NULL ON ERROR)
FROM api_logs
WHERE jsonb_typeof(payload->'result') IN ('string', 'number', 'boolean', 'null');
-- FIX: CASE expression for safe mixed-type handling
SELECT
id,
CASE jsonb_typeof(payload->'result')
WHEN 'string' THEN payload->>'result'
WHEN 'number' THEN payload->>'result'
WHEN 'boolean' THEN payload->>'result'
ELSE NULL
END AS scalar_result
FROM api_logs;
Quick Fix Summary
| Situation | Solution |
|---|---|
| Path returns an array | Use $.array[0] or switch to JSON_QUERY()
|
| Wildcard returns multiple values | Use JSON_TABLE() or jsonb_array_elements()
|
| Mixed column types | Add NULL ON ERROR or filter with jsonb_typeof()
|
| Need safe default | Use DEFAULT 'value' ON ERROR clause |
-- Universal safe pattern for JSON_VALUE()
SELECT JSON_VALUE(data, '$.field' NULL ON ERROR) AS safe_value
FROM your_table;
Prevention Tips
1. Always use ON ERROR clauses with JSON_VALUE()
Adopt NULL ON ERROR or DEFAULT ... ON ERROR as your team's standard. This prevents a single malformed row from breaking an entire query.
-- Team standard pattern
SELECT JSON_VALUE(data, '$.name' NULL ON ERROR) AS name,
JSON_VALUE(data, '$.age' NULL ON ERROR) AS age,
JSON_VALUE(data, '$.active' NULL ON ERROR) AS active
FROM users;
2. Enforce JSON structure with CHECK constraints
Lock down column-level JSON structure at insert time so bad data never reaches your queries.
-- Enforce that a specific field is always scalar
ALTER TABLE events
ADD CONSTRAINT chk_event_code_scalar
CHECK (
payload IS NULL
OR jsonb_typeof(payload->'code') IN ('string', 'number', 'boolean', 'null')
);
Related Errors
-
2203E: sql_json_array_not_found— The inverse: an array was expected but a scalar was returned. -
2203G: sql_json_object_not_found— An object was expected but something else was returned. -
2203A: sql_json_member_not_found— The specified JSON key does not exist in the object. -
22032: invalid_json_text— The JSON string itself is malformed and cannot be parsed at all.
📖 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.
