EC
22037
PostgreSQLERRORNotableData ExceptionHIGH confidence

non-unique keys in a JSON object

What this means

SQLSTATE 22037 is raised when a JSON object construction function or SQL/JSON strict mode detects duplicate keys in an object, which is disallowed in strict mode.

Why it happens
  1. 1JSON object construction where the same key appears more than once in strict uniqueness mode
  2. 2SQL/JSON strict mode parsing an object with duplicate keys
How to reproduce

JSON object with duplicate keys in strict mode.

trigger — this will error
trigger — this will error
SELECT JSON_OBJECT('a': 1, 'a': 2 WITH UNIQUE KEYS);

expected output

ERROR:  duplicate JSON key "a"

Fix

Remove duplicate keys from the JSON object

WHEN When constructing JSON with programmatically generated keys.

Why this works

Deduplicate keys in the application layer before constructing the JSON object, or use jsonb_build_object() which keeps the last value for duplicate keys in lax mode.

Version notes
Postgres 16+

JSON_OBJECT with UNIQUE KEYS syntax added in Postgres 16.

Sources
Official documentation ↗

Class 22 — Data Exception

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors