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 691 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

2 additional answers

Kiran-MSFT 691 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
Kiran-MSFT 691 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 691 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