PostgreSQL 22030 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22030: duplicate json object key value

PostgreSQL error code 22030 is raised when a JSON object contains duplicate keys, which violates the JSON specification (RFC 7159). This error most commonly appears when using jsonb_build_object(), json_object_agg(), or when inserting externally generated JSON strings that contain repeated keys into a jsonb column.

---

Top 3 Causes

1. Passing Duplicate Keys to jsonb_build_object()

This is the most frequent cause. Developers accidentally pass the same key twice when dynamically constructing JSON objects.

-- This will raise ERROR 22030
SELECT jsonb_build_object('user', 'Alice', 'score', 100, 'user', 'Bob');

-- Fix: Remove the duplicate key
SELECT jsonb_build_object('user', 'Alice', 'score', 100);

-- Or use distinct key names
SELECT jsonb_build_object('first_user', 'Alice', 'second_user', 'Bob', 'score', 100);

2. Inserting External JSON Strings with Duplicate Keys into jsonb Columns

External APIs or legacy systems sometimes produce JSON with repeated keys. While the json type accepts this, jsonb does not.

-- json type allows duplicate keys (no error)
SELECT '{"name": "Alice", "name": "Bob"}'::json;

-- Cast json to jsonb to auto-merge duplicate keys (last value wins)
SELECT '{"name": "Alice", "name": "Bob"}'::json::jsonb;
-- Result: {"name": "Bob"}

-- Safe insertion pattern for external data
INSERT INTO user_data (profile)
VALUES ('{"name": "Alice", "name": "Bob"}'::json::jsonb);

3. Duplicate Keys in json_object_agg() Aggregation

When aggregating rows where the key column contains duplicate values, PostgreSQL throws error 22030.

-- Sample data with duplicate keys
CREATE TABLE tags (id INT, k TEXT, v TEXT);
INSERT INTO tags VALUES (1,'color','red'),(1,'color','blue'),(1,'size','M');

-- ERROR: duplicate key value "color"
SELECT json_object_agg(k, v) FROM tags WHERE id = 1;

-- Fix: Deduplicate with DISTINCT ON before aggregating
SELECT json_object_agg(k, v)
FROM (
    SELECT DISTINCT ON (k) k, v
    FROM tags
    WHERE id = 1
    ORDER BY k, v
) deduped;

-- Alternative: Merge duplicate values into an array
SELECT json_object_agg(k, vals)
FROM (
    SELECT k, json_agg(v) AS vals
    FROM tags
    WHERE id = 1
    GROUP BY k
) grouped;

---

Quick Fix Solutions

• Use json → jsonb casting to automatically resolve duplicate keys when handling external data.
• Add DISTINCT ON in subqueries before calling json_object_agg().
• Audit dynamic JSON construction code to ensure all keys are unique before passing them to build functions.

-- Universal safe pattern: normalize external JSON input
CREATE OR REPLACE FUNCTION safe_to_jsonb(input TEXT)
RETURNS JSONB AS $$
BEGIN
    RETURN input::json::jsonb;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Use it in queries
SELECT safe_to_jsonb('{"id": 1, "id": 2, "name": "test"}');
-- Result: {"id": 2, "name": "test"}

---

Prevention Tips

1. Normalize all external JSON input via json → jsonb casting before storing it. This automatically resolves duplicate keys by keeping the last value, preventing runtime errors in production pipelines.

2. Validate and deduplicate keys before aggregation. Always use a CTE or subquery with DISTINCT ON or GROUP BY when using json_object_agg() on potentially dirty data sets.

-- Recommended pattern for safe JSON aggregation
WITH deduped AS (
    SELECT DISTINCT ON (key_col) key_col, val_col
    FROM source_table
    ORDER BY key_col, updated_at DESC
)
SELECT json_object_agg(key_col, val_col) FROM deduped;

---

Related Errors

• 22032 (invalid_json_text) — Malformed JSON syntax; often appears alongside 22030 when handling raw external JSON.
• 23505 (unique_violation) — Triggered by duplicate values in unique-indexed columns, a related uniqueness constraint issue.

---

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