How to Update a Specific Object in a JSON Array in Mysql 5.7

Question

How can I update an object in an array based on a unique value in the object? Let's say this is my json object stored in a table called objects and in a column called content

table: objects

    id:  7383    
    content: { data:[{id: 111, active: 1 }, {id: 222, active: 1 }, {id: 333, active: 0 }] }

I can update objects if I know the position of the element in the array with

SET content = JSON_REPLACE(content,'$.data[1].active', 0)
Where id = 7383

However, if I don't know the position of the array, but I do know the value of id (for example 222) in the object, how can I update active to 0 for the object that has id: 222 ?

Answer 1

Currently, it's complicated to look up numerical values with MySQL JSON functions. In a JSON like the following, it would be simple:

{"id": "222", "active": 1}

There are many ways to get what you need, I present one that can give you ideas (modify everything that is necessary):

UPDATE `objects`
SET `objects`.`content` = 
  JSON_REPLACE(`objects`.`content`, CONCAT('$.data',
  (SELECT
    JSON_UNQUOTE(
      REPLACE(
        JSON_SEARCH(
          REPLACE(
            REPLACE(
              REPLACE(
                `der`.`content` ->> '$.data[*].id',
                ', ',
                '","'),
              ']',
              '"]'),
          '[',
          '["'),
        'one',
        '222'),
      '$',
      '')
    )
  FROM (SELECT `objects`.`content`
        FROM `objects`
        WHERE `objects`.`id` = 7383) `der`
  ), '.active'), 0)
WHERE `objects`.`id` = 7383;

Beware of possible performance problems.

See dbfiddle.

In the most recent version of MySQL (>= 8.0.4), the sentence would be much simpler:

UPDATE `objects`
  INNER JOIN JSON_TABLE(
    `objects`.`content`,
    '$.data[*]' COLUMNS(
      `rowid` FOR ORDINALITY,
      `id` INT PATH '$.id'
    )
  ) `der` ON `der`.`id` = 222
SET `objects`.`content` =
  JSON_REPLACE(
    `objects`.`content`,
    CONCAT('$.data[', `der`.`rowid` - 1, '].active'),
    0)
WHERE
  `objects`.`id` = 7383;

See db-fiddle.