How to split a JSON file into smaller files with values of keys when all keys are different using ADF?

Question

I have a JSON that looks like below.

{
    "Result": {
        "": {
            "Total": "1438",
            "ActiveUsers": 544,
            "TotalUsers": "56197",
            "TotalHistory": {
                "2024-08-11": 5740
            },
            "Department": "[No segment attribute]",
            "LastActivity": "11 Aug 2024 ",
            "PercentUsers": "1",
            "Show": 1
        },
        "Attr'Store #'216": {
            "Total": "14",
            "ActiveUsers": 3,
            "TotalUsers": "39",
            "TotalHistory": {
                "2024-08-11": 56
            },
            "Department": "7824",
            "DepartmentID": "Attr'Store #'216",
            "LastActivity": "11 Aug 2024 ",
            "PercentUsers": "8",
            "Show": 1
        },
        "Attr'Store #'784": {
            "Total": "6",
            "ActiveUsers": 1,
            "TotalUsers": "19",
            "TotalHistory": {
                "2024-08-11": 24
            },
            "Department": "1500",
            "DepartmentID": "Attr'Store #'784",
            "LastActivity": "11 Aug 2024 ",
            "PercentUsers": "5",
            "Show": 1
        },
        "Attr'Store #'896": {
            "Total": "3",
            "ActiveUsers": 1,
            "TotalUsers": "31",
            "TotalHistory": {
                "2024-08-11": 12
            },
            "Department": "1486",
            "DepartmentID": "Attr'Store #'896",
            "LastActivity": "11 Aug 2024 ",
            "PercentUsers": "3",
            "Show": 1
        }
    }
}

This JSON file is stored in blob storage. I want to split the JSON in to smaller files such that files only contain the Values of the Keys inside Result. 

First file will be 

{
            "Total": "1438",
            "ActiveUsers": 544,
            "TotalUsers": "56197",
            "TotalHistory": {
                "2024-08-11": 5740
            },
            "Department": "[No segment attribute]",
            "LastActivity": "11 Aug 2024 ",
            "PercentUsers": "1",
            "Show": 1
        }

Second will be 

{
            "Total": "14",
            "ActiveUsers": 3,
            "TotalUsers": "39",
            "TotalHistory": {
                "2024-08-11": 56
            },
            "Department": "7824",
            "DepartmentID": "Attr'Store #'216",
            "LastActivity": "11 Aug 2024 ",
            "PercentUsers": "8",
            "Show": 1
        }

So on and so forth. I wan to do this in ADF. Also all the Keys inside Result are different.

Answer

Hello @Singh Gunveen

To split a JSON file into smaller files with values of keys when all keys are different using ADF, you can use the following steps:

Create a pipeline in ADF with a Copy activity as the source.
In the Copy activity, set the source dataset to the JSON file in blob storage.
Set the source file format to JSON format.
In the Copy activity, set the sink dataset to a folder in blob storage where you want to store the smaller files.
Set the sink file format to JSON format.
In the Copy activity, set the mapping to extract the values of the keys inside Result.
Use the JSONPath expression to extract the values of the keys inside Result.
Use the forEach activity to iterate over the values of the keys inside Result.
In the forEach activity, use the Copy activity to write each value to a separate file in the sink folder. Here is an example of the JSONPath expression that you can use in the mapping to extract the values of the keys inside Result: $.Result.*.* This expression will extract all the values of the keys inside Result.

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.

Share via

How to split a JSON file into smaller files with values of keys when all keys are different using ADF?

1 answer

Your answer