5

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

1 Answer 1

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.