Update mysql json query with python

Question

I have a json field suppose from table T1 and field F1 {"X":[{"a":"1","t":"2"},{"a":"2","t":"4"}]} If i want to update my t=2 to t=10 for a:1, how to do this? What will be the normal mysql syntax?

i tried something like this it just update t=10 value separately

UPDATE T1 set F1 = JSON_SET(F1, '{"a":"1","$.t", "10"}') where id = 1; 

tried with this..it just add another value to the same field like

{"X":[{"a":"1","t":"2"},{"a":"2","t":"4"}],"t":"10"}

Thanks in advance..

Answer 1

Your path parameter is wrong. You need to index into the X array to get to the property you want to change.

UPDATE T1
SET F1 = JSON_SET(F1, '$.X[0].t', '10')
WHERE id = 1

To search for a = 1 and update the corresponding t, you can use JSON_SEARCH. It returns a string with quotes around it, so you have to remove those. Then you have to replace .a with .t to get the property you want to set.

UPDATE T1
SET F1 = JSON_SET(F1, REPLACE(REPLACE(JSON_SEARCH(F1, 'one', '1', null, '$**.a'), '"', ''), '.a', '.t'), '10')
WHERE id = 1;