↑↓ select ↵ open ⌫ change scope Open full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

SQLSTATE / CLASS 22 · DATA EXCEPTION

22038

Read PG 18 manual ↗

singleton_sql_json_item_required

SQLSTATE
22038
Condition name
singleton_sql_json_item_required
Class
Data Exception
Source macro
ERRCODE_SINGLETON_SQL_JSON_ITEM_REQUIRED
Evidence
Source path confirmed
Analysis & operational context

English SQLSTATE atlas: authored explanations and source/runtime evidence are separate from the manual definitions. View source ↗

<h1>22038</h1>

At a glance

A SQL/JSON path operation expected one result of a required type, but received another cardinality or type. The fixed paths cover a singleton boolean result and the two numeric operands of binary jsonpath arithmetic; the SQL function and the @@ operator have different default silent behavior.

Messages

Representative primary texts are:

Guard Primary
Non-singleton or non-boolean jsonb_path_match result in throwing mode single boolean result is expected
Left binary arithmetic operand is not one numeric item left operand of jsonpath operator %s is not a single numeric value
Right binary arithmetic operand is not one numeric item right operand of jsonpath operator %s is not a single numeric value

Meaning

jsonb_path_match_internal treats exactly two C arguments as the @@ operator path: jsonb_path_match_opr leaves silent=true, so a non-singleton or non-boolean result becomes NULL. The SQL function is declared as jsonb_path_match(target, path, vars DEFAULT '{}', silent DEFAULT false); even a call written with two SQL arguments is expanded with both defaults, so it is non-silent and can raise 22038 unless the caller supplies silent=true. A four-argument call follows its supplied silent value. A single JSON null returns SQL NULL. Binary arithmetic separately evaluates each operand sequence, and both sides must contain exactly one numeric item. The shared evaluator may unwrap arrays in lax mode before this singleton check.

Diagnosis

Identify whether the syntax is the @@ operator or the jsonb_path_match function, and inspect the actual argument/default expansion before interpreting a NULL result. A path that yields several values, a non-boolean match result, or a left/right item that is not numeric belongs to this singleton/type boundary. Distinguish it from 22034 JSON_QUERY/JSON_VALUE cardinality, 2203F scalar-type enforcement, and 22036 numeric item-method conversion.

Response

Narrow the path or select one item explicitly. For binary arithmetic, make both operands resolve to one numeric item. Choose @@ when its suppressing behavior is intended, or call jsonb_path_match(..., false) when a mismatch should remain an ERROR; use silent=true only when NULL is the intended result. If an ERROR occurred in an explicit transaction, roll back or roll back to the existing savepoint before retrying; autocommit can retry the corrected action.

Versions

The locked catalogue records this condition from 12.0; the fixed match and binary-arithmetic paths are PostgreSQL 18.6. No natural runtime observation is claimed for this page.

Sources

The match wrapper and singleton guard are jsonpath_exec.c#L453-491; binary arithmetic singleton checks are #L2087-2155; the shared strict/lax and throw-return macros are #L235-249. The SQL defaults are fixed in system_functions.sql#L539-544, the direct function and @@ implementation signatures in pg_proc.dat#L10520-10522 and #L10547-10549, and the operator binding in pg_operator.dat#L3262-3264. The structured evidence record keeps both paths and exact primaries; no natural runtime was run.

Source evidence

Evidence belongs to the frozen source and runtime versions listed here. It is not a runtime verification of the selected manual version.

22038 is singleton_sql_json_item_required in SQLSTATE Class 22.

Method: Read fixed definition.

src.errcodes.18.6

The fixed jsonb_path_match_internal path treats exactly two C arguments as the @@ operator call: jsonb_path_match_opr leaves silent=true, so a non-singleton or non-boolean result is suppressed to NULL. The SQL function jsonb_path_match has vars DEFAULT '{}' and silent DEFAULT false; even a call written with two SQL arguments is expanded with both defaults, so it is non-silent unless silent=true is explicitly supplied. Binary jsonpath arithmetic independently requires each operand sequence to contain exactly one numeric item; lax unwrapping is performed by the shared evaluator.

Method: Read jsonb_path_match_internal together with system function defaults, pg_proc signatures, the @@ operator binding, and the shared jsonpath evaluator.

Limits: Source confirmation is not a natural runtime observation.

src.jsonpath.match-singleton src.jsonpath.binary-arithmetic-singleton src.jsonpath.context-flags src.jsonpath.execute-context src.catalog.system-functions src.catalog.pg-proc-match src.catalog.pg-proc-match-opr src.catalog.pg-operator-match

The locked catalogue records 22038 from 12.0 without proving exact implementation introduction.

Method: Use catalogue boundary.

src.errcodes.18.6

Message templates

ERROR · message.boolean-singleton

Primary

single boolean result is expected
ERROR · message.left-numeric-singleton

Primary

left operand of jsonpath operator %s is not a single numeric value
ERROR · message.right-numeric-singleton

Primary

right operand of jsonpath operator %s is not a single numeric value

Reproduction & repair cases

No reproduction case is attached to this condition.

Recorded runtime evidence

No runtime observation is attached to this entry.

Version history 1
  1. PG 11 → 12added

Definition snapshot: english-manuals:1eb26d0fed2b299aaa2b2d8069d… · English manual source