Snowflake生成含多实例同名结构的JSON查询方案求助

在Snowflake中有一张表，包含三列名称类型/值字段，需要转换为适配MDM工具的指定JSON格式，且不能重复使用同一属性名（否则报错）。

表结构 #

目标JSON结构 #

"OrgNames": [
                {
                    "value": {
                        "OrgName": [
                            {
                                "value": "AL_PL"
                            }
                        ],
                        "OrgNameType": [
                            {
                                "value": "NameType1"
                            }
                        ]
                    }
                },
                {
                    "value": {
                        "OrgName": [
                            {
                                "value": "Alpha Place"
                            }
                        ],
                        "OrgNameType": [
                            {
                                "value": "NameType2"
                            }
                        ]
                    }
                },
                {
                    "value": {
                        "OrgName": [
                            {
                                "value": "Alpha Place Main Store"
                            }
                        ],
                        "OrgNameType": [
                            {
                                "value": "NameType3"
                            }
                        ]
                    }
                }
            ],

原方案仅支持生成单个结果，当需要多组键值对时会出现键冲突，且Snowflake的struct限制仅支持一个参数，需解决该问题。

核心思路是先将宽表转为长表（把三列NameType字段通过UNPIVOT转换为多行键值对），再基于长表构建目标JSON结构，彻底避免键冲突问题。

步骤1：Unpivot宽表为长表 #

将原表的三列转换为两列（名称类型、对应值），每行对应一组独立的NameType-Value：

WITH unpivoted_data AS (
    SELECT 
        tablespaceID,
        DEPARTMENTID,
        name_type,
        name_value
    FROM 
        YOUR_TABLE_NAME -- 替换为你的表名
    UNPIVOT (
        name_value FOR name_type IN (NameType1, NameType2, NameType3)
    )
)

步骤2：构建单个OrgName对象 #

基于长表每行数据，生成符合要求的单个OrgName结构：

, org_name_objects AS (
    SELECT 
        tablespaceID,
        DEPARTMENTID,
        OBJECT_CONSTRUCT_KEEP_NULL(
            'value', OBJECT_CONSTRUCT_KEEP_NULL(
                'OrgName', ARRAY_AGG(OBJECT_CONSTRUCT_KEEP_NULL('value', name_value)),
                'OrgNameType', ARRAY_AGG(OBJECT_CONSTRUCT_KEEP_NULL('value', name_type))
            )
        ) AS org_name_entry
    FROM 
        unpivoted_data
    GROUP BY 
        tablespaceID, DEPARTMENTID, name_type, name_value
)

步骤3：聚合生成最终OrgNames数组 #

按tablespaceID和DEPARTMENTID分组，聚合所有单个OrgName结构，生成目标JSON：

SELECT 
    tablespaceID,
    DEPARTMENTID,
    OBJECT_CONSTRUCT_KEEP_NULL(
        'OrgNames', ARRAY_AGG(org_name_entry)
    ) AS final_json
FROM 
    org_name_objects
GROUP BY 
    tablespaceID, DEPARTMENTID;

关键说明 #

• UNPIVOT是核心：将宽表转为长表后，每组NameType-Value成为独立行，后续构建JSON时不会出现键重复。
• 分组逻辑：通过tablespaceID和DEPARTMENTID确保同一组织的所有名称类型被聚合到同一个OrgNames数组中。
• 空值保留：使用OBJECT_CONSTRUCT_KEEP_NULL确保空字段也能保留对应结构，适配MDM工具的格式要求。

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