3

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');
Shvalb
  • 1,835
  • 2
  • 30
  • 60

3 Answers3

0

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');
Juma George
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Albert Logic Einstein Aug 19 '22 at 02:04
0

If this is still needed.

JSON_SEARCH is giving output with quotes, so first you have to replace quotes.

Syntax :

UPDATE table_name SET json_col_name=JSON_SET(json_col_name, replace(JSON_SEARCH(json_col_name, 'one' ,'search_value') , "\"",""),'replace_value')
Sahitya Jain
  • 1
  • 1
-1

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"}]
Dharman
  • 30,962
  • 25
  • 85
  • 135
DpakG
  • 56
  • 1
  • 1
  • 10
  • You can't use `'$[1].ste` in the query because the json array might contains more json objects. that's why we need to use `JSON_SEARCH` for searching for certain criteria, e.g: `tpe=PB`. – Shvalb Feb 09 '22 at 00:57
  • @Shvalb : It looks question was not clear & I clearly mentioned in answer that index solution is based on assumption if we want to update particular record. Will try again.. Down vote is discouraging :( – DpakG Feb 09 '22 at 02:04
  • I'm sorry! didn't mean that! I actually impressed with your detailed answer of breaking my question into pieces. – Shvalb Feb 09 '22 at 02:34