如何在 Mysql 5.7 中更新 JSON 数组中的特定对象

2022-01-15 00:00:00 mariadb mysql

如何根据对象中的唯一值更新数组中的对象?假设这是我的 json 对象，存储在名为 objects 的表和名为 content 的列中

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

但是，如果我不知道数组的位置，但我知道对象中 id 的值(例如 222)，如何将具有 id: 222 的对象的 active 更新为 0 ?

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 ?

推荐答案

目前，使用 MySQL JSON 函数查找数值比较复杂.在像下面这样的 JSON 中，这很简单:

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;

注意可能的性能问题.

请参阅 dbfiddle.

在最新版本的 MySQL (>= 8.0.4) 中，这句话会简单得多:

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;

请参阅 db-fiddle.

相关文章