PostgreSQL 22037 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22037: Non Unique Keys in a JSON Object

PostgreSQL error code 22037 is raised when a JSON object contains duplicate keys, which violates the strict uniqueness requirement enforced by jsonb and certain JSON path functions. While the JSON specification (RFC 7159) technically permits duplicate keys, PostgreSQL's jsonb type and related operators require that every key within a JSON object be unique. This error commonly surfaces during data migrations, ETL pipelines, or when ingesting JSON from external APIs that don't enforce key uniqueness.

---

Top 3 Causes

1. Casting a Duplicate-Key JSON String to jsonb

The json type stores data as-is and tolerates duplicate keys, but jsonb parses and normalizes the structure, making duplicate keys intolerable.

-- This will throw ERROR 22037
SELECT '{"status": "active", "status": "inactive"}'::jsonb;
-- ERROR:  22037: non unique keys in a json object

-- The json type accepts it silently (last key wins in some contexts)
SELECT '{"status": "active", "status": "inactive"}'::json;
-- Works fine, but behavior is undefined for duplicate keys

2. Bulk Migration from json Columns to jsonb

Hidden duplicate keys in json columns only become visible when you try to convert them to jsonb, often during schema upgrades or ETL jobs.

-- Identify rows with duplicate keys before migration
SELECT id, raw_json
FROM staging_table
WHERE (
    SELECT COUNT(*) FROM json_object_keys(raw_json)
) <> (
    SELECT COUNT(DISTINCT k) FROM json_object_keys(raw_json) AS k
);

-- Safe deduplication using DISTINCT ON before casting
WITH deduped AS (
    SELECT
        id,
        jsonb_object_agg(key, value) AS clean_data
    FROM staging_table,
    LATERAL (
        SELECT DISTINCT ON (key) key, value
        FROM json_each(raw_json)
        ORDER BY key
    ) AS t
    GROUP BY id
)
UPDATE target_table tt
SET data = d.clean_data
FROM deduped d
WHERE tt.id = d.id;

3. Dynamic JSON Construction with Repeated Keys

Using json_build_object with repeated keys, or merging multiple data sources without deduplication, can silently introduce duplicate keys.

-- Problematic: duplicate key passed to json_build_object
SELECT json_build_object(
    'role', 'admin',
    'role', 'user'   -- duplicate!
);

-- Safe pattern: deduplicate key-value pairs before aggregating
WITH kv AS (
    SELECT 'role' AS k, 'admin' AS v, 1 AS priority
    UNION ALL
    SELECT 'role', 'user', 2
),
deduped AS (
    SELECT DISTINCT ON (k) k, v
    FROM kv
    ORDER BY k, priority ASC
)
SELECT jsonb_object_agg(k, v) AS safe_json
FROM deduped;
-- Result: {"role": "admin"}

---

Quick Fix Solutions

-- Fix 1: Use jsonb || operator to safely merge (right side wins, no duplicate error)
UPDATE user_data
SET profile = profile || '{"status": "active"}'::jsonb
WHERE user_id = 42;

-- Fix 2: Wrap risky casts in exception handling (PL/pgSQL)
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT id, raw_json FROM staging_table LOOP
        BEGIN
            INSERT INTO clean_table(id, data)
            VALUES (rec.id, rec.raw_json::jsonb);
        EXCEPTION
            WHEN sqlstate '22037' THEN
                RAISE WARNING 'Skipping id=% due to duplicate JSON keys', rec.id;
        END;
    END LOOP;
END;
$$;

-- Fix 3: Remove duplicate keys by round-tripping through jsonb functions
-- PostgreSQL keeps the last occurrence when casting json->jsonb (version-dependent)
SELECT your_json_column::text::jsonb
FROM your_table;

---

Prevention Tips

1. Prefer jsonb over json for storage

Using jsonb as your column type ensures duplicate keys are caught at insert time rather than discovered later during queries or migrations. Add a check constraint or a BEFORE INSERT trigger on json columns used as staging areas to detect duplicates early.

-- Trigger to block duplicate-key JSON before it enters staging
CREATE OR REPLACE FUNCTION check_unique_json_keys()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT COUNT(*) FROM json_object_keys(NEW.raw_json)) <>
       (SELECT COUNT(DISTINCT k) FROM json_object_keys(NEW.raw_json) AS k)
    THEN
        RAISE EXCEPTION 'Duplicate keys detected in JSON for id=%', NEW.id
            USING ERRCODE = '22037';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_json_keys
BEFORE INSERT OR UPDATE ON staging_table
FOR EACH ROW EXECUTE FUNCTION check_unique_json_keys();

2. Validate JSON at the application layer before sending to PostgreSQL

Enforce key uniqueness in your application code (Python's json module raises errors on duplicates with certain parsers; use strict JSON libraries). Add JSON schema validation to your CI/CD pipeline so duplicate-key payloads never reach the database in the first place.

---

Related Errors

Error Code	Name	Description
22032	invalid_json_text	Malformed JSON syntax
22P02	invalid_text_representation	Invalid cast to jsonb
22023	invalid_parameter_value	Bad arguments to JSON functions

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.