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 aUPDATEquery to change the value ofsteoftpe=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');

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