How to reference Json fields in copy data (ADF)?

Question

I am currently integrating an API response into a SQL Server database using Azure Data Factory. The response from the API is structured in JSON format, as shown below:

The goal is to extract the id values from each batch and insert them into a SQL Server table. Each id should be inserted as a separate record. I am utilizing JSONPath within Azure Data Factory to map these values but am facing challenges in extracting the ids correctly despite the JSONPath expressions being validated correctly in other environments.

Attached below is a screenshot from the Data Factory interface showing the mapping configuration:

Attempted Solutions and Current Issues

I have attempted various JSONPath expressions to extract the ids, but the Data Factory does not seem to recognize or correctly apply these paths, leading to unsuccessful data extraction.

It's important to consider that the JSON response may contain a variable number of dynamic keys. Therefore, directly referencing specific keys like 01J8MXYPR40Y1G1RRX4V03PP66 and 01J8MV1NK2DRNZGQ30TMH8K9V0 in the mappings is not feasible, as it would rigidly fix the mappings to those keys. Given the potential variability in the number of keys and their corresponding IDs, a flexible mapping approach is necessary. This is the reason for using JSONPath, as it allows for the dynamic extraction of IDs regardless of the changes in key names or the number of entries, ensuring that the mapping adapts to different data structures.

Request for Assistance

Is there a workaround or a specific JSONPath expression that can be used within Azure Data Factory to effectively parse and map these id values from the JSON response into the SQL Server table, ensuring each id is treated as a unique record in the destination table? If a direct solution is not available, I would appreciate any guidance on alternative approaches or configurations that could resolve this issue.

Answer 1

@Max Teitelbaum

Thanks for reaching out to Microsoft Q&A

The issue lies in how the data is being processed and inserted into the SQL Server table. To ensure that each id is inserted as an individual record, you can use a Data Flow in Azure Data Factory. Here’s how you can do it:

Create a Data Flow:

• In your ADF pipeline, add a Data Flow activity.
• Create a new Data Flow and add a source transformation.

Source Transformation:

• Configure the source to read from your API response.
• Use the JSONPath expression $.batches[*].id to extract the id values.

Flatten Transformation:

• Add a Flatten transformation to convert the array of id values into individual rows.
• In the Flatten transformation, set the unroll by field to the id array.

Sink Transformation:

• Add a Sink transformation to write the data to your SQL Server table.
• Map the id field to the corresponding column in your SQL Server table.

This configuration will ensure that each id is treated as a separate record and inserted individually into your SQL Server table.

Hope this helps. Do let us know if you any further queries.

Answer 2

Hello @Max Teitelbaum

To extract the id values from each batch and insert them into a SQL Server table as separate records, you can use the following JSONPath expression in the mapping configuration of your copy activity:

$..id This expression will extract all id values from the JSON response, regardless of the number of dynamic keys or their corresponding IDs. In the mapping configuration, you can map the extracted id values to the corresponding column in your SQL Server table. Each id value will be inserted as a separate record in the destination table.

If you are still facing challenges in extracting the id values using JSONPath expressions, you can try using the "Preview Data" feature in the mapping configuration to validate your expressions and ensure that they are correctly extracting the desired values. Additionally, you can refer to the JSONPath syntax to ensure that your expressions are correctly formatted.

I hope this helps

Answer 3

I tried implementing the suggested JSONPath expression $..id in the mapping configuration of my copy activity. However, instead of inserting each id as a separate record in the SQL Server table, all the IDs are being combined and inserted into a single record as a comma-separated array of strings.

Could you please advise on how to adjust the configuration to ensure that each id is inserted as an individual record in the destination table?

Thank you for your assistance!