如何从表格数据创建Snowflake半结构化OBJECT对象？

完全可以在Snowflake内部直接生成你需要的半结构化对象，无需导出再导入数据。通过组合使用Snowflake的半结构化函数即可实现，核心用到OBJECT_AGG、ARRAY_AGG和OBJECT_CONSTRUCT。

完整实现SQL #

WITH test_hierarchy AS (
    SELECT *
    FROM (VALUES 
              ('2023-06-23'::DATE, 'A', 1),
              ('2023-06-23'::DATE, 'A', 2),
              ('2023-06-23'::DATE, 'A', 3),
              ('2023-06-23'::DATE, 'B', 4),
              ('2023-06-23'::DATE, 'B', 5),
              ('2023-06-26'::DATE, 'A', 1),
              ('2023-06-26'::DATE, 'A', 2),
              ('2023-06-26'::DATE, 'A', 5),
              ('2023-06-26'::DATE, 'B', 3),
              ('2023-06-26'::DATE, 'B', 4)) 
          AS t ("DATE", "PARENT", "CHILD")
),
parent_child_objects AS (
    SELECT 
        "DATE",
        OBJECT_CONSTRUCT(
            'parent', "PARENT",
            'children', ARRAY_AGG("CHILD")
        ) AS parent_obj
    FROM test_hierarchy
    GROUP BY "DATE", "PARENT"
)
SELECT OBJECT_AGG("DATE"::STRING, ARRAY_AGG(parent_obj)) AS final_result
FROM parent_child_objects
GROUP BY ALL;

实现逻辑拆解 #

• 基础数据CTE：定义原始层级数据，方便直接测试验证。
• 生成单个parent-children对象：按DATE和PARENT分组，用ARRAY_AGG("CHILD")把同一父节点下的子节点聚合成数组，再通过OBJECT_CONSTRUCT包装成{"parent": "...", "children": [...]}的结构化对象。
• 构建顶层日期映射对象：按DATE分组，用ARRAY_AGG(parent_obj)把同一日期下的所有parent对象聚合成数组，最后通过OBJECT_AGG将日期字符串作为键、对应的parent对象数组作为值，生成目标顶层大对象。

输出效果 #

执行上述SQL后，会得到符合需求的半结构化对象：

{
    "2023-06-23": [
        {"parent": "A", "children": [1,2,3]},
        {"parent": "B", "children": [4,5]}
    ],
    "2023-06-26": [
        {"parent": "A", "children": [1,2,5]},
        {"parent": "B", "children": [3,4]}
    ]
}

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