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

Maarten Oosterkamp 1 Reputation point
2021-11-23T17:13:38.607+00:00

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.
6,338 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. svijay-MSFT 5,111 Reputation points Microsoft Employee
    2021-11-24T12:31:46.21+00:00

    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

    152190-image.png

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

    RESTAPI Connector :

    152198-image.png

    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 :

    152226-image.png

    Output :

    152220-image.png

    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.