如何在Snowflake中生成双层嵌套JSON？

你当前的SQL问题在于直接按id聚合时，同一placeId下的多条记录会被生成分开的对象，没有实现内层按placeId聚合的结构。需要通过两层分组先构建内层placeId对应的actionId-resultValue映射，再组装外层结构。

正确查询写法 #

WITH subquery AS (
  SELECT id, placeId, actionId, resultValue
  FROM my_table
),
-- 第一层分组：按id和placeId聚合，生成每个placeId下的actionId-resultValue对象
place_level AS (
  SELECT 
    id,
    placeId,
    OBJECT_AGG(actionId, resultValue) AS action_result_map
  FROM subquery
  GROUP BY id, placeId
)
-- 第二层分组：按id聚合，生成外层的resultValues结构
SELECT 
  id,
  OBJECT_CONSTRUCT('resultValues', OBJECT_AGG(placeId, action_result_map)) AS nested_json
FROM place_level
GROUP BY id;

关键说明 #

• OBJECT_AGG(key_expr, value_expr)：Snowflake内置的JSON聚合函数，直接将分组内的键值对聚合为JSON对象，避免手动拼接字符串容易出错的问题。
• 两层分组逻辑：
  1. 先按id+placeId分组，把同一地点下的所有actionId和resultValue聚合成一个JSON对象。
  2. 再按id分组，把每个placeId对应的对象聚合到外层的resultValues中，形成双层嵌套结构。

如果需要确保JSON的键顺序，可以在OBJECT_AGG中添加ORDER BY子句，例如：OBJECT_AGG(actionId, resultValue ORDER BY actionId)。

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