PostgreSQL 22037 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22037: Non Unique Keys in a JSON Object

PostgreSQL error code 22037 occurs when a JSON object contains duplicate keys — that is, the same key name appears more than once within a single JSON object. While the json type may silently accept or store duplicate keys, the jsonb type strictly enforces key uniqueness and will immediately raise this error upon insertion or casting. This commonly surfaces during data migrations, ETL pipelines, or when dynamically building JSON objects using PostgreSQL's built-in functions.

---

Top 3 Causes

1. Duplicate Keys in jsonb_build_object()

The most frequent culprit is passing duplicate key names when constructing a JSON object dynamically.

-- ❌ This will fail
SELECT jsonb_build_object(
    'name', 'Alice',
    'role', 'admin',
    'name', 'Bob'   -- duplicate key!
);
-- ERROR: 22037: non unique keys in a json object

-- ✅ Fix: use distinct key names
SELECT jsonb_build_object(
    'primary_name', 'Alice',
    'role',         'admin',
    'secondary_name', 'Bob'
);

2. Casting Non-Standard JSON Input to jsonb

External APIs or legacy systems may produce JSON strings with repeated keys. Casting such strings directly to jsonb will fail.

-- ❌ This will fail
SELECT '{"status": "ok", "status": "pending"}'::jsonb;
-- ERROR: 22037: non unique keys in a json object

-- ✅ Fix: parse as json first, then extract only what you need
WITH raw AS (
    SELECT '{"status": "ok", "status": "pending"}'::json AS data
)
SELECT jsonb_build_object('status', data->>'status')
FROM raw;

-- ✅ Fix: use a safe wrapper with exception handling
CREATE OR REPLACE FUNCTION safe_cast_jsonb(input TEXT)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE result jsonb;
BEGIN
    result := input::jsonb;
    RETURN result;
EXCEPTION WHEN sqlstate '22037' THEN
    -- Fallback: parse as json and re-aggregate (last key wins)
    SELECT jsonb_object_agg(key, value)
    INTO result
    FROM json_each(input::json);
    RETURN result;
END;
$$;

SELECT safe_cast_jsonb('{"status": "ok", "status": "pending"}');

3. Duplicate Keys in jsonb_object_agg()

When aggregating key-value pairs from a table, duplicate keys in the same group will trigger this error.

-- Sample data with a duplicate key per group
CREATE TEMP TABLE kv_data (id INT, k TEXT, v TEXT);
INSERT INTO kv_data VALUES (1, 'color', 'red'), (1, 'color', 'blue'), (1, 'size', 'M');

-- ❌ This will fail
SELECT id, jsonb_object_agg(k, v) FROM kv_data GROUP BY id;
-- ERROR: 22037: non unique keys in a json object

-- ✅ Fix: deduplicate before aggregating (keep first occurrence)
SELECT id, jsonb_object_agg(k, v)
FROM (
    SELECT DISTINCT ON (id, k) id, k, v
    FROM kv_data
    ORDER BY id, k
) deduped
GROUP BY id;

-- ✅ Fix: merge duplicate values into a JSON array
SELECT id, jsonb_object_agg(k, vals) AS result
FROM (
    SELECT id, k, jsonb_agg(v) AS vals
    FROM kv_data
    GROUP BY id, k
) grouped
GROUP BY id;
-- Result: {"color": ["red", "blue"], "size": ["M"]}

---

Quick Fix Solutions

• Catch the error gracefully using a PL/pgSQL EXCEPTION WHEN sqlstate '22037' block.
• Prefer jsonb over json so duplicate key issues are caught immediately at insert time rather than silently stored.
• Deduplicate source data with DISTINCT ON or GROUP BY before using aggregation functions.
• Validate JSON on the application side before sending it to the database.

---

Prevention Tips

1. Always use jsonb instead of json for stored data. The jsonb type validates key uniqueness at write time, making bugs visible early rather than hiding them inside loosely typed json columns.

2. Add a pre-load validation step in your ETL pipelines. Before bulk-inserting JSON data, run a quick diagnostic query to detect duplicate keys:
   

-- Detect rows with duplicate keys before loading
SELECT id, raw_json
FROM staging_table
WHERE (
    SELECT COUNT(*) FROM json_object_keys(raw_json)
) <> (
    SELECT COUNT(DISTINCT key)
    FROM json_object_keys(raw_json) AS key
);

Catching duplicates at the staging layer prevents entire batch jobs from failing and makes debugging far easier in production environments.

---

📖 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.