Access REST API Nested JSON Objects in MS Fabric Data Pipelines

Organizations often deal with complex data structures, including nested JSON objects in the modern data processing and integration landscape. Microsoft Fabric provides a robust platform for orchestrating data workflows, including efficiently accessing and manipulating nested JSON objects. This guide will walk us through the steps to access nested JSON objects in Azure Data Factory pipelines.

Microsoft Fabric Pipelines are essential components within the data engineering platform, providing essential tools for building and orchestrating data workflows. These pipelines enable users to create end-to-end data integration and transformation processes, facilitating the movement of data between various sources and destinations, such as databases, file systems, and cloud services. MS Fabric pipelines offer a visual interface for designing workflows, allowing users to define activities, dependencies, and triggers to automate data movement and processing tasks.

• Copy Activity is a key building block within pipelines, designed to efficiently move data between different data stores. It supports a wide range of source and destination combinations, including cloud-based services like Lakehouse, Azure SQL Database, Amazon S3, and on-premises databases. Copy Activity offers features such as parallel execution, data encryption, and fault tolerance, ensuring reliable and scalable data movement operations. Users can configure mappings, transformations, and data movement settings to tailor the copy operation according to their specific requirements.
• Web Activity enables interaction with external web services, allowing users to invoke HTTP endpoints, consume REST APIs, or interact with web-based resources. This activity enables scenarios such as data extraction from web pages and invoking third-party APIs for data enrichment. With Web Activity, users can define HTTP requests, headers, query parameters, pagination and authentication mechanisms to interact with web services securely and efficiently.
• ForEach Activity is designed to enable iterative processing over data collections. This activity allows users to iterate through arrays or lists of items and execute a sequence of activities for each item in the collection. ForEach Activity is particularly useful for scenarios involving repetitive tasks, such as processing multiple files in a directory, iterating over database records, or performing parallel processing of data partitions. By dynamically iterating over datasets, ForEach Activity facilitates flexible and scalable data processing workflows within MS Fabric pipelines.

Access Nested JSON Objects in MS Fabric Data Pipelines

To access nested JSON data via the MS Fabric pipeline, begin by setting up a Web Activity to fetch the list of objects from the API endpoint. Once retrieved, configure a ForEach Activity to iterate over the data array obtained from the Web Activity output. Inside the ForEach Activity, add a Copy Activity to save JSON files directly into the lakehouse. To access individual JSON objects based on the “Id,” configure the Source settings of the Copy Activity with the appropriate connection details and use dynamic content in the Relative URL field. Finally, the destination where the JSON objects will be saved as JSON files will be defined, and the pipeline will be executed to initiate the data integration process.

Setting Up a WEB Activity

1. Begin by setting up a WEB Activity within your MS Fabric pipeline.
2. Ensure that the connection is established by adding it by going to Settings -> Connection -> + NEW.
3. Here, you have to add the base URL, authentication method and credentials.
4. Configure the Relative URL of the WEB Activity to call the API endpoint that provides the JSON data containing the nested objects, such as `/customers`.

Configuring a ForEach Activity

After obtaining the list, set up a ForEach Activity within your pipeline.

1. Add ForEach activity to the interface.
2. Navigate to Settings -> Items -> Add Dynamic Content.
3. Here, the aim is to access the list from the WEB activity that has IDs for the nested JSON objects.
4. Use the expression `@array(activity([NAMEOFWEBACTIVITY]).output.data)` to iterate over the array of objects.

Accessing Nested JSON Objects

1. Within the ForEach Activity, you’ll need to access the nested JSON objects.
2. Add a Copy Activity inside the ForEach Activity to process the JSON files.
3. Add the necessary connection details in the Source settings of the Copy Activity.
4. To access specific nested objects, such as `”Id”`, navigate to the Copy Activity settings.
5. Utilize dynamic content to construct the Relative URL, such as `@concat('list/',item().Id)`.
   This dynamic URL construction ensures that each iteration of the ForEach Activity accesses the relevant nested JSON object.

Setting Destination and Execution

1. Specify the destination where you want to save the processed JSON objects as JSON files.
2. Configure the destination settings accordingly. Add the file path if you want to save the objects as JSON files.
3. Now, in the file name, you can save the files with respect to the IDs of JSON objects. For example, click on add dynamic content and enter @concat(item().id'.json')
4. Once the pipeline setup is complete, execute the pipeline to begin accessing and processing the nested JSON objects.

Conclusion

Microsoft Fabric provides powerful capabilities for handling nested JSON objects within data pipelines. By following the outlined steps, one can efficiently extract, process, and store nested JSON data from REST APIs. Working with the data pipelines on MS Fabric limits one’s capability as a data engineer who is used to using programming language where everything is easy. Still, tools like Data Pipelines make data engineering accessible to domain experts who are not familiar with programming languages. I hope the guide above helped you, if you have any more questions you can write it directly on the Microsoft Fabric Community or drop a comment below.