ADF Data Flow json Flatten Unroll by

Question

I have a json result that should look like this:

{
  "TotalItemCount": 636,
  "PageIndex": 0,
  "PageSize": 20,
  "Result": [
    {
      "Id": "c02dd3ca-7440-4742-9802-af2b00655fe5",
      ...
      "Employment": [
        {
          "EmploymentNumber": "1234",
        ...
        }
      ]
    },
    {
      ...
    }
  ]
}

Swagger output:

However when it is setup with a REST Datatset source in ADF it seems like it adds a wrapping array like this:

[
{
  "TotalItemCount": 636,
  "PageIndex": 0,
  "PageSize": 20,
  "Result": [
    {
      "Id": "c02dd3ca-7440-4742-9802-af2b00655fe5",
      ...
      "Employment": [
        {
          "EmploymentNumber": "1234",
        ...
        }
      ]
    },
    {
      ...
    }
  ]
}
]

ADF Data flow source dataset output:

I want to flatten the items in the result in a data flow Flatten formatter but what do I put ion the Unroll by field when the anonymous array i added? Or do I need to perform something before the flatten to get all items in the Result array and the perform flatten on each of the items? The data preview in the Source looks like this:

The result would be an array with Id and EmploymentNumber

Update: Setting the unroll by to body does not work

Source projection:

Answer 1

• I have taken the data as shown below (The one you have given is not a valid).

{"body":[
{
  "TotalItemCount": 636,
  "PageIndex": 0,
  "PageSize": 20,
  "Result": [
    {
      "Id": "1",
      "Employment": [{
        "EmploymentNumber": "1234"
      }]
    },
    {
        "Id": "2",
      "Employment": [{
        "EmploymentNumber": "5678"
      }]
    }
  ]
}
]
}

• You can unroll by body.Result.Employment using unroll root as body.Result. Then select both the required id and employment number columns.

• This would give the result in the following way:

• Unroll on body with unroll root as body, you can select the required columns as well:

• Using this, the result would be: