ADF数据流中处理JSON复杂列：解决AssignedID数组分组异常

问题说明 #

原数据库表包含四列：ID、Name、Designation、AssignedID，需要转换为以下格式的JSON：

{"Employee":[{"id":value,"name":value,"designation":value,"Assignee":[{"assigneid":value1},{"assigneid":value2}]},
{"id":value,"name":value,"designation":value,"Assignee":[{"assigneid":value1},{"assigneid":value2}]}]}

核心要求：

• 按ID分组，每个Employee对应唯一ID的记录
• 将同一ID下的所有AssignedID整合到Assignee数组中（每个AssignedID对应一个独立的assigneid对象）

当前问题：AssignedID以逗号分隔多行存在，未整合为单个数组。

解决方案（按数据库类型分类） #

SQL Server #

使用STRING_AGG聚合AssignedID，结合FOR JSON PATH生成目标JSON结构：

SELECT 
    ID AS id,
    Name AS name,
    Designation AS designation,
    JSON_QUERY((
        SELECT AssignedID AS assigneid 
        FROM YourTableName t2 
        WHERE t2.ID = t1.ID 
        FOR JSON PATH
    )) AS Assignee
FROM YourTableName t1
GROUP BY ID, Name, Designation
FOR JSON PATH, ROOT('Employee')

解释：

1. 内层子查询为每个ID生成Assignee数组的JSON片段
2. GROUP BY确保每个ID只输出一条主记录
3. ROOT('Employee')将结果包裹在指定根节点中

MySQL #

利用JSON_ARRAYAGG和JSON_OBJECT实现聚合与结构生成：

SELECT JSON_OBJECT(
    'Employee', JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', ID,
            'name', Name,
            'designation', Designation,
            'Assignee', (
                SELECT JSON_ARRAYAGG(JSON_OBJECT('assigneid', AssignedID))
                FROM YourTableName t2
                WHERE t2.ID = t1.ID
            )
        )
    )
) AS result
FROM YourTableName t1
GROUP BY ID, Name, Designation;

解释：

1. 内层JSON_ARRAYAGG(JSON_OBJECT(...))将同一ID的AssignedID转为数组对象
2. 外层聚合所有Employee对象并包裹到Employee根节点中

PostgreSQL #

使用json_agg和json_build_object完成转换：

SELECT json_build_object(
    'Employee', json_agg(
        json_build_object(
            'id', ID,
            'name', Name,
            'designation', Designation,
            'Assignee', (
                SELECT json_agg(json_build_object('assigneid', AssignedID))
                FROM YourTableName t2
                WHERE t2.ID = t1.ID
            )
        )
    )
) AS result
FROM YourTableName t1
GROUP BY ID, Name, Designation;

解释：

• json_agg负责将多行数据聚合为JSON数组
• 嵌套子查询为每个ID生成对应的Assignee数组

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