How to add OR merge JSON data into a JSON field of an existing MySQL row?

Question

I have an existing table like that:

id | json
---+----------
1  | {"1":"1"}
2  | NULL

and i want to be able to merge {"2":"2"} into any one of the existing rows wheater or not the json field already contains JSON or is NULL. That it looks like that:

id | json
---+----------
1  | {"1":"1", "2":"2"}
2  | {"2":"2"}

with UPDATE table SET json = CAST('{"2":"2"}' AS JSON) WHERE id=1) I can only update the second row or overwrite the first one.

with UPDATE table SET json = JSON_MERGE(json, CAST('{"2":"2"}' AS JSON) WHERE id=1) I can only update the first row, the second row stays unchanged.

Is there a single command that accounts for both cases?

Tim Biegeleisen · Accepted Answer · 2018-06-07 11:05:18Z

3

One approach would be to COALESCE the current json column to the equivalent of empty string in JSON terms:

UPDATE yourTable
SET json = JSON_MERGE(COALESCE(json, CAST('{}' AS JSON)), CAST('{"2":"2"}' AS JSON))
WHERE id = 1;

Another quick and dirty approach here would be to use a CASE expression to decide whether you merge or simply overwrite:

UPDATE yourTable
SET json = CASE WHEN json IS NOT NULL
                THEN JSON_MERGE(json, CAST('{"2":"2"}' AS JSON))
                ELSE CAST('{"2":"2"}' AS JSON) END
WHERE id = 1;

answered Jun 7, 2018 at 11:05

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add OR merge JSON data into a JSON field of an existing MySQL row?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related