Multiple Collection Reference from API JSON with an API Source GET

Lucas GAURICHON 1

Hello,

I'm having difficulties to map an API JSON with Multiple Collection Reference.

Here under the information on my Source which is an API JSON from SAP ByDesign :

Do you know how can I succeed to map with it with two loops : one on "results" and the other on "salesorderitem" ?

Thank You for your help.

Best,

MartinJaffer-MSFT 26,081 Reputation points

2021-06-22T19:48:16.7+00:00
Hello @Lucas GAURICHON and welcome to Microsoft Q&A.

Unfortunately, Copy Activity only supports 1 collectionReference at a time.
There is also the question of, which SalesOrderItem.ObjectID should be applied to which result[].

I see 2 options for you:

chain two Copy Activities together, where the source for the second is the sink of the first. Each Copy Activity uses a different collectionReference. This way you can flatten both.

use Data Flows' Flatten Transformation
MartinJaffer-MSFT 26,081 Reputation points

2021-06-29T16:56:21.067+00:00

@Lucas GAURICHON I have not heard back from you. Did my response help, or do you need more information?
MartinJaffer-MSFT 26,081 Reputation points

2021-06-30T16:44:12.097+00:00

If you found your own solution, please share it here with the community.
Lucas GAURICHON 1 Reputation point

2021-07-06T18:30:17.687+00:00

Thank You Martin.

Sorry for my late answer, I was off for a few days.

Concerning the first option, when I'm trying to select the SalesOrderItem as Array, I'm getting all the SalesOrderItem but only for the first "results". Thus, I'm not able to get a table with all the SalesOrderItems from all results and chain the two tables together with a key from "results".

Concerning the second option, how can I use the Data Flows Flatten Transformation withouth having a first copy of the data in Azure ?

Thank you very much for your precious help :)

1 answer