如何在Azure Data Factory中转换SQL Server数据源的JSON格式为嵌套列表结构

Absolutely, you can handle this JSON transformation directly within Azure Data Factory (ADF) — no need to rely solely on T-SQL's FOR JSON feature if it's not producing your desired output. Here are two practical approaches tailored to your scenario:

Approach 1: Quick Derived Column (Fixed Custom Fields) #

If your custom field list (CustomField1, CustomField2) won't change, you can directly construct the target JSON structure using a Derived Column transformation:

1. Add a Derived Column to your data flow.
2. Create a new column (e.g., TargetJSON) with this expression to build the nested structure:

   @(
       EntityName = EntityName,
       OtherFields = [
           @(customFieldName = 'custom field 1', customFieldValue = CustomField1),
           @(customFieldName = 'custom field 2', customFieldValue = CustomField2)
       ]
   )
   

3. Use a Select transformation to keep only the TargetJSON column.
4. Configure your JSON sink to output as an Array of objects — this will generate exactly the format you need.

Approach 2: Unpivot + Aggregate (Scalable for More Custom Fields) #

If you expect to add more custom fields later, this method is more flexible:

Step 1: Unpivot Custom Fields #

Use the Unpivot transformation to convert your custom columns into key-value pairs:

• Unpivot Key: Select CustomField1 and CustomField2; name the output column FieldName.
• Unpivot Value: Name the corresponding value column FieldValue.
  This will turn your original rows into:

Step 2: Clean Up Field Names (Optional) #

If you need to rename CustomField1 to custom field 1 (as in your target format), add a Derived Column with a conditional expression:

case(
    FieldName == 'CustomField1', 'custom field 1',
    FieldName == 'CustomField2', 'custom field 2',
    FieldName
)

Overwrite the FieldName column with this result.

Step 3: Aggregate to Build the OtherFields Array #

Use the Aggregate transformation to group by EntityName and assemble the nested array:

• Group By: Select EntityName.
• Aggregate Column: Create OtherFields with this expression:

  collect(@(customFieldName = FieldName, customFieldValue = FieldValue))
  

This will group all key-value pairs for each EntityName into the OtherFields array.

Step 4: Output as JSON #

Configure your JSON sink to output the aggregated data as an array of objects, and you'll get your target format.

Bonus: Fixing T-SQL FOR JSON (If You Prefer) #

If you still want to use T-SQL, adjust your query to explicitly build the nested array:

SELECT 
    EntityName,
    (
        SELECT 'custom field 1' AS customFieldName, CustomField1 AS customFieldValue
        UNION ALL
        SELECT 'custom field 2' AS customFieldName, CustomField2 AS customFieldValue
        FOR JSON PATH
    ) AS OtherFields
FROM YourTableName
FOR JSON PATH

This should produce the exact nested structure you need directly from SQL Server.

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