如何在Mysql 5.7中更新JSON数组中的特定对象 [英] How to Update a Specific Object in a JSON Array in Mysql 5.7

问题描述

如何根据对象中的唯一值更新数组中的对象? 假设这是我的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 }] }

如果我知道

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 .

这篇关于如何在Mysql 5.7中更新JSON数组中的特定对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！