Maria를 사용하여 Json 개체 내부의 특정 값으로 필드 업데이트DB

Maria를 사용하여 Json 개체 내부의 특정 값으로 필드 업데이트DB

MariaDB의 json 열에 저장된 데이터를 업데이트하려고 합니다(libmysql version - 5.6.43, Server: 10.34-MariaDB-cll-lve - MariaDB Server).

데이터는 다음과 같이 구성되어 있습니다.

아이디	json_data
1	{....}
2	{....}

여기서 json_data는 다음과 같이 구성됩니다.

{
    "company": {
        "id": "",
        "name": "",
        "address": ""
    },
    "info_company": {
          "diff_v": "1",
          "grav_v": "",
          "diff_s": "2",
          "grav_s": "",
          "diff_g": "3",
          "grav_g": "",
          "diff_ri": "4",
          "grav_ri": "2"
    }
}

info_company 내의 데이터를 갱신하려고 합니다.대체처리는 다음과 같습니다.

• '1'과 '50% 미만'
• '2'와 '50% 미만'
• '3'과 '50% 이상'
• '4'와 '50% 이상'

결과는 다음과 같습니다.

{
    "company": {
        "id": "",
        "name": "",
        "address": ""
    },
    "info_company": {
          "diff_v": "<50%",
          "grav_v": "",
          "diff_s": "<50%",
          "grav_s": "",
          "diff_g": ">50%",
          "grav_g": "",
          "diff_ri": ">50%",
          "grav_ri": "<50%"
    }
}

이 쿼리를 작성하면 info_company 데이터를 가져올 수 있지만 포함된 각 키에 대해 새 값에 따라 데이터를 업데이트할 수 없습니다.

SELECT new_t.id, JSON_EXTRACT(new_t.json_data, “$.info_company“) FROM (SELECT * FROM `my_table` WHERE json_data LIKE “%info_company%”) new_t

출력:

아이디	json_data
1	{"diff_v": "1", "diff_s": "2", "diff_s": "3", "diff_g": "4", "diff_ri": "2"

도와주셔서 고맙습니다.

이 문제는 CTE를 사용하여 내부의 키(및 원하는 일치값)와 일치하는 정규식을 생성함으로써 해결할 수 있습니다.info_company그 후 를 사용하여REGEXP_REPLACE을 교체하다1또는2와 함께<50%및 a3또는4와 함께>50%:

UPDATE my_table
JOIN (
  WITH jkeys_table AS (
    SELECT id, JSON_KEYS(json_data, '$.info_company') AS jkeys
    FROM my_table
  )
  SELECT id,
         CONCAT('((?:',
                REPLACE(SUBSTRING(jkeys, 2, CHAR_LENGTH(jkeys)-2), ', ', '|'),
                ')\\s*:\\s*)"([12])"'
         ) AS regex12,
         CONCAT('((?:',
                REPLACE(SUBSTRING(jkeys, 2, CHAR_LENGTH(jkeys)-2), ', ', '|'),
                ')\\s*:\\s*)"([34])"'
         ) AS regex34
  FROM jkeys_table
) rt ON my_table.id = rt.id
SET json_data = REGEXP_REPLACE(REGEXP_REPLACE(json_data, regex12, '\\1"<50%"'), regex34, '\\1">50%"')

출력(샘플 JSON의 경우):

id  json_data
1   {
        "company": 
        {
            "id": "",
            "name": "",
            "address": ""
        },
        "info_company": 
        {
            "diff_v": "<50%",
            "grav_v": "",
            "diff_s": "<50%",
            "grav_s": "",
            "diff_g": ">50%",
            "grav_g": "",
            "diff_ri": ">50%",
            "grav_ri": "<50%"
        }
    }

dbfiddle 데모

가능한 경우 키를 입력해 주세요.info_company내부의 다른 곳에 존재할 수 있다json_data에 대한 변경을 현지화해야 합니다.info_company요소.이 조작을 실시하려면 , 다음의 순서를 변경해 주세요.SET의 조항UPDATE대상:

SET json_data = JSON_REPLACE(json_data, '$.info_company',
                JSON_MERGE_PATCH(JSON_QUERY(json_data, '$.info_company'),
                                 REGEXP_REPLACE(REGEXP_REPLACE(JSON_QUERY(json_data, '$.info_company'), regex12, '\\1"<50%"'), regex34, '\\1">50%"')
                                )
                )

dbfiddle 데모

키가 입력된 경우info_company모든 행이 동일하기 때문에 쿼리를 최적화할 수 있습니다.regex12그리고.regex34값을 한 번 지정한 후 해당 값을 의 모든 행에 적용합니다.my_table사용방법CROSS JOIN:

UPDATE my_table
CROSS JOIN (
  WITH jkeys_table AS (
    SELECT JSON_KEYS(json_data, '$.info_company') AS jkeys
    FROM my_table
    LIMIT 1
  )
  SELECT CONCAT('((?:',
                REPLACE(SUBSTRING(jkeys, 2, CHAR_LENGTH(jkeys)-2), ', ', '|'),
                ')\\s*:\\s*)"([12])"'
         ) AS regex12,
         CONCAT('((?:',
                REPLACE(SUBSTRING(jkeys, 2, CHAR_LENGTH(jkeys)-2), ', ', '|'),
                ')\\s*:\\s*)"([34])"'
         ) AS regex34
  FROM jkeys_table
) rt
SET json_data = REGEXP_REPLACE(REGEXP_REPLACE(json_data, regex12, '\\1"<50%"'), regex34, '\\1">50%"')

dbfiddle 데모

MariaDB 10.34 데이터베이스 서버에서 테스트 완료json_data:

DELIMITER //
CREATE PROCEDURE percentage()
BEGIN
SELECT @info_keys:=JSON_KEYS(json_data, "$.info_company") FROM my_table;
SELECT @info_keys_num:=JSON_LENGTH(@info_keys);
WHILE @info_keys_num >= 0 DO
   SET @info_keys_num = @info_keys_num - 1;
   SELECT @info_attr:=JSON_EXTRACT(@info_keys, CONCAT("$[", @info_keys_num, "]"));
   UPDATE my_table SET json_data = JSON_REPLACE(json_data, CONCAT("$.info_company.", @info_attr), "<50%") 
      WHERE CHAR_LENGTH(JSON_VALUE(json_data, CONCAT("$.info_company.", @info_attr))) = 1 AND
            JSON_VALUE(json_data, CONCAT("$.info_company.", @info_attr)) < 3;
   UPDATE my_table SET json_data = JSON_REPLACE(json_data, CONCAT("$.info_company.", @info_attr), ">50%") 
      WHERE CHAR_LENGTH(JSON_VALUE(json_data, CONCAT("$.info_company.", @info_attr))) = 1 AND
            JSON_VALUE(json_data, CONCAT("$.info_company.", @info_attr)) > 2;
END WHILE;
END;
//
DELIMITER ;

call percentage();

출력 예:

MariaDB [test]> call percentage();
+------------------------------------------------------------------------------------+
| @info_keys:=JSON_KEYS(json_data, "$.info_company")                                 |
+------------------------------------------------------------------------------------+
| ["diff_v", "grav_v", "diff_s", "grav_s", "diff_g", "grav_g", "diff_ri", "grav_ri"] |
+------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

... [cut here] ...

Query OK, 5 rows affected (0.011 sec)

언급URL : https://stackoverflow.com/questions/71645485/update-a-field-with-specific-value-inside-a-json-object-with-mariadb