22038 — singleton_sql_json_item_required

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.

English manual coverage begins with PostgreSQL 8.1. Source history and runtime observations have their own recorded version boundaries.

Reading PG 18·Select a recorded version

12 13 14 15 16 17 18 19 20

aliases: Unknown
class name: Data Exception
condition name: singleton_sql_json_item_required
lang: en
sqlstate: 22038

Definition PG 18

22038

At a glance {#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 {#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 {#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 {#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 {#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 {#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.

Related {#related}

22034, 2203F, 22036

Sources {#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.

Compare versions

SQLSTATE evidence and recorded runtime versions

claims · identity

{
  "claim_id": "identity",
  "limits": "",
  "method": "Read fixed definition.",
  "position": 0,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "22038 is singleton_sql_json_item_required in SQLSTATE Class 22."
}

claims · resolved-path

{
  "claim_id": "resolved-path",
  "limits": "Source confirmation is not a natural runtime observation.",
  "method": "Read jsonb_path_match_internal together with system function defaults, pg_proc signatures, the @@ operator binding, and the shared jsonpath evaluator.",
  "position": 1,
  "runtime": [],
  "sources": [
    "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"
  ],
  "statement": "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."
}

claims · history

{
  "claim_id": "history",
  "limits": "",
  "method": "Use catalogue boundary.",
  "position": 2,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "The locked catalogue records 22038 from 12.0 without proving exact implementation introduction."
}

messages · message.boolean-singleton

{
  "limits": "",
  "message_id": "message.boolean-singleton",
  "path": "",
  "position": 0,
  "raw": {
    "id": "message.boolean-singleton",
    "primary_template": "single boolean result is expected",
    "severity_source": "ERROR",
    "sources": [
      "src.jsonpath.match-singleton"
    ],
    "sqlstate": "22038"
  },
  "severity": "ERROR",
  "sources": [
    "src.jsonpath.match-singleton"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "single boolean result is expected",
      "position": 0,
      "role": "",
      "template": "single boolean result is expected"
    }
  ]
}

messages · message.left-numeric-singleton

{
  "limits": "",
  "message_id": "message.left-numeric-singleton",
  "path": "",
  "position": 1,
  "raw": {
    "id": "message.left-numeric-singleton",
    "primary_template": "left operand of jsonpath operator %s is not a single numeric value",
    "severity_source": "ERROR",
    "sources": [
      "src.jsonpath.binary-arithmetic-singleton"
    ],
    "sqlstate": "22038"
  },
  "severity": "ERROR",
  "sources": [
    "src.jsonpath.binary-arithmetic-singleton"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "left operand of jsonpath operator is not a single numeric value",
      "position": 0,
      "role": "",
      "template": "left operand of jsonpath operator %s is not a single numeric value"
    }
  ]
}

messages · message.right-numeric-singleton

{
  "limits": "",
  "message_id": "message.right-numeric-singleton",
  "path": "",
  "position": 2,
  "raw": {
    "id": "message.right-numeric-singleton",
    "primary_template": "right operand of jsonpath operator %s is not a single numeric value",
    "severity_source": "ERROR",
    "sources": [
      "src.jsonpath.binary-arithmetic-singleton"
    ],
    "sqlstate": "22038"
  },
  "severity": "ERROR",
  "sources": [
    "src.jsonpath.binary-arithmetic-singleton"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "right operand of jsonpath operator is not a single numeric value",
      "position": 0,
      "role": "",
      "template": "right operand of jsonpath operator %s is not a single numeric value"
    }
  ]
}

sources · src.errcodes.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/errcodes.txt",
  "position": 0,
  "sha256": "6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba",
  "source_id": "src.errcodes.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt#L223"
}

sources · src.jsonpath.match-singleton

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/jsonpath_exec.c",
  "position": 1,
  "sha256": "678cb8947f0b5e6ad14524f1863987ab99641cfb5f81121fc5c5006af0319d02",
  "source_id": "src.jsonpath.match-singleton",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/jsonpath_exec.c#L453"
}

sources · src.jsonpath.binary-arithmetic-singleton

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/jsonpath_exec.c",
  "position": 2,
  "sha256": "678cb8947f0b5e6ad14524f1863987ab99641cfb5f81121fc5c5006af0319d02",
  "source_id": "src.jsonpath.binary-arithmetic-singleton",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/jsonpath_exec.c#L2087"
}

sources · src.jsonpath.context-flags

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/jsonpath_exec.c",
  "position": 3,
  "sha256": "678cb8947f0b5e6ad14524f1863987ab99641cfb5f81121fc5c5006af0319d02",
  "source_id": "src.jsonpath.context-flags",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/jsonpath_exec.c#L235"
}

sources · src.jsonpath.execute-context

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/adt/jsonpath_exec.c",
  "position": 4,
  "sha256": "678cb8947f0b5e6ad14524f1863987ab99641cfb5f81121fc5c5006af0319d02",
  "source_id": "src.jsonpath.execute-context",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/adt/jsonpath_exec.c#L654"
}

sources · src.catalog.system-functions

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/catalog/system_functions.sql",
  "position": 5,
  "sha256": "e5b61e70663b9dd55e2e6397ecb1ff3084f4f9afbf12907529b6e565a75f254f",
  "source_id": "src.catalog.system-functions",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/catalog/system_functions.sql#L539"
}

sources · src.catalog.pg-proc-match

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/include/catalog/pg_proc.dat",
  "position": 6,
  "sha256": "1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a",
  "source_id": "src.catalog.pg-proc-match",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/include/catalog/pg_proc.dat#L10520"
}

sources · src.catalog.pg-proc-match-opr

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/include/catalog/pg_proc.dat",
  "position": 7,
  "sha256": "1f934ce80d460159dda137714374a35f9896315ea2476709cbb4da13b38ef95a",
  "source_id": "src.catalog.pg-proc-match-opr",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/include/catalog/pg_proc.dat#L10547"
}

sources · src.catalog.pg-operator-match

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/include/catalog/pg_operator.dat",
  "position": 8,
  "sha256": "5d35b9b2ef5f9797cc815263927ef8a12367fedace53e828b4e8b320e2a7f703",
  "source_id": "src.catalog.pg-operator-match",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/include/catalog/pg_operator.dat#L3262"
}

Source references

Original manual reference

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · english-manuals:5b84e8967d81c30953c66bad5e88ad21e0de7a2f7c203bfd6a8737eaa1a6c1ef

Text language: en · english-manuals:5b84e8967d81c30953c66bad5e88ad21e0de7a2f7c203bfd6a8737eaa1a6c1ef