如何用SQL基于键对JSON对象求和并按关联表分组？

问题描述 #

给定包含JSON对象列的Table3，需结合Table1、Table2、Table3的关联关系（Table1通过Table2关联多个Table3），使用SQL按Table1分组，对对应Table3的JSON对象中相同键的值进行求和，最终返回每个Table1对应的汇总JSON。

示例表结构 #

Table1

id
1
2

Table2

id, table1_key, table3_key
1, 2, 3
2, 1, 4

Table3

id, values
1, {"A": 10, "B": -5}
2, {"A": 20}
3, {"A": -15, "B": -5}
4, {"A": -10, "C": 77}

预期结果 #

table1_id, summed_values
1, {"A": 5, "B": -5}  -- 对应Table3 id=2和3的求和
2, {"A": 0, "B": -5, "C": 77}  -- 对应Table3 id=1和4的求和

解决方案 #

不同数据库的JSON处理函数存在差异，以下是主流数据库的实现方式：

1. PostgreSQL（使用jsonb类型）

先拆分JSON为键值对行，关联多表后按Table1.id和JSON键分组求和，最后聚合回JSON对象：

SELECT 
  table1_id,
  jsonb_object_agg(json_key, total_value) AS summed_values
FROM (
  SELECT 
    t1.id AS table1_id,
    kv.key AS json_key,
    SUM((kv.value)::numeric) AS total_value
  FROM Table1 t1
  JOIN Table2 t2 ON t1.id = t2.table1_key
  JOIN Table3 t3 ON t2.table3_key = t3.id,
       jsonb_each_text(t3.values) kv  -- 拆分JSON为键值对
  GROUP BY t1.id, kv.key
) grouped_results
GROUP BY table1_id
ORDER BY table1_id;

2. MySQL 8.0+

利用JSON_TABLE拆分JSON键值对，再通过JSON_OBJECTAGG聚合回JSON：

SELECT
  t1.id AS table1_id,
  JSON_OBJECTAGG(jt.json_key, SUM(jt.json_value)) AS summed_values
FROM Table1 t1
JOIN Table2 t2 ON t1.id = t2.table1_key
JOIN Table3 t3 ON t2.table3_key = t3.id,
     JSON_TABLE(
       JSON_KEYS(t3.values),
       '$[*]' COLUMNS(
         json_key VARCHAR(255) PATH '$',
         json_value DECIMAL PATH CONCAT('$.', '$')
       )
     ) jt  -- 拆分JSON为键值对
GROUP BY t1.id
ORDER BY t1.id;

3. SQL Server 2016+

使用OPENJSON拆分JSON，再通过JSON_OBJECT_AGG聚合：

SELECT
  t1.id AS table1_id,
  JSON_OBJECT_AGG(jt.[key], SUM(CAST(jt.[value] AS DECIMAL(18,2)))) AS summed_values
FROM Table1 t1
JOIN Table2 t2 ON t1.id = t2.table1_key
JOIN Table3 t3 ON t2.table3_key = t3.id
CROSS APPLY OPENJSON(t3.values) jt  -- 拆分JSON为键值对
GROUP BY t1.id
ORDER BY t1.id;

内容的提问来源于stack exchange，提问作者Michael