Search code examples
mysqlmysql-workbenchmysql-json

mysql update json attribute and another column in one query

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?

Solution

  • 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.