Flatten JSON with drifting schema in Azure Data Factory

Question

Is there a way to flatten the following rather inconvenient type of JSON object in Azure Data Factory (V2)? Top-level keys in the data represent dynamically generated dates, which are different in each file. Hence, the schema is drifting.

I would like to flatten the data by unrolling the members array under each date key. Please see sample input and desired output below.

I haven't found a way to achieve this in a Mapping Data Flow. The Flatten activity does not seem to work since the schema cannot be defined, and I am not unrolling single array but many. My second attempt was to use Unpivot to transpose each date to a row and then flatten, but it seems that the complex type is not supported for unpivoted values.

Input sample

{
  "2021-01-01": {
    "total": 30,
    "members": [
      {
        "name": "foo",
        "value": 10
      },
      {
        "name": "bar",
        "value": 20
      }
    ]
  },
  "2021-01-02": {
    "total": 70,
    "members": [
      {
        "name": "foo",
        "value": 30
      },
      {
        "name": "john",
        "value": 40
      }
    ]
  }
}

Desired tabular output

	name	value
2021-01-01	foo	10
2021-01-01	bar	20
2021-01-02	foo	30
2021-01-02	john	40

Steve Zhao Steve Zhao · Accepted Answer · 2021-01-13T06:04:59

Maybe you can try this:

Flatten your JSON array members of 2021-01-01 and 2021-01-02 separately. Then use DerivedColumn transformation to add date as column. Finally, union your data in two stream.

But this way need you create multiple branches. If you have lots of date object data, it will be complex.

Flatten JSON with drifting schema in Azure Data Factory

1 Answers