Mysql 5.7에서 JSON 어레이 내의 특정 개체를 갱신하는 방법

Mysql 5.7에서 JSON 어레이 내의 특정 개체를 갱신하는 방법

개체의 고유 값을 기반으로 어레이 내의 개체를 업데이트하려면 어떻게 해야 합니까?오브젝트라는 테이블과 content라는 컬럼에 저장되어 있는json 오브젝트라고 하겠습니다

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으로 업데이트하려면 어떻게 해야 합니까?

현재 MySQL JSON 함수로 수치 조회가 복잡합니다.다음과 같은 JSON에서는 심플합니다.

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

필요한 것을 얻을 수 있는 방법은 여러 가지가 있습니다.아이디어(필요한 것을 모두 수정)를 제공할 수 있는 것을 소개합니다.

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)에서는 다음과 같은 문장이 훨씬 단순해집니다.

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을 참조하십시오.

JSON_SEARCH 함수를 조합하여 필요한 항목에 더러운 json 경로를 반환한 다음 json 경로 값을 배열 인덱스와 추출하고 업데이트할 하위 경로와 연결하고 JSON_SET을 사용하여 최종 json 경로로 새 값을 설정합니다(MySQL 5732에서 테스트됨).

-- INPUT ------------------------------------------------
-- unique value for an object in the array
SET @unique_value = "12345";

-- object field we want to update
SET @field_to_update = '.myField';

-- new value
SET @new_value = 1;

-- PROCESSING ------------------------------------------
-- Get json path to the item with specified @unique_value
-- RESULT: $.data[6].id
SET @temp_path = ( TRIM(BOTH '"' FROM ( SELECT JSON_SEARCH(json, 'one', @unique_value, NULL, "$.data")
FROM `my-table`
WHERE `column1` = "abcd" ) ));

-- We are looking for the bracket that delimits index within the array of documents: [11]
SET @closing_bracket_index = (SELECT LOCATE(']', @temp_path));

-- Get json path with index of an object for @unique_value
-- in MySQL, string indexing starts from position 1, not a zero
-- RESULT: $.data[6]
SET @item_path = ( SELECT SUBSTRING(@temp_path, 1, @closing_bracket_index) );

-- $.data[6].myFIeld
SET @item_path_to_update = ( SELECT CONCAT(@item_path, @field_to_update) );

-- UPDATE JSON STATEMENT
UPDATE `my-table`
SET json = JSON_SET(json-column, @item_path_to_update, @new_value)
WHERE `column1` = "abcd";

언급URL : https://stackoverflow.com/questions/51545084/how-to-update-a-specific-object-in-a-json-array-in-mysql-5-7