Copy Data activity fails when API returns top-level JSON array

[dworka]

Scenario:

I’m using the REST connector with a Copy data activity to pull data from a custom API and ingest it into a Lakehouse. The API response is valid JSON, but it returns the payload as a top-level array ([ ... ], i.e., an array of objects) rather than a top-level object ({ ... }).

It looks like Copy data expects the response to be a JSON object, and fails when the root element is an array.

Error:
ErrorCode=JsonInvalidDataFormat,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error occurred when deserializing source JSON file ''. Check if the data is in valid JSON object format.,Source=Microsoft.DataTransfer.ClientLibrary,'

Question:
Is there any way to configure the Copy data activity to handle a JSON array at the root and avoiding this error — without changing the API?

[v-achippa]

Hi @dworka,

Thank you for reaching out to Microsoft Fabric Community.

In Fabric the copy activity with the REST connector does not support a top level json arrays, this is a product limitation.
The recommended approach is to use a fabric notebook to call the API and write the result to the lakehouse, since the spark natively supports json arrays.
Alternatively, we can use Dataflow Gen2 because it can ingest array based json payloads.

Thanks and regards,

Anjan Kumar Chippa

[dworka]

Hi @v-achippa,

Thank you for the confirmation and prompt response.

I don’t think I can use a Fabric notebook because my API is behind a Data Gateway.

That leaves me with Dataflow Gen2, which we chose and which seems to work fine. However, we’re running into separate issues related to authorization for the mentioned API. From what I understand, these should be resolved in the future through planned features, so they’re not critical to this discussion.

In the meantime, support for top-level JSON arrays in Copy Activity would be the best solution for us. It also seems strange to me that Copy Activity doesn’t support this. Is there any chance this could be supported in the future?