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

Maarten Oosterkamp 1 Reputation point

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.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,444 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. svijay-MSFT 5,201 Reputation points Microsoft Employee

    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.