PrestoDB中按ID分组将多行转为键值对JSON数组的SQL实现问题

核心实现逻辑 #

你对array_agg的认知存在偏差：Presto的array_agg函数支持聚合包括JSON在内的任意可哈希类型，无需额外转换，直接对你已经生成的单行JSON对象做分组聚合即可。 #

最优实现代码（推荐） #

使用Presto内置的json_object函数构造单行JSON对象，写法更简洁易读，执行效率也更高：

SELECT
  id,
  array_agg(
    json_object(
      'col1': col1,
      'col2': col2,
      'col3': col3
    )
  ) AS value
FROM your_table
GROUP BY id

如果你习惯用原逻辑的MAP转JSON写法，也可以直接聚合：

SELECT
  id,
  array_agg(
    CAST(MAP(ARRAY['col1','col2','col3'], ARRAY[k.col1, k.col2, k.col3]) AS JSON)
  ) AS value
FROM your_table k
GROUP BY id

低版本兼容实现方案 #

如果你的Presto版本较低，不支持直接聚合JSON类型，可以先将单行JSON转字符串，聚合后再拼接转换为JSON数组：

SELECT
  id,
  CAST(
    CONCAT('[', array_join(array_agg(CAST(
      MAP(ARRAY['col1','col2','col3'], ARRAY[k.col1, k.col2, k.col3])
      AS JSON
    )::VARCHAR), ','), ']')
    AS JSON
  ) AS value
FROM your_table k
GROUP BY id

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