1

I'm using MySQL 5.7.12, and have a JSON column with the following data:

[{"tpe": "I", "val": 1}, {"tpe": "C", "val": 2}, {"tpe": "A", "val": 3}]

I would like to UPDATE val from 2 into 20 WHERE tpe='C'.

Here is my attempt:

UPDATE user SET data = JSON_SET(data->"$[1]", '$.val', 20);

This does update the value but it trims the other elements in the array and it becomes only a json-object, here how it looks after the update:

{"tpe": "C", "val": 20}

How can I get this right?

2nd question: is there a way to dynamically get the json object in the array so I don't have to hard code "$[1]" ? I tried to use JSON_SEARCH ??

Shvalb
  • 1,835
  • 2
  • 30
  • 60
  • Please only ask one question at a time. Your second question is answered here: https://stackoverflow.com/questions/56193438/mysql-return-json-array-index-based-on-property-value/56315064#56315064 – Barmar Feb 11 '22 at 03:00
  • Problems like this are why putting JSON into SQL tables is a horrible idea. Normalize your tables and it becomes trivial. – Barmar Feb 11 '22 at 03:02

0 Answers0