如何在Snowflake中从表数据生成嵌套JSON（反向lateral flatten）

核心结论 #

可以实现反向操作，该过程本质是分组聚合+嵌套结构构造，和点表示法读取JSON的逻辑完全对应，最终可以实现父级值无重复、子级数据自动归入对应父级条目的效果，也就是你要的反向lateral flatten能力。
核心实现逻辑如下：

• 先提取所有父级维度字段，按父级字段做分组去重，保证父级值唯一
• 将同一父级分组下的所有子级数据，聚合为数组/对象格式，作为父级对应的嵌套属性
• 可按需自定义嵌套层级，最终输出结构化数据或JSON格式即可

常用场景实现示例 #

数仓SQL场景（以Snowflake为例，适配原生lateral flatten逆操作） #

假设你已经通过lateral flatten得到了打平后的表，结构如下：

通过OBJECT_CONSTRUCT和ARRAY_AGG组合即可实现反向嵌套：

SELECT 
  parent_id,
  parent_name,
  -- 构造嵌套的子级数组，对应点表示法读取路径 *.children
  ARRAY_AGG(
    OBJECT_CONSTRUCT(
      'child_id', child_id,
      'child_name', child_name
    )
  ) AS children
FROM 已打平的表
GROUP BY parent_id, parent_name -- 按父级字段分组保证无重复父级值
-- 如果需要直接输出整行JSON，在外层再套一层OBJECT_CONSTRUCT包装所有字段即可

Python代码处理场景 #

如果是本地处理打平后的字典列表，可通过分组映射实现嵌套构造：

from collections import defaultdict

# 示例打平数据
flat_data = [
    {"parent_id":1, "parent_name":"父级A", "child_id":101, "child_name":"子级A1"},
    {"parent_id":1, "parent_name":"父级A", "child_id":102, "child_name":"子级A2"},
    {"parent_id":2, "parent_name":"父级B", "child_id":201, "child_name":"子级B1"},
]

parent_map = defaultdict(lambda: {"children": []})
for item in flat_data:
    # 父级唯一标识
    parent_key = (item["parent_id"], item["parent_name"])
    # 仅写入一次父级信息避免重复
    if not parent_map[parent_key].get("parent_id"):
        parent_map[parent_key]["parent_id"] = item["parent_id"]
        parent_map[parent_key]["parent_name"] = item["parent_name"]
    # 追加对应子级数据
    parent_map[parent_key]["children"].append({
        "child_id": item["child_id"],
        "child_name": item["child_name"]
    })

# 最终得到父级无重复的嵌套结果
nested_result = list(parent_map.values())

如果需要自定义更深的嵌套层级，只需要对应增加分组维度和嵌套构造层级即可，和点表示法的读取路径完全一一对应。

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