I want to write a query to update the field_2's value of an json_array, but it works wrong. The given values cannot be mapped to the fields (such as field_2:a,b,c). Could you help me to find the errors of it? Thanks.
1.table_A
|-------------------------------------
| id | field_1 | field_2 | field_3 |
|-------------------------------------
| char | char | json | char |
|-------------------------------------
2.table_A filed's value
{
id:'xxx',
field_1:'111',
field_2:{
a:'aaa',
b:'bbb',
c:[
{"a":"00","b":"01","c":02},
{"a":"10","b":"11","c":12}
],
d:100
}
field_3:'333',
}
3.my query
update table_A
set field_2 =
json_set(
field_2,
'$.c',
json_object(
'afiled','aaa',
'bfiled',1010,
'$.cfiled[0].a','c00',
'$.cfiled[0].b','c01',
'$.cfiled[0].c',11,
'$.cfiled[1].a','c10',
'$.cfiled[1].b','c11',
'$.cfiled[1].c',22,
'dfiled',100
)
)
where id = 'xxx' (old version : [where key = 'xxx'])