mariadb - 使用 MariaDB 更新 Json 对象中具有特定值的字段

Question

我正在尝试更新存储在 MariaDB 的 json 列中的数据(libmysql 版本 - 5.6.43，服务器:10.3.34-MariaDB-cll-lve - MariaDB 服务器)。

我的数据结构如下:

<表类="s-表"><头>身份证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

输出:

<表类="s-表"><头>身份证json_data<正文>1{"diff_v": "1","grav_v": "","diff_s": ​​"2","grav_s": ​​"","diff_g": "3","grav_g": "","diff_ri": "4","grav_ri": "2"

感谢您的帮助。

Answer 1

您可以通过使用 CTE 生成正则表达式来匹配 info_company 中的键(和所需的匹配值)来解决此问题。然后使用 REGEXP_REPLACE替换 1或 2与 <50%和一个 3或 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%"
        }
    }

Demo on 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%"')
                                )
                )

Demo on 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%"')

Demo on dbfiddle