0

I have a column that saves JSON string and I want to get rid of "zipcode" for that JSON object. How can I achieve that using MySQL functions?

{name:"Alex", "address": [
 {"type": "primary", "street": "bakers street", "zipcode": 90001},
 {"type": "secondary", "street": "honor street", "zipcode": 90002}
]
}

I tried this one select JSON_REMOVE(JSON_EXTRACT(JSON_EXTRACT(@payload_json,'$. address'), '$[0]'),'$. zipcode') but it results like

     {"type": "primary", "street": "bakers street",},
     {"type": "secondary", "street": "honor street", "zipcode": 90002}
    ]
    }``` 

> Is there a way I could do it all at once?
ps_403
  • 23
  • 4
  • Please add your trials and findings to the question. – Ahmed Hammad May 19 '20 at 23:08
  • I used ```update user_info ui set ui.info_json = JSON_REMOVE(info_json, '$. zipcode');``` – ps_403 May 19 '20 at 23:13
  • 1
    You cannot access the JSON key-value pairs using SQL, it is just a string to it. You will need to update the whole JSON string but without the `zipcode`. – Ahmed Hammad May 19 '20 at 23:15
  • Which version of MySQL are you using? – Nick May 20 '20 at 00:13
  • @AhmedHammad can you post an example of how to do it? – ps_403 May 20 '20 at 01:13
  • I tried this one ```select json_remove(JSON_EXTRACT(json_extract(@payload_json,'$. address'), '$[0]'),'$. zipcode')``` and it works but I would need to run a loop to update all the object in the array is there a way I could do all at once?? – ps_403 May 20 '20 at 01:16

0 Answers0