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

Question

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

YERNAIDU SIRAPARAPU 45

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:

Report Date	Breakdown	Contacts	Customers
2023-07-30	(no row)	(no row)	(no row)
2023-07-31	(no row)	(no row)	(no row)
2023-08-01	email	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)

J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-05-27T10:11:53.9666667+00:00

@YERNAIDU SIRAPARAPU,
Just checking in to see if the below answer provided by @Alex Burlachenko helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-05-27T10:11:53.9666667+00:00

@YERNAIDU SIRAPARAPU,
Just checking in to see if the below answer provided by @Alex Burlachenko helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Alex Burlachenko 9,780

Hey Yernaidu,

Thanks for posting your question on the Q&A portal! I see you're working with a nested time-series JSON and trying to flatten it in Azure Data Factory mapping data flow before loading it into Azure SQL. Let me walk you through how to do this step by step in a simple way.

First, in your data flow, you'll need to use the flatten transformation to handle the nested arrays. Since your JSON has dates as keys and arrays of objects as values, we'll need to first convert this structure into rows.

Source setup: In your source dataset, make sure you're reading the JSON file or API response correctly. Set the JSON file path or API endpoint, and ensure the format is set to JSON. You can check the microsoft documentation on JSON datasets in ADF here.

Flatten transformation: After the source, add a flatten transformation. Here, you'll want to unroll the nested arrays under each date. Since your dates are dynamic keys, you might need to use a derived column first to normalize the structure. For example, you can use an expression to convert the date keys into a column like reportDate and then unroll the arrays. The flatten transformation docs are here.

Handling empty arrays: For dates with empty arrays (like "2023-07-30": []), you can use a conditional split to either keep them as-is or filter them out. If you want to keep them (as shown in your goal), you might need to add a derived column to mark them before splitting.

Mapping fields: Once flattened, map the fields (breakdown, contacts, customers) to your Azure SQL table columns. If a date has no entries, you can use a select transformation to ensure those rows still appear with null values.

Sink to Azure SQL: Finally, configure your sink to write to Azure SQL. Make sure the table structure matches your output (reportDate, breakdown, contacts, customers). The ADF SQL sink docs are here.

A quick tip, if the JSON structure is tricky, you might want to use a data flow script or pre-processing with a web activity to reshape it before the data flow.

Hope this helps!

Best regards,
Alex
P.S. If my answer help to you, please Accept my answer
PPS That is my Answer and not a Comment
https://ctrlaltdel.blog/

YERNAIDU SIRAPARAPU 45 Reputation points

2025-05-26T09:44:23.2733333+00:00

Could you please share the Dataflow @Alex Burlachenko

Alex Burlachenko 9,780

Hi, yes for sure

you can use as a starting point for your pipeline. You can paste this into the Script view of your Mapping Data Flow in Azure Data Factory:

{
  "name": "FlattenTimeSeriesJson",
  "properties": {
    "type": "MappingDataFlow",
    "typeProperties": {
      "sources": [
        {
          "dataset": {
            "referenceName": "YourJsonDataset",
            "type": "DatasetReference"
          },
          "name": "JsonSource"
        }
      ],
      "transformations": [
        {
          "name": "ParseDynamicDates",
          "transformType": "DerivedColumn",
          "dataset": {
            "referenceName": "YourJsonDataset",
            "type": "DatasetReference"
          },
          "inputs": [
            {
              "referenceName": "JsonSource",
              "type": "DatasetReference"
            }
          ],
          "columns": [
            {
              "name": "dateEntries",
              "expression": "mapKeys(mapValues($, (k, v) -> {reportDate: k, items: v}))"
            }
          ]
        },
        {
          "name": "ExplodeArrays",
          "transformType": "Flatten",
          "dataset": {
            "referenceName": "YourJsonDataset",
            "type": "DatasetReference"
          },
          "inputs": [
            {
              "referenceName": "ParseDynamicDates",
              "type": "DatasetReference"
            }
          ],
          "unrollBy": "dateEntries.items",
          "columns": [
            {
              "name": "reportDate",
              "expression": "dateEntries.reportDate"
            },
            {
              "name": "breakdown",
              "expression": "dateEntries.items.breakdown"
            },
            {
              "name": "contacts",
              "expression": "toInteger(dateEntries.items.contacts)"
            },
            {
              "name": "customers",
              "expression": "toInteger(dateEntries.items.customers)"
            }
          ]
        }
      ],
      "sinks": [
        {
          "dataset": {
            "referenceName": "YourAzureSqlTable",
            "type": "DatasetReference"
          },
          "name": "SqlSink"
        }
      ]
    }
  }
}

You'll need to replace:

"YourJsonDataset" with your actual JSON dataset name

"YourAzureSqlTable" with your SQL table dataset name

The flow works in 3 main steps:

First converts dynamic date keys into structured rows

Then flattens the nested arrays using the items field

Finally writes to SQL

For handling empty arrays, you might want to add a Conditional Split after the Flatten with this rule:

{
  "name": "HandleEmptyEntries",
  "transformType": "ConditionalSplit",
  "inputs": [
    {
      "referenceName": "ExplodeArrays",
      "type": "DatasetReference"
    }
  ],
  "rules": [
    {
      "name": "emptyArrays",
      "expression": "isNull(breakdown)",
      "outputs": [
        {
          "referenceName": "EmptyRows",
          "type": "DatasetReference"
        }
      ]
    }
  ]
}

The toInteger() calls ensure proper type conversion for your number fields.

You can find more about the data flow script format here: ADF Data Flow Script

Best regards,
Alex
P.S. If my answer help to you, please Accept my answer
PPS That is my Answer and not a Comment
https://ctrlaltdel.blog/

Alex Burlachenko 9,780 Reputation points

2025-05-27T17:53:54.1466667+00:00

Hi, does it help? @YERNAIDU SIRAPARAPU

Share via

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

1 answer

Your answer