How to Flatten Nested Time-Series JSON from API into Azure SQL using ADF Mapping Data Flow?

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.