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

Max Teitelbaum 0 Reputation points
2024-09-26T16:54:25.8933333+00:00

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:
User's image

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:
User's image

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.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,643 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Pinaki Ghatak 4,210 Reputation points Microsoft Employee
    2024-09-27T18:34:10.98+00:00

    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

  2. Max Teitelbaum 0 Reputation points
    2024-09-27T19:13:22.5166667+00:00

    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!

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.