PostgreSQL 22030 오류: 원인 및 솔루션 전체 가이드

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);

Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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"}

Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

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.