Split a json string row or flatten transformation in data flow (ADF)

Using synapse data flow I want to transform the json format bellow into a tabular format with one column as date and the other as Total

{
"2023-12-11T00:00:00+01:00": 1272,
"2023-12-12T00:00:00+01:00": 1436,
"2023-12-13T00:00:00+01:00": 1173,
"2023-12-14T00:00:00+01:00": 1083,
"2023-12-15T00:00:00+01:00": 1231,
"2023-12-16T00:00:00+01:00": 854,
"2023-12-17T00:00:00+01:00": 805,
"2023-12-18T00:00:00+01:00": 1021,
"2023-12-19T00:00:00+01:00": 1112,
"2023-12-20T00:00:00+01:00": 1192,
"2023-12-21T00:00:00+01:00": 1071,
"2023-12-22T00:00:00+01:00": 1089,
"2023-12-23T00:00:00+01:00": 942,
"2023-12-24T00:00:00+01:00": 879,
"2023-12-25T00:00:00+01:00": 911,
"2023-12-26T00:00:00+01:00": 1167,
"2023-12-27T00:00:00+01:00": 1070,
"2023-12-28T00:00:00+01:00": 1172,
"2023-12-29T00:00:00+01:00": 953,
"2023-12-30T00:00:00+01:00": 780,
"2023-12-31T00:00:00+01:00": 749,
"2024-01-01T00:00:00+01:00": 703,
"2024-01-02T00:00:00+01:00": 1045
}

You can use Unpivot transformation to achieve your requirement.

First take a select transformation and use Rule based mapping with expression split($$,'T')[1] to remove the extra characters from the date column names.

Then, take a derived column transformation and create a new column key and give any string value to this. This will be used in the Unpivot transformation later.

Now, take an Unpivot transformation and give below configurations in that.

Ungroup by: key

Unpivot key :

Unpivot column name : date Unpivot column type : string

Unpivoted Columns:

You can drop the null values if you want by enabling the above setting.

This will give the result like below.

Now, use select another transformation and remove the extra column key.

This will the expected results.