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