TSQL实现每行JSON数组关联Record表填充TransactionId

我有一张包含JSON列的表，每行的JSON内容示例如下：

{"DoublesIds":[{"RecordId":415070},{"RecordId":4370908}]}

另有一张独立的Record表，存储RecordId与TransactionId的对应关系：

需要将原JSON转换为以下格式，把每个RecordId对应的TransactionId合并到JSON数组的对象中：

{
  "DoublesIds": [
    {"RecordId":415070, "TransactionId": 12357},
    {"RecordId":4370908, "TransactionId": 12354}
  ]
}

解决方案 #

PostgreSQL 实现 #

借助PostgreSQL的jsonb系列函数解析JSON、关联数据并重新构造目标JSON：

假设原表名为your_table，JSON列名为json_data，执行以下SQL：

SELECT 
  jsonb_build_object(
    'DoublesIds',
    jsonb_agg(
      jsonb_set(item, '{TransactionId}', to_jsonb(r.TransactionId))
    )
  ) AS new_json_data
FROM your_table,
     jsonb_array_elements(json_data->'DoublesIds') AS item
JOIN Record r ON (item->>'RecordId')::bigint = r.RecordId
GROUP BY your_table.id; -- 替换为原表实际主键字段

MySQL 8.0+ 实现 #

利用MySQL的JSON函数完成解析、关联与重组：

假设原表名为your_table，JSON列名为json_data，执行以下SQL：

SELECT 
  JSON_OBJECT(
    'DoublesIds',
    JSON_ARRAYAGG(
      JSON_MERGE_PRESERVE(
        item,
        JSON_OBJECT('TransactionId', r.TransactionId)
      )
    )
  ) AS new_json_data
FROM your_table,
     JSON_TABLE(
       json_data->'$.DoublesIds',
       '$[*]' COLUMNS (RecordId BIGINT PATH '$.RecordId')
     ) AS item
JOIN Record r ON item.RecordId = r.RecordId
GROUP BY your_table.id; -- 替换为原表实际主键字段

内容的提问来源于stack exchange，提问作者karol.etrych