mysql update json attribute and another column in one query

Question

I need to update a json value in a column as well as update another column in the same query.

Something like this:

UPDATE fixtures 
SET jsonResults = '{}', 
    JSON_SET(jsonFixture, '$.time_status', '0') 
WHERE intRefID = 88961323;

How can I accomplish this?

score 1 · Accepted Answer · answered Sep 27 '20 at 01:20

JSON_SET() returns a JSON document value, but an UPDATE statement needs a series of assignment expressions:

UPDATE fixtures 
SET jsonResults = '{}', 
    jsonFixture = JSON_SET(jsonFixture, '$.time_status', '0') 
WHERE intRefID = 88961323;

This replaces jsonFixture with the result of JSON_SET(), after setting a field within that document.

Compare with an UPDATE like this:

UPDATE mytable
SET i = i + 1
WHERE ...

It takes the value of i, adds 1, and then uses the result of that addition expression to replace i.

1 Answers1