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 ??

Improve this question
2
  • Please only ask one question at a time. Your second question is answered here: stackoverflow.com/questions/56193438/… Commented Feb 11, 2022 at 3:00
  • Problems like this are why putting JSON into SQL tables is a horrible idea. Normalize your tables and it becomes trivial. Commented Feb 11, 2022 at 3:02
Related questions
0
PHP/MySQL update table rows from JSON array
1
How to update multiple json object in one row?
0
Updateing mysql json field in array
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.