Azure Data Factory -Copy Activity : REST API to Cosmos DB (nested array)

Abdul Kalam Mulla 46

Copying below REST API data into cosmos DB by using ADF copy activity(explicit mapping) but inplace of array[] it appearing as object {} in cosmos.

REST api response:
{
"page": 1,
"per_page": 2,
"total": 4,
"total_pages": 2,
"Candidate": [
{
"Contact": [
{
"ContactID": 1,
"ContactType": "aliqua laboris ",
"EffectiveDate": "2022-01-10T13:20:35.335Z",
"IsPrimary": false,
"isPrivate": true
}
],
"PartyID": 23,
"ID": "dolo",
"LastUpdatedDateTime": {
"DateTime": "2021-09-06T12:33:53.891Z"
},
"Name": [
{
"FirstName": "incididun",
"LastName": "cillum do",
"LongName": "cil",
"MiddleName": "veli",
"NameSuffix": "inc",
"NameType": "incididunt ",
"Title": "amet occaecat aute",
"isPreferred": true
}
]
]
}

Mapping in copy activity:

Cosmos output:

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-04-15T19:02:55.817+00:00

Hello @Abdul Kalam Mulla ,

Thanks for the question and using MS Q&A platform.

To better assist on your query, could you please attach a sample JSON source (please save it as .txt file to attach here). I see that you selected Prospect as Collection Reference but I don't see that Field/Key in you sample JSON provided.

And from the mapping image shared, I have also noticed that you have selected columns names based on the indexes (for eg: ['Contact'][0]['ContactID'] ) - which means you are not copying the whole array data but you are limiting the data copy to only 1st index, which is why the data copied to your sink is object and not the array objects.

If you would like copy the who data as-is then instead of explicit mapping you can try with auto-mapping (if the schema is same on both source and sink) which would avoid this issue.

Hope this helps. Do let us know how it goes.

Thank you
Abdul Kalam Mulla 46 Reputation points

2022-04-18T08:30:28.297+00:00

Thank you KranthiPakala-MSFT ·

Attached REST API response in txt file. With field level mapping it is considering only first object from the array , As per your suggestion mapped array level and object level its working as expected. Thank you.

REST API having page details so i need to remove that information while loading to cosmos so used explicit mapping .
Please let me know is there way we can remove the page details with implicit mapping and also have a requirement for upsert in cosmos db , not sure we can do this as well.

For Upsert i tried to duplicate ID column at source side with additional column option . but it returning only first ID value of array for all documents, when I am removing index the expression throwing Attached screenshots for reference.

193764-source-json.txt
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-05-17T18:02:35.793+00:00

Hello @Abdul Kalam Mulla ,

Since your source data is having complex json objects with nested arrays, it is better to use Mapping data flow and use flatten transformation to flatten the source data and transform the data as per your desired target requirement.

For more info, please refer to this documentation which has helpful demonstration video - Flatten transformation in mapping data flow

Hope this info helps.