How to update multiple json object in one row?

Question

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 ?

wchiquito · Accepted Answer · 2017-11-10 16:09:31Z

1

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.

edited Nov 10, 2017 at 16:09

answered Nov 10, 2017 at 12:13

wchiquito

16.7k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

오범선 Over a year ago

oh! i see it in db-fiddle. it work fine! at one glance, i can not figure out. anyway i need to figure out! thanks for your answer..

Collectives™ on Stack Overflow

How to update multiple json object in one row?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related