How to flatten multidimensional array in Azure Data Factory/Synapse

Question

I want to extract "email" from JSON using ADF/Synapse.

Below is the JSON that I want to flatten and extract all "email" which is in an object in a multidimensional array:

{
  "input": {
    "title": [
      "ALIAS_VALUES",
      "ALIAS_COUNT"
    ],
    "values": [
      [
        {
          "code": "aaaa",
          "email": "[email protected]"
        },
        55
      ],
      [
        {
          "code": "bbbb",
          "email": "[email protected]"
        },
        10
      ],
      [
        {
          "code": "cccc",
          "email": "[email protected]"
        },
        18
      ]
    ],
    "IsNew": false
  },
  "errors": []
}

I'm trying to return a .csv output that only includes "email". I've tried using consecutive copy activities as well as a data flow using the flatten activity. I'm now trying to parse the JSON using a lookup activity.

Ravindra Babu · Accepted Answer · 2025-09-17 19:09:48Z

0

you basically have a nested array inside `input.values`, where each element is itself an array, and the first element of that sub-array is an object containing `"email"`.

In Azure Data Factory (ADF) or Synapse Data Flows, you can flatten this cleanly without multiple copy activities.

Here’s a step-by-step approach using a Mapping Data Flow:

---

Source

• Point your Source dataset to the JSON file (or API output).

• In Projection, make sure the schema is imported so you can see `input.values`.

---

First Flatten

• Add a Flatten transformation.

• Unroll by: `input.values`

This will give you each inner array as a row.

---

Second Flatten

• Add another Flatten transformation.

• Unroll by: `input_values[0]` (the first element of the inner array — the object with `email`).

• Now you can directly access `email` as a column:

`input_values[0].email`

---

Select Only Email

• Add a Select transformation.

• Keep only the `email` column.

Sink

• Set your Sink dataset to CSV.

• Map `email` → `email` in the output.

answered Sep 17 at 19:09

Ravindra Babu

1

Sign up to request clarification or add additional context in comments.

1 Comment

Community Sep 18 at 0:12

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

How to flatten multidimensional array in Azure Data Factory/Synapse

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related