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"}]
    }
]

user_6788885 · Answer

I managed to do it in ADF, the solution is quite long, i think if you will write a stored procedure, it will be much easier.

Here is a quick demo that i built:

the idea is to build the Json structure as requested and then using collect function to build the array.

we have 2 arrays, one for EntityName and one for OtherFields.

Prepare the data:

First, i added column names in the corresponding rows, we will use this info later on to build our Json, i used a Derived column activity to fill the rows with column names.

Splitting Columns: In order to build the Json structure as requested, i split the data into two parallel flows.

first flow is to select CustomFieldName1 and CustomFieldValue1 and the second flow is to select CustomFieldName2 and CustomFieldValue2 like so:

SelectColumn2 Activity:

SelectColumn1 Activity:

Note: Please keep the EntityName, We will Union the data by it later on in the flow.

OtherFields Column:

In order to build the Json, we need to do it using Sub-columns feature in a Derived column activity, that will ensure to us the Json structure.

Add new column with a name 'OtherFields' and open Expression Builder: add 2 subcolumns : CustomFieldName and CustomFieldValue, add CustomFieldName1 as a value for the subcolumn CustomFieldName and add CustomFieldValue1 to the CustomFiedValue column like so:

Add a derived column activity and repeat same steps to CustomFieldName2.

Union:

Now we have 2 flows, one for extracting field1 and field2, we need to Union the data (you can do it by position or by name)

In order to create an array of Json we need to aggregate the data; this will transform complex data type {} into array []

Aggregate Activity:

Group by -> 'EntityName' Aggregates -> collect(OtherFields)

Building Outer Json:

as described in the question above , we need to have a json that consists of 2 columns : {"EntitiyName" :"" , "OtherFields":[]}

In order to do it, again we need to add a derived column and add 2 subcolumns,

also, in order to combine all Json's in one Json array, we need a common value so we can aggregate by it, since we have different values, i added a dummy value with a constant 1, this will guarantee to us that all Json's will be under the same array