SQLSTATE 22023 ERROR Class 22: Data Exception

invalid_parameter_value cannot extract elements from a scalar — 22023

PostgreSQL error "cannot extract elements from a scalar" (SQLSTATE 22023): what it means, common causes, and how to fix it.

PG 12, 13, 14, 15, 16, 17, 18 Official docs
Last reviewed May 2025 Grounded in source

Diagnostic Queries

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

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

Related & next steps

Reference: PostgreSQL 18 Section 9.16 “JSON Functions”.

Was this helpful?