Is there a way in MySQL to loop through nested JSON and update them?

Question

For example, I have a field like the following, and I want to update all the current values to the initial values.

 {
 "dataset": {
    "field1": {
      "current": "Customized",
      "initial": "Initial1"
    },
    "field2": {
      "current": "Something else...",
      "initial": "Initial2"
    }
  }
}

After updating, it should be like this

 {
 "dataset": {
    "field1": {
      "current": "Initial1",
      "initial": "Initial1"
    },
    "field2": {
      "current": "Initial2",
      "initial": "Initial2"
    }
  }
}

Is there any way to do this purely in MySQL? Thanks a lot!

Parse (JSON_TABLE() function), update, reconstruct JSON. For example.. — Akina, May 09 '22 at 10:45
This is gonna be slow. Like... **really slow**. If you find yourself needing to look _inside_ JSON records in any relational database, it almost always means it's time to start looking at your schema definition and including a few of those fields as part of the table, to maintain along with each INSERT/UPDATE. — Joel Coehoorn, May 09 '22 at 14:38

D-Shih · Answer 1 · 2022-05-09T14:40:08.900

I search mysql JSON document there doesn't seem to have an easy function that can replace nested JSON object structure.

We can try to use dynamic SQL with JSON_EXTRACT and json_keys function.

First, we need to find updating fields of key and updated fields and use json_table with json_keys to get all field* which is your updated root node.

current：updated value.
initial：updating value.

the result will look like

'$.dataset.field1.current', JSON_EXTRACT(col, '$.dataset.field1.initial'),
'$.dataset.field2.current', JSON_EXTRACT(col, '$.dataset.field2.initial'),
'$.dataset.field3.current', JSON_EXTRACT(col, '$.dataset.field3.initial')

Then we can try to use JSON_REPLACE from the connect string variable.

Replaces existing values in a JSON document and returns the result.

so that final script might be like.

SET @updateFields= '';

SELECT @updateFields := group_concat(DISTINCT '''$.dataset.',fieldKey,'.current'', JSON_EXTRACT(col, ''$.dataset.',fieldKey,'.initial'')') 
FROM T,
    json_table(
        json_keys(col,'$.dataset'),
        '$[*]' COLUMNS(fieldKey VARCHAR(50) PATH '$')
    ) t;
    
set @sql := CONCAT('UPDATE T SET col = JSON_REPLACE(col,',@updateFields,')');
                
                
PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;

sqlfiddle

Is there a way in MySQL to loop through nested JSON and update them?

1 Answers1