MySQL update specific JSON objects in an array

Question

I am struggling to find a way to update specific JSON objects in a array within JSON-type field in MySQL. Let's say I have the following object:

SET @j = '{
 "cat": "meow", 
 "dog": "woof", 
 "parrot": [
   {"volume": "quiet", "says": "hello"}, 
   {"volume": "loud", "says": "polly"},
   {"volume": "loud", "says": "cracker"}
  ]
}';

How can I update all objects in the parrot array that have the volume value as loud?

I know I could use the JSON_SET or JSON_REPLACE functions to change/update a specific object if the position of the object is known. For example something like:

UPDATE T1 SET @J = JSON_SET(@j, '$.parrot[1].says', 'pretty bird');

However I don't know the positions of the objects and also this doesn't update all in the parrot array that have the volume value as loud?

Any suggestions?

We have the same concern. Did you find (and perhaps test) a solution for this? Thanks — pAkY88
– pAkY88, Commented Oct 15, 2020 at 16:49
@pAkY88 After some testing I simply chose to handle the data changes at the application level instead at database level. In my case it was a quicker and easier solution. — Kaspar
– Kaspar, Commented Oct 19, 2020 at 18:26

wchiquito · Accepted Answer · 2018-02-01 20:43:07Z

One option:

DROP PROCEDURE IF EXISTS `sp_update_json`;

DELIMITER //

CREATE PROCEDURE `sp_update_json`(
  `json` JSON,
  `value` VARCHAR(255)
)
BEGIN
  DECLARE `array_objects` JSON DEFAULT
    REPLACE(JSON_SEARCH(`json`,
                'all',
                'loud',
                NULL,
                '$.parrot[*].volume'
               ), 'volume', 'says');
  DECLARE `max_objects` INT UNSIGNED DEFAULT 
    JSON_LENGTH(`array_objects`);
  DECLARE `current_object` INT UNSIGNED DEFAULT 0;
  WHILE `current_object` < `max_objects` DO
    SET `json` := JSON_REPLACE(`json`, 
                           JSON_UNQUOTE(                             
                             JSON_EXTRACT(
                               `array_objects`,
                               CONCAT('$[', `current_object`, ']')
                             )
                           ), `value`);
    SET `current_object` := `current_object` + 1;
  END WHILE;
  SELECT `json`;
END//

DELIMITER ;

SET @`j` := '
{
  "cat": "meow", 
  "dog": "woof", 
  "parrot": [
    {"volume": "quiet", "says": "hello"}, 
    {"volume": "loud", "says": "polly"},
    {"volume": "loud", "says": "cracker"}
  ]
}';

CALL `sp_update_json`(@`j`, 'pretty bird');

See db-fiddle.

Collectives™ on Stack Overflow

MySQL update specific JSON objects in an array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related