Is there a fast way to update one json column data with MySQL?

Question

There is a column in a table as

table: articles
rows:  id           type: int(11)
       json_data    type: json
       created_at   type: datetime
       updated_at   type: datetime

Now the json_data is

{
   "version":"1",
   "title":"A good product",
   "body":"Very good",
   "published_at":null
}

Want to update its data to new json schema format based on the current meta data:

{
   "version":"2",
   "items":[
      {
         "title":"A good product",
         "body":"Very good",
         "published_at":null
      }
   ]
}

How to do it if not use a programming language. Is it possible to do it by MySQL's procedure?

Answer 1

UPDATE articles
SET json_data = JSON_OBJECT('version', 2, 
                            'items', JSON_ARRAY(JSON_REMOVE(json_data, '$.version')));

fiddle

---

If run the update command twice, the data will become nested and break the format - fiddle. Do you know how to avoid it even run many times? – iooi

Add proper WHERE, check "$.version", update only those rows where this value is 1:

UPDATE articles
SET json_data = JSON_OBJECT('version', 2, 'items', JSON_ARRAY(JSON_REMOVE(json_data, '$.version')))
WHERE json_data->>"$.version" = 1;

fiddle