Update specific key/value of json object inside json array using MySQL syntax

user_1720101 0
Viewed 886

I have MySQL 5.7.12 DB with a table that has a JSON column.

The data in the column as the following structure (json array may contain more than 2 json-objects:

[{"ste": "I", "tpe": "PS"}, {"ste": "I", "tpe": "PB"}]

I would like to craft an UPDATE query that changes the value of ste where tpe=PB.

Here is my attempt:

UPDATE user SET ext = JSON_SET(JSON_SEARCH(ext, 'one', 'PB', NULL, '$**.tpe'), '$.tpe', 'A');

The output if the query should give:

[{"ste": "I", "tpe": "PS"}, {"ste": "A", "tpe": "PB"}]

It doesn't work - it updates the column to be:

"$[0].tpe"

How can I make this work?

EDIT

I think this makes more sense but still something wrong with syntax

UPDATE user SET ext = JSON_SET(ext->JSON_SEARCH(ext, 'one', 'PS', NULL, '$**.tpe'), '$.tpe', 'A');
mysql-json mysql
2 Answers

Hope you still need this.

Try using variable path in you JSON_SET. Use JSON_SEARCH to get the variable path the replace the the absolute path tpe with ste to update its value. Works for me!

update user set ext= JSON_SET(ext, REPLACE(REPLACE(JSON_SEARCH(ext, 'one', 'PB'),'"',''),'tpe','ste'), 'A');
user_135074770

If I understand question correctly ext column of user table has below value

[{"ste": "I", "tpe": "PS"}, {"ste": "I", "tpe": "PB"}]

and ask is for UPDATE Query to modify 2nd index of value like below

[{"ste": "I", "tpe": "PS"}, {"ste": "A", "tpe": "PB"}]

Recommended & Easy Solution based on Problem Statement considering json structure is fixed

Update Query based on Json array index

update user set ext = JSON_SET(ext, '$[1].ste', 'A') where primary_key = 'primary_key_criteria';

select ext from user where primary_key = 'primary_key_criteria';

Output after Update

[{"ste": "I", "tpe": "PS"}, {"ste": "A", "tpe": "PB"}]
user_168626620
Related