How to fix SQL/JSON scalar required in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 27, 2025· 142 views

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.

Quick fix

Fixes SQL/JSON scalar required

SELECT JSON_VALUE(jsonb '{"name": "John", "age": 30}', '$.name');
-- Returns: "John" (scalar string)
  1. Understand JSON_VALUE vs JSON_QUERY functions
  2. Fix path expressions to target scalar fields
  3. Specify array indices explicitly
  4. Use JSON_QUERY for composite data structures
postgresql — error
SELECT JSON_VALUE(jsonb '{"name": "John", "age": 30}', '$.name');
-- Returns: "John" (scalar string)
SQL/JSON scalar required

What this error means

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

Typical symptoms

  • ERROR: SQL/JSON scalar required when executing JSON_VALUE queries
  • Queries work with JSON_QUERY but fail with JSON_VALUE
  • Path expressions that worked in other JSON functions cause errors with JSON_VALUE
  • Selecting an entire nested object or array triggers the error
  • Error appears in application logs during JSON extraction operations

Common causes

  1. Using JSON_VALUE to extract an entire JSON object instead of a scalar field
  2. Path expression targets an array instead of a single element
  3. Forgetting to specify the array index when selecting from a JSON array
  4. Using JSON_VALUE when JSON_QUERY is more appropriate for the data structure
  5. Incorrect path syntax that returns composite data instead of scalars
  6. Strict mode evaluation where the path expression doesn't guarantee a scalar result

How to fix "SQL/JSON scalar required"

1Understand JSON_VALUE vs JSON_QUERY functions

PostgreSQL provides different functions for different extraction needs:

JSON_VALUE - For extracting SCALAR values only (strings, numbers, booleans, null):

sql
SELECT JSON_VALUE(jsonb '{"name": "John", "age": 30}', '$.name');
-- Returns: "John" (scalar string)

JSON_QUERY - For extracting ANY value (scalars, arrays, objects):

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

2Fix path expressions to target scalar fields

Ensure your JSON path expression explicitly points to a scalar value, not a composite structure.

Incorrect (targets entire object):

sql
SELECT JSON_VALUE(jsonb '[{"id": 1, "name": "Alice"}]', 'strict $[0]');
-- ERROR: SQL/JSON scalar required - $[0] is an object

Correct (targets scalar field within object):

sql
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: 1

Always specify the complete path to the scalar field you want to extract.

3Specify array indices explicitly

When extracting from JSON arrays, always include the index to target a specific element:

Incorrect (targets array instead of element):

sql
SELECT JSON_VALUE(jsonb '["apple", "banana", "cherry"]', 'strict $');
-- ERROR: SQL/JSON scalar required - $ refers to the entire array

Correct (targets specific array element):

sql
-- 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"
4Use JSON_QUERY for composite data structures

If you need to extract objects or arrays, use JSON_QUERY instead:

Wrong function:

sql
-- 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 object

Correct function for composite data:

sql
-- 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: 30
5Check path expression modes (strict vs lax)

PostgreSQL JSON paths support two modes that handle errors differently:

Strict mode (default) - Errors are reported:

sql
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 scalar

Lax mode - Errors are suppressed (returns NULL for structural errors):

sql
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 match

Use ON ERROR clause to handle these cases:

sql
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)
6Validate JSON structure before extraction

Use JSON_EXISTS or type-checking to ensure your path returns scalars before attempting extraction:

sql
-- 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 exists

Defensive query pattern:

sql
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';
7Test your JSON paths independently

Before using paths in production, test them separately:

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

Advanced notes

### 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

sql
-- 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:

sql
-- 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

Frequently asked questions

What does "SQL/JSON scalar required" mean?
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.
How do I fix "SQL/JSON scalar required"?
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.
What causes "SQL/JSON scalar required"?
Using JSON_VALUE to extract an entire JSON object instead of a scalar field Path expression targets an array instead of a single element Forgetting to specify the array index when selecting from a JSON array
Is the "SQL/JSON scalar required" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading