Append an array column in mapping data flow's derived column transformation in ADF

SARASWAT CHETAN 91

I have an existing array of multiple JSON objects coming out of a select transformation and I have to append all the objects within that array with some more "default" key-value" pairs(string type) using a derived column.

Could anyone suggest any possible way of doing these. I have tried using below to get all the items(json based objects) from the array, but not able to add more key-value pairs to each objects.

![77394-image.png ]1

Note the four fields below "devicearray" are the one's which are to added to the array of objects.

Accepted answer

Kiran-MSFT 696 Reputation points Microsoft Employee

2021-03-15T05:56:27.503+00:00

map(userdevices.devices, @(
each(#item, match(true), $$),
createdtime = currentDate(),
isActive = true
)
)

If the UI does not validate this, instead of each(#item, match(true), $$), you have to specify every item

like
deviceId = #item.deviceId,
deviceName = #item.deviceName
...
Please sign in to rate this answer.
SARASWAT CHETAN 91 Reputation points

2021-03-15T07:01:03.77+00:00

Thank you. This helped. Got the logic and the desired output. Cheers
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

2 additional answers

Kiran-MSFT 696 Reputation points Microsoft Employee

2021-03-15T01:17:12.3+00:00

You can just do arr1 + arr2 if there are of the same type
Please sign in to rate this answer.
SARASWAT CHETAN 91 Reputation points

2021-03-15T05:32:53.71+00:00

My initial array is an array of multiple JSON objects(key-value pair combination) and the values I need to append to each object within the initial array are again some key-value pairs.

Ex: Initial array
devices:[
{
deviceid:
devicename:
createdtime:
}
{
deviceid:
devicename:
createdtime:
}
{
deviceid:
devicename:
createdtime:
}
]
Now I need to append each of the above object with some more key-value pairs as below:
devices:[
{
deviceid:
devicename:
createdtime:
devicetype:
createdby:
isactive:
}
I am able to fetch the initial array using map() function within a derived column, but am unable to add it to the items(json objects).
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Kiran-MSFT 696 Reputation points Microsoft Employee

2021-03-15T05:38:24.29+00:00

What does your DSL script look like(click on script button)?
Please sign in to rate this answer.
SARASWAT CHETAN 91 Reputation points

2021-03-15T05:42:41.38+00:00

77528-script.txt

Script contains the complete dataflow I am working on. This involves joining up data from multiple sources and fetching the initial array as mentioned above and append it with more values.

Kiran-MSFT 696 Reputation points Microsoft Employee

2021-03-15T05:44:13.237+00:00

Click on the script button not the code. It will give a formatted script.

SARASWAT CHETAN 91 Reputation points

2021-03-15T05:48:36.507+00:00

77653-script.txt

I had attached the script only(code looks like below) . It got re-formatted while saving. Attaching it again.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Append an array column in mapping data flow's derived column transformation in ADF

2 additional answers

Your answer