MySql Json Replace An array Value

Question

MySql Json Replace An array Value at Specific index if the value matches some condition

Example:

{
 "deploy": [
 "Infrastructure",
 "API Security"
 ],
 "operate": [
 "Pen Testing",
 "Bug Bounty"
 ]
}

here I want to replace the value Of Infrastructure with Infrastructure Tools

here in this example the index Of Infrastructure is at 0 but this may vary for other rows

query I have tried to solve this is

update table_config 
set config = JSON_SET(config,JSON_SEARCH(config,'one',"Infrastructure"),"Infrastructure");

which doesn't run

the following is my table:

CREATE TABLE `table_config` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `config` longtext,
 `type` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
)

Answer 1

The result from JSON_SEARCH() is not a valid JSON path. It's quoted, like a JSON string value. Notice the double-quotes:

mysql> select json_search(config, 'one', 'Infrastructure') 
 from table_config;
+----------------------------------------------+
| json_search(config, 'one', 'Infrastructure') |
+----------------------------------------------+
| "$.deploy[0]" |
+----------------------------------------------+

So if you try to use it as the path argument in JSON_SET(), it doesn't work:

mysql> select json_set(config, json_search(config, 'one', 'Infrastructure'), 'Infrastructure')
 from table_config;
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 1.

To use this as a JSON path, you have to remove those quotes:

mysql> select json_unquote(json_search(config, 'one', 'Infrastructure')) 
 from table_config;
+------------------------------------------------------------+
| json_unquote(json_search(config, 'one', 'Infrastructure')) |
+------------------------------------------------------------+
| $.deploy[0] |
+------------------------------------------------------------+

Then you can use it in a call to JSON_SET():

mysql> select json_set(config, json_unquote(json_search(config, 'one', 'Infrastructure')), 'Infrastructure') 
 from table_config;
+------------------------------------------------------------------------------------------------+
| json_set(config, json_unquote(json_search(config, 'one', 'Infrastructure')), 'Infrastructure') |
+------------------------------------------------------------------------------------------------+
| {"deploy": ["Infrastructure", "API Security"], "operate": ["Pen Testing", "Bug Bounty"]} |
+------------------------------------------------------------------------------------------------+