This error occurs when using PostgreSQL's JSON_VALUE function with a JSON path expression that returns an array or object instead of a scalar value. The JSON_VALUE function is designed to extract single scalar values, and will fail if the path expression points to a non-scalar result.
PostgreSQL's SQL/JSON functions (JSON_VALUE, JSON_QUERY, JSON_EXISTS) use path expressions to navigate and extract data from JSON documents. The JSON/SQL data model treats values as either scalar items (strings, numbers, booleans, null) or composite structures (arrays and objects). The "SQL/JSON scalar required" error specifically occurs when using the JSON_VALUE function, which is designed exclusively for extracting scalar values from JSON. If the JSON path expression evaluates to an array, object, or multiple items instead of a single scalar value, PostgreSQL raises this error. This commonly happens when: - The path expression targets an entire JSON object or array instead of a specific field - The path expression returns multiple items when only one scalar is expected - The wrong function is used for the type of data being extracted
PostgreSQL provides different functions for different extraction needs:
JSON_VALUE - For extracting SCALAR values only (strings, numbers, booleans, null):
SELECT JSON_VALUE(jsonb '{"name": "John", "age": 30}', '$.name');
-- Returns: "John" (scalar string)JSON_QUERY - For extracting ANY value (scalars, arrays, objects):
SELECT JSON_QUERY(jsonb '{"user": {"name": "John", "age": 30}}', '$.user');
-- Returns: {"name": "John", "age": 30} (object)If you need to extract an object or array, use JSON_QUERY instead of JSON_VALUE.
Ensure your JSON path expression explicitly points to a scalar value, not a composite structure.
Incorrect (targets entire object):
SELECT JSON_VALUE(jsonb '[{"id": 1, "name": "Alice"}]', 'strict $[0]');
-- ERROR: SQL/JSON scalar required - $[0] is an objectCorrect (targets scalar field within object):
SELECT JSON_VALUE(jsonb '[{"id": 1, "name": "Alice"}]', 'strict $[0].name');
-- Returns: "Alice"
SELECT JSON_VALUE(jsonb '[{"id": 1, "name": "Alice"}]', 'strict $[0].id');
-- Returns: 1Always specify the complete path to the scalar field you want to extract.
When extracting from JSON arrays, always include the index to target a specific element:
Incorrect (targets array instead of element):
SELECT JSON_VALUE(jsonb '["apple", "banana", "cherry"]', 'strict $');
-- ERROR: SQL/JSON scalar required - $ refers to the entire arrayCorrect (targets specific array element):
-- Get first element
SELECT JSON_VALUE(jsonb '["apple", "banana", "cherry"]', 'strict $[0]');
-- Returns: "apple"
-- Get element by index
SELECT JSON_VALUE(jsonb '["apple", "banana", "cherry"]', 'strict $[1]');
-- Returns: "banana"
-- Get nested scalar in array element
SELECT JSON_VALUE(
jsonb '[{"color": "red"}, {"color": "yellow"}]',
'strict $[0].color'
);
-- Returns: "red"If you need to extract objects or arrays, use JSON_QUERY instead:
Wrong function:
-- This fails because JSON_VALUE expects scalars
SELECT JSON_VALUE(
jsonb '{"user": {"name": "John", "details": {"age": 30, "city": "NYC"}}}',
'$.user.details'
);
-- ERROR: SQL/JSON scalar required - details is an objectCorrect function for composite data:
-- Use JSON_QUERY to extract the object
SELECT JSON_QUERY(
jsonb '{"user": {"name": "John", "details": {"age": 30, "city": "NYC"}}}',
'$.user.details'
);
-- Returns: {"age": 30, "city": "NYC"}
-- To then extract scalar from it, use JSON_VALUE on the result
SELECT JSON_VALUE(
jsonb '{"user": {"name": "John", "details": {"age": 30, "city": "NYC"}}}',
'$.user.details.age'
);
-- Returns: 30PostgreSQL JSON paths support two modes that handle errors differently:
Strict mode (default) - Errors are reported:
SELECT JSON_VALUE(jsonb '[1, 2, 3]', 'strict $');
-- ERROR: SQL/JSON scalar required - strict mode rejects array
SELECT JSON_VALUE(jsonb '{"a": [1, 2]}', 'strict $.a');
-- ERROR: SQL/JSON scalar required - $.a is an array, not scalarLax mode - Errors are suppressed (returns NULL for structural errors):
SELECT JSON_VALUE(jsonb '[1, 2, 3]', 'lax $');
-- Returns: NULL - lax mode adapts and returns null for array
SELECT JSON_VALUE(jsonb '{"a": [1, 2]}', 'lax $.a');
-- Returns: NULL - lax mode returns null when structural type doesn't matchUse ON ERROR clause to handle these cases:
SELECT JSON_VALUE(
jsonb '[1, 2, 3]',
'strict $'
ERROR ON ERROR
);
-- ERROR explicitly raised
SELECT JSON_VALUE(
jsonb '[1, 2, 3]',
'strict $'
DEFAULT 'not-a-scalar' ON ERROR
);
-- Returns: "not-a-scalar" (error handled gracefully)Use JSON_EXISTS or type-checking to ensure your path returns scalars before attempting extraction:
-- Check if a path exists and would return a scalar
SELECT jsonb '{"name": "Alice", "hobbies": ["reading", "gaming"]}' @? '$.name';
-- Returns: true
SELECT jsonb '{"name": "Alice", "hobbies": ["reading", "gaming"]}' @? '$.hobbies';
-- Returns: true (path exists but might not be scalar)
-- Better approach: explicitly validate before JSON_VALUE
WITH data AS (
SELECT jsonb '{"user": {"name": "John", "age": 30}}' as doc
)
SELECT
CASE
WHEN doc @? '$.user.name' THEN JSON_VALUE(doc, '$.user.name')
ELSE NULL
END as name
FROM data;
-- Safely extracts name if it existsDefensive query pattern:
SELECT
id,
JSON_VALUE(config, '$.timeout' RETURNING integer DEFAULT 30 ON ERROR) as timeout,
JSON_VALUE(config, '$.host' DEFAULT 'localhost' ON ERROR) as host
FROM settings
WHERE status = 'active';Before using paths in production, test them separately:
-- Test JSON structure
SELECT jsonb_pretty(jsonb '{"users": [{"id": 1, "name": "Alice"}]}');
-- Test path exists
SELECT jsonb '{"users": [{"id": 1, "name": "Alice"}]}' @? '$.users[0].name';
-- Test JSON_QUERY first (more lenient)
SELECT JSON_QUERY(
jsonb '{"users": [{"id": 1, "name": "Alice"}]}',
'$.users[0]'
);
-- If this works, then test JSON_VALUE on the specific field:
SELECT JSON_VALUE(
jsonb '{"users": [{"id": 1, "name": "Alice"}]}',
'$.users[0].name'
);Use this test-before-deployment pattern to catch path issues early.
### SQL/JSON Path Expression Language
PostgreSQL implements the SQL/JSON path language (IETF draft standard) which has specific rules for path evaluation:
Lax vs Strict Mode Behavior:
- Lax (default): Automatically unwraps single-element arrays and adapts to structure
- Strict: Requires exact structural match and fails on type mismatches
### Handling NULL and Missing Values
-- Missing field returns NULL
SELECT JSON_VALUE(jsonb '{"a": 1}', '$.missing_field');
-- Returns: NULL
-- Use COALESCE with DEFAULT
SELECT COALESCE(
JSON_VALUE(jsonb '{"a": 1}', '$.b'),
'default_value'
);
-- Returns: 'default_value'
-- Or use ON EMPTY clause
SELECT JSON_VALUE(
jsonb '{"a": 1}',
'$.b'
DEFAULT 'empty' ON EMPTY
);### Type Casting with RETURNING
JSON_VALUE can return different SQL types:
-- Returns text (default)
SELECT JSON_VALUE(jsonb '{"count": 42}', '$.count');
-- Returns: "42" (text)
-- Returns integer
SELECT JSON_VALUE(jsonb '{"count": 42}', '$.count' RETURNING int);
-- Returns: 42 (integer)
-- Returns boolean
SELECT JSON_VALUE(jsonb '{"active": true}', '$.active' RETURNING boolean);
-- Returns: true
-- Type mismatch error handling
SELECT JSON_VALUE(
jsonb '{"name": "Alice"}',
'$.name'
RETURNING integer
DEFAULT 0 ON ERROR
);
-- Returns: 0 (cannot convert "Alice" to integer)### Performance Considerations
- Use specific paths instead of wildcards when possible
- Index JSON documents with GIN or GIST indexes for better performance
- Consider materialized views for frequently accessed JSON paths
- For repeated extractions, consider normalizing to separate columns
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL