在MySQL文档中合并两个JSON对象主键

Question

我有一个存储文档的JSON列。我想对这一列执行原子更新。

例如，给定值：

[{"substanceId": 182, "text": "substance_name_182"}, {"substanceId": 183, "text": "substance_name_183"}]

和更新

[{"substanceId": 182, "text": "substance_name_182_new"}, {"substanceId": 184, "text": "substance_name_184"}]

我希望得到这样的结果：

[{"substanceId": 182, "text": "substance_name_182_new"}, {"substanceId": 183, "text": "substance_name_183"} {"substanceId": 184, "text": "substance_name_184"}}]

没有一个JSON_MERGE_PATCH或JSON_MERGE_PRESERVE直接允许我实现它。JSON_MERGE_PATCH不知道substanceId是文档的ID。有没有办法在MySQL端实现这一点？我可以在客户端做这件事(首先获取值，修改并更新它)，但那是最后的手段，首先我有很多行要更新，其中所有行都将由带有WHERE子句的UPDATE覆盖，所以MySQL方法会更方便。当在客户端安全地执行此操作时，我将不得不锁定许多行以进行更新。

例如查询：

SELECT JSON_MERGE_PATCH(
'[{"substanceId": 182, "text": "substance_name_182"}, {"substanceId": 183, "text": "substance_name_183"}]',
'[{"substanceId": 182, "text": "substance_name_182_new"}, {"substanceId": 184, "text": "substance_name_184"}]'
) v;

结果

[{"text": "substance_name_182_new", "substanceId": 182}, {"text": "substance_name_184", "substanceId": 184}]

Answer 1

下面是在MySQL中执行此操作的一种极其复杂的方法，使用JSON_TABLE将更新的JSON值和原始JSON值转换为列，使用(模拟的) FULL JOIN合并列，然后使用JSON_OBJECT和JSON_ARRAYAGG重新创建输出JSON值；最后使用该方法更新原始表：

WITH upd AS (
  SELECT *
  FROM JSON_TABLE('[{"substanceId": 182, "text": "substance_name_182_new"}, {"substanceId": 184, "text": "substance_name_184"}]',
                  '$[*]' COLUMNS (
                  substanceId INT PATH '$.substanceId',
                  txt VARCHAR (100) PATH '$.text')
                  ) jt
  CROSS JOIN (SELECT DISTINCT id
              FROM test) t
),
cur AS (
  SELECT id, substanceId, txt
  FROM test
  JOIN JSON_TABLE(test.j,
                  '$[*]' COLUMNS (
                  substanceId INT PATH '$.substanceId',
                  txt VARCHAR (100) PATH '$.text')
                 ) jt
),
allv AS (
  SELECT COALESCE(upd.id, cur.id) AS id, 
         COALESCE(upd.substanceId, cur.substanceId) AS substanceId,
         COALESCE(upd.txt, cur.txt) AS txt
  FROM upd
  LEFT JOIN cur ON cur.substanceId = upd.substanceId
  UNION ALL 
  SELECT COALESCE(upd.id, cur.id) AS id, 
         COALESCE(upd.substanceId, cur.substanceId) AS substanceId,
         COALESCE(upd.txt, cur.txt) AS txt
  FROM upd
  RIGHT JOIN cur ON cur.substanceId = upd.substanceId
),
obj AS (
  SELECT DISTINCT id, JSON_OBJECT('substanceId', substanceId, 'text', txt) AS o
  FROM allv
),
arr AS (
  SELECT id, JSON_ARRAYAGG(o) AS a
  FROM obj
  GROUP BY id
)
UPDATE test
JOIN arr ON test.id = arr.id
SET test.j = arr.a
;
SELECT JSON_PRETTY(j)
FROM test

输出：

[
  {
    "text": "substance_name_183",
    "substanceId": 183
  },
  {
    "text": "substance_name_184",
    "substanceId": 184
  },
  {
    "text": "substance_name_182_new",
    "substanceId": 182
  }
]

Demo on dbfiddle

注这假设您使用唯一的id值来区分表中的行。如果您使用其他东西，则需要在上面的查询中使用id的地方交换它。