Changing JSON in place in SQL

Editing a document without parsing it in your application first.

UPDATE webhooks
SET payload = JSON_SET(payload, '$.processed', 1)
WHERE payload ->> '$.kind' = 'order.paid';

SELECT
    JSON_INSERT(payload, '$.retries', 0) AS with_retries,
    JSON_REPLACE(payload, '$.kind', 'order.settled') AS renamed,
    JSON_REMOVE(payload, '$.tags') AS without_tags,
    JSON_PATCH(payload, '{"kind":"order.void","note":"merged"}') AS patched
FROM webhooks;

How it works

1. json_set adds or overwrites; json_insert refuses to overwrite.
2. json_replace only touches paths that already exist.
3. json_remove deletes a path; json_patch merges a whole object in.

Keywords and builtins used here

• AS
• FROM
• SELECT
• SET
• UPDATE
• WHERE

The run, in numbers

Lines

10

Characters to type

366

Tokens

65

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 41 seconds.

Type this snippet

Step 3 of 5 in Writing JSON, step 6 of 14 in JSON & search.

← Previous Next →