MySQL syntax for updating specific key/value pairs in JSON array

Question

I have a table in a MySQL 5.7.12 database with a JSON column.

The data in the column has the following structure (the json array may contain multiple json objects):

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

I want to write a UPDATE query to change the value of ste of tpe=PB.

This is my attempt:

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

The output of the query should be:

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

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

"$[0].tpe"

How do I make it work?

edit

I think this makes more sense, but the syntax is still problematic

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

Answer 1

Hope you still need this.

Try using a variable path in your JSON_SET. Use JSON_SEARCH to get the variable path, then replace absolute pathtpe 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');