MySQL JSON合并JSON文档中的两个json对象主键 [英] MySQL JSON merge two json objects primary key in JSON document

问题描述

我有一个存储文档的 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": "物质名称_184"}}]

JSON_MERGE_PATCH 或 JSON_MERGE_PRESERVE 都不允许我直接实现它.JSON_MERGE_PATCH 不知道 substanceId 是文档的 ID.有什么方法可以在 MySQL 端实现它?我可以在客户端进行(首先获取值，修改并更新它)但这是最后的手段，首先我有很多行要更新，所有这些行都将被 UPDATE 覆盖code>WHERE 子句，所以 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}]

解决方案

这是在 MySQL 中执行此操作的一种非常复杂的方法，使用 JSON_TABLE 将更新和原始 JSON 值转换为列，使用(模拟) FULL JOIN 然后使用 JSON_OBJECT 和 JSON_ARRAYAGG;最后使用它来更新原始表:

WITH upd AS (选择 *FROM JSON_TABLE('[{"substanceId": 182, "text": "substance_name_182_new"}, {"substanceId": 184, "text": "substance_name_184"}]','$[*]' 列 (物质 ID INT PATH '$.substanceId',txt VARCHAR (100) 路径 '$.text')) jt交叉连接(选择不同的 id来自测试) t),当前 AS (选择 id、substanceId、txt从测试加入 JSON_TABLE(test.j,'$[*]' 列 (物质 ID INT PATH '$.substanceId',txt VARCHAR (100) 路径 '$.text')) jt),所有的 AS (SELECT COALESCE(upd.id, cur.id) AS id,COALESCE(upd.substanceId，cur.substanceId)作为substanceId，COALESCE(upd.txt, cur.txt) AS txt从更新左连接 cur ON cur.substanceId = upd.substanceId联合所有SELECT COALESCE(upd.id, cur.id) AS id,COALESCE(upd.substanceId，cur.substanceId)作为substanceId，COALESCE(upd.txt, cur.txt) AS txt从更新右连接 cur ON cur.substanceId = upd.substanceId),对象 AS (SELECT DISTINCT id, JSON_OBJECT('substanceId', SubstanceId, 'text', txt) AS o来自所有),arr AS (SELECT id, JSON_ARRAYAGG(o) as a从对象按 ID 分组)更新测试加入 arr ON test.id = arr.idSET test.j = arr.a;选择 JSON_PRETTY(j)从测试

输出:

<预><代码>[{"text": "substance_name_183",物质 ID":183},{"text": "substance_name_184",物质 ID":184},{"text": "substance_name_182_new",物质 ID":182}]

dbfiddle 演示

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

I've got JSON column storing a document. I want to perform atomic UPDATEs on this column.

Given e.g. value:

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

and the update

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

I expect to get this:

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

None of the JSON_MERGE_PATCH or JSON_MERGE_PRESERVE directly allow me to achieve it. The JSON_MERGE_PATCH is not aware substanceId being an ID of the document. Is there any way to achieve it on the MySQL side? I could do it client-side (fetch value first, modify and update it back) but that's last resort, firstly I have lots of rows to update, where all of them would be covered by UPDATE with WHERE clause, so the MySQL way would be more convenient. When doing it client-side and do it safely I would have to LOCK many rows FOR UPDATE.

E.g. query:

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;

results

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

解决方案

Here is an insanely convoluted way of doing this in MySQL, using JSON_TABLE to convert the update and original JSON values into columns, merging the columns using a (simulated) FULL JOIN and then re-creating the output JSON value using JSON_OBJECT and JSON_ARRAYAGG; finally using that to update the original table:

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

Output:

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

Demo on dbfiddle

Note this assumes you use a unique id value to distinguish the rows in your table. If you use something else, you would need to swap that in where id is used in the above query.

这篇关于MySQL JSON合并JSON文档中的两个json对象主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！