How do I update a data value stored in an array of JSON data stored in MySQL dB

Question

I have JSON data in an array in a dB. I would like to update the value for one field.

This returns the array:

SELECT id, data 
from ComponentResult 
WHERE id = 2272;

This returns the last element of this array:

SELECT id, json_extract(data, '$[last]') 
from ComponentResult 
WHERE id = 2272;

This returns the object containing the field I want to update:

SELECT json_extract(json_extract(data, '$[last]'), '$.AllResults') 
from ComponentResult 
WHERE id = 2272;`

This returns the value I want to update:

SELECT id, json_extract(json_extract(json_extract(data, '$[last]'), '$.AllResults'),'$.Accuracy') 
from ComponentResult 
WHERE id = 2272;

It is currently set to -99 and I want to change it to something else, like 86.

I have tried the following:

json_set(json_extract(json_extract(data, '$[last]'), '$.PrimaryResults') 
from ComponentResult 
WHERE id = 2272, '$.Accuracy',202);

update t set json_col = json_set(json_col, json_extract(json_extract(json_extract(data, '$[last]'), '$.AllResults'),'$.Accuracy'),'$.Accuracy',22) 
from ComponentResult 
WHERE id = 2272;`

Ideally, it would be nice to actually take this last element of the array, change the one value, then put this updated element back into the array as a new "last" value. This allows later audit of the current and previous values.

Thank you

score 1 · Answer 1 · answered May 30 '23 at 14:08

This returns the array [...]

This returns the last element of this array [...]

This returns the object containing the field I want to update [....]

As as starter: you don't need to nest the calls to json_extract to navigate through the JSON document. Instead, you can use MySQL JSON path syntax; this gives you the current value of the attribute you want to change:

select id, json_extract(data, '$[last].AllResults.Accuracy') 
from ComponentResult 
where id = 2272

If you wanted to change that value to 86 in the document, you would use json_set like so:

update ComponentResult
set data = json_set(data, '$[last].AllResults.Accuracy', 86) 
where id = 2272

Note that storing JSON data in a relational database is not a good idea in general, especially if you need to actually update it.

How do I update a data value stored in an array of JSON data stored in MySQL dB

1 Answers1