OData query $expand is not executed in ADF copy activity, how to get to the related entities?

Question

Whenever I try to add related OData entities to the query through the $expand function it is ignored and not returning the related entities. This makes the entire OData connector in ADF pretty useless....

To reproduce:

Use the reference API of OData.org
Use this endpoint: https://services.odata.org/TripPinRESTierService
Use this table: People
Use this query: $select=UserName&$expand=Trips($select=TripId)
Full call should result in GET https://services.odata.org/TripPinRESTierService/People?$select=UserName&$expand=Trips($select=TripId)

The expected result would be a JSON containing both UserName and TripID values, but instead only UserName is returned.

Answer 1

Hello @Maarten Oosterkamp ,

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

From my research - The output returns data in a complex format

Username : string
Trips : Array of TripID

Reference : https://learn.microsoft.com/en-us/azure/data-factory/connector-odata?tabs=data-factory

From this doc and from my testing ODATA connector does not support the complex types

Now I was able to achieve your requirement partially using the copy activity using a REST API Connector.

RESTAPI Connector :

Base URL : https://services.odata.org/TripPinRESTierService/(S(krde5urjcyiaops2qxkbtkgj))/People?$select=UserName,Trips&$expand=Trips($select=TripId)

You can parameterize the above the url as per your need.

Mapping :

Output :

This might not still meet your requirement of getting the TripId Values.

You will have to make use of the Mapping Data flow. To make the necessary transformation to get the TripID Values.