Handling Nested JSON Arrays from Business Central APIs using Rest in Azure Data Factory – Best Approach?

Within Azure Data Factory, the most native and maintainable pattern for handling Business Central OData responses (with @odata.* metadata and a nested value array) without Azure Functions is:

1. Use REST connector + pagination to land raw JSON “as-is” in file storage
   • Configure a REST dataset for the Business Central API.
   • Use the REST connector’s pagination rules so ADF issues multiple requests and writes all pages. The connector supports patterns where the next URL comes from headers or body, and can stop when an end condition is met.
   • To export the JSON response without caring about schema, use a Copy activity with no mapping defined. This enables the schema‑agnostic mode and writes the raw JSON response directly to Blob/ADLS.
   • This step preserves the original OData metadata and value array but decouples ingestion from transformation.
2. Store JSON as Type II arrayOfObjects where possible
   • When writing JSON via Copy activity, set filePattern to arrayOfObjects if the response is already an array of records or can be normalized to that pattern.
   • ADF’s JSON handling in Copy activity supports setOfObjects and arrayOfObjects. arrayOfObjects aligns with the value: [ ... ] structure and is the most natural pattern for downstream processing.
3. Use Mapping Data Flow with JSON source configured for arrays
   • In a Mapping Data Flow, use a JSON source pointing to the landed files.
   • Under JSON settings in the source options, choose the appropriate Document Form:
     • If each file is a JSON array (e.g., the value array persisted as the file root), use Array of documents.
     • If each file is a single JSON object containing value: [ ... ], use the default document form and then flatten the value array.
   • Mapping Data Flows support JSON as a source and can read from Blob, ADLS Gen1/Gen2, SFTP, and S3.
4. Flatten the value array in Mapping Data Flow
   • Use the Flatten transformation to denormalize the value array into rows.
   • Configure the Flatten transformation to:
     • Set the Unroll by property to the value array.
     • Project the fields from the unrolled items as columns.
   • This removes the nested array structure and produces a flat rowset suitable for loading into Azure SQL staging tables.
5. Handle OData metadata by ignoring it in schema
   • The OData metadata properties (@odata.context, @odata.nextLink, etc.) can simply be omitted from the projection in the data flow.
   • Since the Flatten transformation focuses on the value array, the metadata fields remain at the root and are not part of the flattened output unless explicitly selected.
6. Dynamic / metadata‑driven pattern considerations
   • For a metadata‑driven framework where multiple entities are ingested:
     • Use a generic REST dataset and parameterize the relative URL, query parameters, and pagination rules per entity.
     • Land each entity’s raw JSON into a parameterized folder/path in Blob/ADLS.
     • Use a single generic Mapping Data Flow where:
       • The source dataset path is parameterized (entity name, date, etc.).
       • The Flatten transformation always unrolls the value array.
       • Downstream schema mapping to Azure SQL can be driven by metadata (e.g., dynamic sink mapping or separate per‑entity mappings if needed).
   • While Mapping Data Flow’s Import projection is static, the JSON source can still operate generically if the structure of the value array is similar across entities, or if the framework is designed to handle a known set of schemas.
7. Avoiding BOM / encoding pitfalls when chaining Copy → Data Flow
   • If Copy activity is used to generate JSON files that are later consumed by a JSON source in data flows, be aware:
     • Copy activity writes Unicode JSON with BOM for UTF encodings.
     • Data Flow JSON source with Single document enabled does not support Unicode with BOM.
     • Issues like JSON parsing error, unsupported encoding or multiline can occur when using arrayOfObjects with BOM and Single document in Data Flow.
   • To avoid this:
     • Prefer arrayOfObjects and configure the JSON source’s Document Form accordingly instead of using Single document where BOM is present.
     • Or adjust encoding settings so the combination of Copy sink and Data Flow source is supported.

This pattern stays entirely within ADF: REST connector + Copy activity (schema‑agnostic) → JSON files (arrayOfObjects or object with value array) → Mapping Data Flow JSON source + Flatten transformation → Azure SQL staging. It removes the need for Azure Functions while handling both the value array and OData metadata in a dynamic, reusable way.

References:

• JSON format in Azure Data Factory and Azure Synapse Analytics
• Copy and transform data from and to a REST endpoint by using Azure Data Factory
• Flatten transformation in mapping data flow
• Troubleshoot connector and format issues in mapping data flows in Azure Data Factory