non-unique keys in a JSON object

Category: Data ExceptionVersions: Postgres 14+

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

1JSON object construction where the same key appears more than once in strict uniqueness mode
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

SELECT JSON_OBJECT('a': 1, 'a': 2 WITH UNIQUE KEYS);

ERROR: duplicate JSON key "a"

Fix 1: Remove duplicate keys from the JSON object

When constructing JSON with programmatically generated keys.

fix

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 docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 22 — Data Exception

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE for JSON key uniqueness. Postgres 16+ for JSON_OBJECT WITH UNIQUE KEYS.