json and array and list

Question

how to to get all rows inside the data in columns in adf..please sahre the screenshot it has array also inside using no manual column selection process

Answer 1

@Vineet S
Thank you for the question and using Microsoft Q&A platform.

To extract all rows from a complex column (which might contain JSON, arrays, or other structures) in Azure Data Factory without manually selecting each column you can follow the below steps:

Here I have used the sample JSON file as below:

[
  {
    "id": 1,
    "customer_id": "C001",
    "info": {
      "name": "Alice",
      "age": 28
    },
    "tags": ["developer", "azure"],
    "error": null
  },
  {
    "id": 2,
    "customer_id": "C002",
    "info": {
      "name": "Bob",
      "age": 35
    },
    "tags": ["admin", "cloud"],
    "error": null
  },
  {
    "id": 3,
    "customer_id": "C003",
    "info": {
      "name": "Charlie",
      "age": 40
    },
    "tags": ["engineer", "devops"],
    "error": "Data Incomplete"
  }
]

1. In ADF, create a Mapping Data Flow. Add a source transformation to bring in the dataset that contains the complex data column.

1. Add a Flatten transformation if the data column contains arrays or nested objects. In the Flatten transformation, select the array or object field that you want to expand into rows or columns. Flatten Arrays: If data is an array, this transformation will turn each item in the array into a new row. Flatten Objects: If data contains nested JSON objects, you can extract each key-value pair as separate columns.

For more help you can also refer to the below links:
https://stackoverflow.com/questions/72100860/adf-dataflow-and-columns-rows-in-separate-array-in-json
https://learn.microsoft.com/en-us/answers/questions/1053184/how-to-flatten-an-array-json-structure-in-adf

Hope this helps. Do let us know if you have any further queries.

---

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 2

Hi 659720 gdyhv