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`)
)

Bill Karwin · Accepted Answer · 2022-12-11 21:52:45Z

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"]}       |
+------------------------------------------------------------------------------------------------+

Stack Exchange Network

MySql Json Replace An array Value

1 Answer 1

Hot Network Questions

MySql Json Replace An array Value

1 Answer 1

Related

Hot Network Questions