Azure Data Factory数据流：嵌套无键JSON映射SQL表卡点咨询

核心思路 #

先把无键数组拆分为多行，给每个数组元素标记位置序号，再通过透视操作将行转成目标列，最终关联原数据的name和display_name生成单条记录。

具体操作步骤（以ETL数据流工具为例，如Azure Data Factory） #

假设你的输入JSON结构类似：

{
  "name": "group1",
  "display_name": "Group One",
  "values": [100, 200, 300]
}

1. 拆分数组为多行：使用「拆分行」转换，选中无键数组字段（比如values），将每个数组元素拆成单独一行，同时保留原有的name和display_name字段。
2. 添加元素位置序号：用「派生列」生成序号，按name和display_name分区，确保同一组的元素得到连续序号。
   • 派生列表达式示例：item_seq = rowNumber(partitionBy(name, display_name))
3. 透视转列：使用「透视」工具完成行转列：
   • 透视键：选择刚生成的item_seq
   • 透视值：选择数组元素对应的字段（比如拆分后的value）
   • 自定义列名：设置成你需要的格式，比如value_1、value_2等
4. 输出到SQL表：透视后的结果已按name和display_name分组为单条记录，直接写入目标SQL表即可。

若直接用SQL处理（以SQL Server为例） #

如果是在SQL中直接解析导入的JSON，可结合OPENJSON和条件聚合实现：

WITH split_data AS (
    SELECT
        JSON_VALUE(json_col, '$.name') AS name,
        JSON_VALUE(json_col, '$.display_name') AS display_name,
        CAST(value AS INT) AS item_val,
        ROW_NUMBER() OVER (
            PARTITION BY JSON_VALUE(json_col, '$.name'), JSON_VALUE(json_col, '$.display_name') 
            ORDER BY (SELECT NULL)
        ) AS item_seq
    FROM your_json_table
    CROSS APPLY OPENJSON(json_col, '$.values')
)
SELECT
    name,
    display_name,
    MAX(CASE WHEN item_seq = 1 THEN item_val END) AS value_1,
    MAX(CASE WHEN item_seq = 2 THEN item_val END) AS value_2,
    MAX(CASE WHEN item_seq = 3 THEN item_val END) AS value_3
    -- 数组元素数量不固定时，需改用动态SQL生成列
FROM split_data
GROUP BY name, display_name;

关键注意事项 #

• 若数组元素数量不固定，数据流工具需先统计每组的最大元素数来动态生成列；SQL场景则需要用动态SQL实现适配。
• 拆分和透视时，必须以name+display_name作为分区依据，避免不同组的数据混乱。

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