Adding Keys to key value pairs in ADF JSON Processing

Question

I have Json file like below.

[
    {
        "id": "29"
        "name": "Glenn",
        "start": "2024-12-23",
        "end": "2025-01-03",
        "created": "2024-01-17",
        "dates": {
            "2024-12-23": "8",
            "2024-12-24": "8",
            "2024-12-25": "0",
            "2024-12-26": "8",
            "2024-12-27": "8",
            "2024-12-28": "0",
            "2024-12-29": "0",
            "2024-12-30": "8",
            "2024-12-31": "8",
            "2025-01-01": "0",
            "2025-01-02": "8",
            "2025-01-03": "8"
        },
        "notes": {
            "employee": "test1"
        }
    },
    {
        "id": "89",
        "name": "Jeff",
        "start": "2024-12-27",
        "end": "2025-01-12",
        "created": "2024-04-01",
        "dates": {
            "2024-12-27": "8",
            "2024-12-28": "0",
            "2024-12-29": "0",
            "2024-12-30": "8",
            "2024-12-31": "8",
            "2025-01-01": "0",
            "2025-01-02": "8",
            "2025-01-03": "8",
            "2025-01-04": "0",
            "2025-01-05": "0",
            "2025-01-06": "0",
            "2025-01-07": "0",
            "2025-01-08": "0",
            "2025-01-09": "0",
            "2025-01-10": "0",
            "2025-01-11": "0",
            "2025-01-12": "0"
        },
        "notes": {
            "employee": "test1"
        }
    }

I am using ADF to load it to structured table.

I am having problem in parsing Dates object

I just need columns id, name, Dates, value

But I am unable to de structure this object, Dates can vary so I can't use pivot or unpivot I tried using string transformation in ADf data flow but couldn't achieve what I want

I tried Converting the object into string and then to Array like below

["2024-12-23":8,"2024-12-24":8,"2024-12-25":0,"2024-12-26":8,"2024-12-27":8,"2024-12-28":0,"2024-12-29":0,"2024-12-30":8,"2024-12-31":8,"2025-01-01":0,"2025-01-02":8.0,"2025-01-03":8]

when I am trying to key: and value: to each element I am unsuccessful

Answer 1

To get you required output you need to use several transformations as below:

• First add your Source in the data flow activity.
• Then take Aggregate transformation to convert Json value to array and also get ither required columns. Set Group by for Id and name column. Aggregates as datesarray column and values as collect(@(dates=dates)) to convert dates json to array.
• Then take flatten transformation to flatten the array created in above transformation like below:
• Now take derived column to convert by default boolean type to integer (In Json wherever 0 is present Dataflow taking it as false(boolean value)) so replacing boolean value with 0 and shorts type to integer as below:
• Then use unpivot transformation to convert covert rows to column as below:
• Then filter out the rows with null values using filter transformation:

OUTPUT: