Generating SQL UPSERT (INSERT ON CONFLICT) from JSON

JSON to SQL UPSERT

An UPSERT (INSERT or UPDATE) inserts a new row if it does not exist, or updates the existing row if a conflict is detected. This is essential for idempotent data imports from JSON.

Example JSON

[
  { "id": 1, "name": "Alice", "email": "alice@new.com", "score": 95 },
  { "id": 2, "name": "Bob", "email": "bob@example.com", "score": 87 }
]

PostgreSQL (ON CONFLICT)

INSERT INTO users (id, name, email, score) VALUES
  (1, 'Alice', 'alice@new.com', 95),
  (2, 'Bob', 'bob@example.com', 87)
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  email = EXCLUDED.email,
  score = EXCLUDED.score;

MySQL (ON DUPLICATE KEY UPDATE)

INSERT INTO users (id, name, email, score) VALUES
  (1, 'Alice', 'alice@new.com', 95),
  (2, 'Bob', 'bob@example.com', 87)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email),
  score = VALUES(score);

SQL Server (MERGE)

MERGE INTO users AS target
USING (VALUES
  (1, 'Alice', 'alice@new.com', 95),
  (2, 'Bob', 'bob@example.com', 87)
) AS source (id, name, email, score)
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET
  name = source.name,
  email = source.email,
  score = source.score
WHEN NOT MATCHED THEN INSERT (id, name, email, score)
  VALUES (source.id, source.name, source.email, source.score);

Conflict Target Options

Conflict target	Use case
Primary key (id)	Standard row identification
Unique constraint (email)	Business key deduplication
Composite key (user_id, date)	Time-series deduplication

Partial Updates

You may want to update only certain columns on conflict:

ON CONFLICT (id) DO UPDATE SET
  email = EXCLUDED.email,
  score = GREATEST(users.score, EXCLUDED.score);
-- Only update score if the new value is higher

DO NOTHING

If you simply want to skip duplicates without updating:

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (id) DO NOTHING;