Forum Discussion
How to Flatten Nested Time-Series JSON from API into Azure SQL using ADF Mapping Data Flow?
How to Flatten Nested Time-Series JSON from API into Azure SQL using ADF Mapping Data Flow?
Hi Community,
I'm trying to extract and load data from API returning the following JSON format into an Azure SQL table using Azure Data Factory.
{ "2023-07-30": [], "2023-07-31": [], "2023-08-01": [ { "breakdown": "email", "contacts": 2, "customers": 2 } ], "2023-08-02": [], "2023-08-03": [ { "breakdown": "direct", "contacts": 5, "customers": 1 }, { "breakdown": "referral", "contacts": 3, "customers": 0 } ], "2023-08-04": [], "2023-09-01": [ { "breakdown": "direct", "contacts": 76, "customers": 40 } ], "2023-09-02": [], "2023-09-03": [] }
Goal: I want to flatten this nested structure and load it into Azure SQL like this:
Expand table
| ReportDate | Breakdown | Contacts | Customers |
|---|---|---|---|
| 2023-07-30 | (no row) | (no row) | (no row) |
| 2023-07-31 | (no row) | (no row) | (no row) |
| 2023-08-01 | 2 | 2 | |
| 2023-08-02 | (no row) | (no row) | (no row) |
| 2023-08-03 | direct | 5 | 1 |
| 2023-08-03 | referral | 3 | 0 |
| 2023-08-04 | (no row) | (no row) | (no row) |
| 2023-09-01 | direct | 76 | 40 |
| 2023-09-02 | (no row) | (no row) | (no row) |
| 2023-09-03 | (no row) | (no row) | (no row) |
1 Reply
To flatten your nested time-series JSON in Azure Data Factory, you can use Mapping Data Flow. Start by connecting your JSON as the source and treating it as a single object. Then, use a Flatten transformation to turn each date (which is a key) into a row. After that, add another Flatten to expand the array of data under each date. This way, for dates with breakdown data, you’ll get one row per item, and for dates with empty arrays, you'll still get a row with the date and nulls for the rest. Finally, use a Select transformation to rename the columns to match your target table (like ReportDate, Breakdown, Contacts, Customers), and load the result into your Azure SQL table. This approach keeps everything clean and gives you exactly the format you're aiming for.
