Modifying json in azure data factory to give nested list

Question

Im retrieving data from sql server in Azure Data Factory. The API im passing it to requires the Json in a specific format.

I have been unable to get the data in the required format so far, trying "for json output" in tsql.

is there a way to do this in data factory with the data it retrieved from SQL Server?

SQL Server Output

EntityName      customField1 CustomField2  
------------------------------------------  
AA01            NGO21        2022-01-01  
AA02            BS34         2022-03-01  

How it appears in Data Factory

[  
{"EntityName": "AA01", "CustomField1": "NGO21", "CustomField2":"2022-01-01"},  
{"EntityName": "AA02", "CustomField1": "BS32", "CustomField2":"2022-03-01"}  
]  

Required output

[  
    {  
        "EntityName": "AA01"  
        "OtherFields":[{"customFieldName": "custom field 1, "customFieldValue": "NGO21"},{"customFieldName": "custom field 2", "customFieldValue": "2022-01-01"} ]  
    },  
    {  
        "EntityName": "AA02"  
        "OtherFields":[{"customFieldName": "CustomField1", "customFieldValue": "BS34"},{ "CustomFieldName":"CustomField2", "customFieldValue" : "2022-03-01"}]  
    }  
]  

Answer 1

Hi @na ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question.

As I understand your query, you want to convert SQL data into Nested JSON .

You can achieve this by using mapping dataflow in Azure data factory.

1. In source transformation , point the dataset to the Azure SQL table having the source data.

2. Use Unpivot transformation and provide the required details in all the tabs like below:

This is how the output from unpivot transformation would look like:

3. Use Aggregate transformation to group the data by Entity column and convert the other fields as items of the OtherFields[] array.

This is the output coming from aggregate transformation:

4. Use sink transformation to load the data into .json file.

Check the output file:

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on and take satisfaction survey whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators