CODEWITHSUNDEEP
mysqljson
오범선
오범선

Reputation: 35

How to update multiple json object in one row?

Hello I work on JSON in MySQL. I bumped into trouble...

for example, I have data as below

{
     1:{fan:0},
     2:{fan:3},
     3:{fan:4},
}

And I wish update every fan value to 6 : How to do this in MySQL statement?

My below statement gives "in this situation path expressions may not contain the * and ** tokens"

UPDATE mytable set json = JSON_REPLACE(json,'$.*."Fan"', 6);

Is there any simple way to achieve this ?

Upvotes: 1

Views: 928

Answers (1)

wchiquito
wchiquito

Reputation: 16551

One option is to use a stored routine (Function). Modify the script as you need:

DROP FUNCTION IF EXISTS `update_json`;

DELIMITER //

CREATE FUNCTION `update_json` (
  `json` JSON,
  `key` CHAR(5),
  `value` TINYINT
) RETURNS JSON
BEGIN
  DECLARE `_keys` JSON DEFAULT JSON_KEYS(`json`);
  DECLARE `_length_keys` TINYINT UNSIGNED DEFAULT JSON_LENGTH(`_keys`);
  DECLARE `_current_key` TINYINT UNSIGNED DEFAULT 0;
  DECLARE `_key` VARCHAR(10);
  WHILE `_current_key` < `_length_keys` DO
    SET `_key` := JSON_EXTRACT(`_keys`, CONCAT('$[', `_current_key`, ']')),
        `json` := JSON_REPLACE(`json`, CONCAT('$.', `_key`,'.', `key`), `value`),
        `_current_key` := `_current_key` + 1;
  END WHILE;
  RETURN `json`;
END//

DELIMITER ;

UPDATE `mytable`
SET `json` = `update_json`(`json`, '"fan"', 6);

See db-fiddle.

Upvotes: 1

Related Questions