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

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!

mysqlsqljsonmysql-json


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions