如何在Athena中对含键值对JSON列的数据按用户分组求和

我有一张Athena表ABC，其中test_column列存储着JSON格式的键值对数据。示例查询及数据如下：

SELECT test_client, test_column FROM ABC;

返回结果：

需要按test_client分组，对每个键对应的数值进行求和，期望得到类似如下格式的结果（输出格式不做严格要求，核心是完成求和）：

john: d: 23, e:310, a:110, b:10
mark: a:57, b:10, c:12, f:9, e:10

解决方案 #

1. 解析JSON并拆分键值对 #

先用json_extract_map解析JSON列成键值映射，再通过UNNEST将映射拆分为单独的键值行：

SELECT
  test_client,
  kv.key AS json_key,
  CAST(kv.value AS INT) AS json_value
FROM ABC
CROSS JOIN UNNEST(json_extract_map(test_column)) AS kv

这一步会把每条JSON数据拆分成多行，比如john的第一条数据会拆成john, d, 13和john, e, 210。

2. 分组计算求和 #

基于上一步的结果，按test_client和json_key分组求和：

SELECT
  test_client,
  json_key,
  SUM(json_value) AS total_value
FROM (
  SELECT
    test_client,
    kv.key AS json_key,
    CAST(kv.value AS INT) AS json_value
  FROM ABC
  CROSS JOIN UNNEST(json_extract_map(test_column)) AS kv
)
GROUP BY test_client, json_key
ORDER BY test_client, json_key

执行后会得到每个用户对应每个键的求和结果，比如john, a, 110、mark, c, 12等。

3. （可选）聚合为目标字符串格式 #

如果需要将同一用户的所有求和结果合并成一行字符串，使用array_agg和concat_ws实现：

SELECT
  test_client,
  CONCAT(
    test_client, ': ',
    CONCAT_WS(', ', ARRAY_AGG(CONCAT(json_key, ': ', total_value)))
  ) AS result
FROM (
  SELECT
    test_client,
    json_key,
    SUM(CAST(kv.value AS INT)) AS total_value
  FROM ABC
  CROSS JOIN UNNEST(json_extract_map(test_column)) AS kv
  GROUP BY test_client, json_key
)
GROUP BY test_client
ORDER BY test_client

执行后输出结果类似：

john | john: a: 110, b: 10, d: 23, e: 310
mark | mark: a: 57, b: 10, c: 12, e: 10, f: 9

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