cannot extract elements from a scalar

SQLSTATE 22023 condition invalid_parameter_value class 22 — Data Exception severity ERROR

Reproduced & verified on PostgreSQL 14.23, 15.18, 16.14, 17.10 and 18.4 — identical message on every version.

Last reviewed 30 May 2025 · Reproduced live with the SQL on this page.

Symptoms

A JSON path/extraction tried to treat a scalar JSON value (number, string, boolean) as if it were an array or object. PostgreSQL raises SQLSTATE 22023 (invalid_parameter_value).

Happens with json_array_elements/jsonb_array_elements on a non-array.
The JSON value is a scalar, not a container.
Common when input shape varies row to row.

What the server log shows

ERROR:  cannot extract elements from a scalar

Why PostgreSQL raises this — what the manual says

As Section 9.16 JSON Functions and Operators explains:

A function such as jsonb_array_elements was applied to a JSON value that is a scalar (a number, string, boolean, or null) rather than an array or object, so there are no elements to extract; check the value’s type with jsonb_typeof first.

Array-expansion functions require the input to be a JSON array. A scalar has no elements to extract, so the function raises 22023 rather than returning rows.

Common causes

Calling array-expansion on a scalar or object.
Inconsistent JSON shapes across rows.
Wrong path producing a scalar where an array was expected.

How to fix it

Guard the call with a type check: WHERE jsonb_typeof(data) = 'array'.
Use jsonb_array_elements only on arrays; handle scalars separately.
Normalize the JSON shape on input.

Related & next steps

Reference: PostgreSQL 18 Section 9.16 “JSON Functions”.