Hi Guys, Is there a simple way to append to a JSON object within Azure Data Factory? We retrieve the JSON object from Cosmos. The type is shown as 'complex' We then create calculated columns. We then want to add those columns within the JSON object. I know we could create an azure function but was wondering if there was anything we could do in ADF. Your advice will be appreciated.

Hi @Shezad Chishti , Thank you for posting query in Microsoft Q&A Platform. You can do that using Mapping Data flows . Source data flow will take your Json data and then use transformations according to your requirement and finally load data back to sink in json format. Hope this will help. Please let us know if any further queries. ------------------- Please consider hitting Accept Answer . Accepted answers helps community as well.

ADF - Adding element to JSON object

Shezad Chishti 1

Hi Guys,

Is there a simple way to append to a JSON object within Azure Data Factory?

We retrieve the JSON object from Cosmos. The type is shown as 'complex'
We then create calculated columns.
We then want to add those columns within the JSON object.

I know we could create an azure function but was wondering if there was anything we could do in ADF.

Your advice will be appreciated.

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2022-01-12T11:05:24.25+00:00

Hi @Shezad Chishti ,

Please consider Accepting answer if it helps. Please let us know if any further queries. Thank you.

1 answer

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2021-12-15T16:32:48.323+00:00

Hi @Shezad Chishti ,

Thank you for posting query in Microsoft Q&A Platform.

You can do that using Mapping Data flows.

Source data flow will take your Json data and then use transformations according to your requirement and finally load data back to sink in json format.

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

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

Please consider hitting Accept Answer. Accepted answers helps community as well.
Please sign in to rate this answer.
Shezad Chishti 1 Reputation point

2021-12-15T16:55:51.227+00:00

@ShaikMaheer-MSFT - That's where we are struggling at the moment. How do we do it in the data flows?
I have just found out that it can be done by adding additional mappings in the Copy Activity. However we want to be able to do it within the Data flow.
Can you show me an example of how to do it within a data flow?

Shezad Chishti 1 Reputation point

2021-12-15T17:02:04.01+00:00

Just to give you insight lets say the below is the requirement.

We just dont know how to add the calculated columns to the existing JSON.

Initial json :

{
"car": {
"carName": "bmw"
}
}

New json :

{
"car": {
"carName": "bmw",
"carMileage": 123, <-- these are calculated columns
"carAge": 10, <-- these are calculated columns
}
}

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2021-12-23T16:40:29.863+00:00

Hi @Shezad Chishti - Below are steps to implement same in high level.

Sorry for delay response.

Step1: Source transformation. use dataset which is pointing to your source json data.
Step2: Derived Column transformation to create two new calculated columns(carMileage & carAge).
Step3: Sink transformation. Use dataset which is json type and load data in to destination.

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

----------

Please consider hitting Accept Answer. Accepted answers helps community as well.

Shezad Chishti 1 Reputation point

2021-12-31T14:19:27.627+00:00

@ShaikMaheer-MSFT - Could you please go more into the low level as we were not able to add the derived column within the parent "car" using data flows. Thanks

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2022-01-03T15:48:40.587+00:00

Hi @Shezad Chishti - In your case your Car column is complex type as it contains sub fields(carName, carMileage & carAge) as well. So, in your derived column transformation you can compute that complex type json. Kindly check below video, where similar kindly of complex type build was discussed.
https://www.youtube.com/watch?v=-FRZXgSkvSY&list=PLMWaZteqtEaLacN3eS3s8pw2jtwBVb1BH&index=33

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2022-01-07T17:11:05.467+00:00

Hi @Shezad Chishti ,

Please consider Accepting answer if it helps. Please let us know if any further queries. Thank you.
Sign in to comment