0

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'])
soCooler
  • 1
  • 1

0 Answers0