Generating SQL UPDATE Statements from JSON Patch Data

JSON to SQL UPDATE

Not all JSON-to-SQL conversions are INSERT operations. When you receive a partial JSON object representing changes to an existing record, you need an UPDATE statement instead.

Example JSON (Partial Update)

{
  "id": 42,
  "name": "Alice Johnson",
  "email": "alice.johnson@newdomain.com",
  "score": 99
}

Generated SQL

UPDATE users SET
  name = 'Alice Johnson',
  email = 'alice.johnson@newdomain.com',
  score = 99
WHERE id = 42;

Identifying the WHERE Clause

The converter needs to know which field(s) identify the row. Common strategies:

1. Primary key field -- use id by default
2. User-specified key -- let the user select the WHERE column
3. Composite key -- use multiple fields (WHERE user_id = 1 AND date = '2024-06-15')

Excluding the Key from SET

The WHERE column(s) should not appear in the SET clause:

-- Wrong: id in both SET and WHERE
UPDATE users SET id = 42, name = 'Alice' WHERE id = 42;

-- Correct: id only in WHERE
UPDATE users SET name = 'Alice' WHERE id = 42;

Null Handling in Updates

{ "id": 42, "bio": null, "phone": null }

UPDATE users SET bio = NULL, phone = NULL WHERE id = 42;

Setting a column to NULL is a valid and common operation for clearing optional fields.

Batch Updates from JSON Arrays

[
  { "id": 1, "score": 95 },
  { "id": 2, "score": 87 },
  { "id": 3, "score": 92 }
]

UPDATE users SET score = 95 WHERE id = 1;
UPDATE users SET score = 87 WHERE id = 2;
UPDATE users SET score = 92 WHERE id = 3;

UPDATE vs UPSERT

Scenario	Statement
Row definitely exists	UPDATE
Row may or may not exist	INSERT ... ON CONFLICT ... DO UPDATE
Row must not exist	INSERT (with unique constraint)

Conditional Updates

For more complex logic, the converter can generate CASE expressions:

UPDATE users SET
  score = CASE
    WHEN id = 1 THEN 95
    WHEN id = 2 THEN 87
    WHEN id = 3 THEN 92
  END
WHERE id IN (1, 2, 3);

This performs all updates in a single statement, which is more efficient than individual UPDATE statements.