Search code examples
azureazure-data-factory

Need to generate JSON file in Azure Data Factory with dynamic Key

I have a below data in csv file.

ID userId Name
719A070E-4874-E811-9CCE-02152146006A 123 Joe
5d7d0a74-f1b9-483a-8e9f-c7f45ccfeda0 456 Mike

Now, I need to generate below json using Azure Data Factory,

[
    {
        "719A070E-4874-E811-9CCE-02152146006A":{
                 "userId":"123",
                  "Name":"Joe"
             }    
    },
    {
        "5d7d0a74-f1b9-483a-8e9f-c7f45ccfeda0":{
                 "userId":"456",
                  "Name":"Mike"
             }    
    }
 ]

I have tried all the possible ways, I can able to form the inner json using userid and name. But I cannot able to figure out how to set the dynamic KEY in JSON which has to be derived from ID column in csv.

Solution

  • I am able to achieve your requirement using ForEach and append variable activities.

    I took csv same data like yours and given it to a lookup activity(uncheck the first row in lookup activity). Here in the csv dataset, uncheck the first row as header checkbox.

    This will be the lookup output array:

    enter image description here

    Then, in the variables create an array variable. Create a ForEach activity and give the following expression for it(check the Sequential checkbox here).

    @skip(activity('Lookup1').output.value, 1)

    Inside ForEach, create an append variable activity with following expression in it.

    @json(concat('{"',item().Prop_0,'":{"',activity('Lookup1').output.value[0].Prop_1,'":"',item().Prop_1,'","',activity('Lookup1').output.value[0].Prop_2,'":"',item().Prop_2,'"}}'))

    enter image description here

    Here, the above expression generates the object in each iteration and appends to the array variable(This is result array).

    For showing the output, I have used another array variable and assigned the result array value to it after Foreach.

    enter image description here

    My Pipeline JSON for your reference:

    {
    "name": "pipeline1",
    "properties": {
        "activities": [
            {
                "name": "Lookup1",
                "type": "Lookup",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "DelimitedTextSource",
                        "storeSettings": {
                            "type": "AzureBlobFSReadSettings",
                            "recursive": true,
                            "enablePartitionDiscovery": false
                        },
                        "formatSettings": {
                            "type": "DelimitedTextReadSettings"
                        }
                    },
                    "dataset": {
                        "referenceName": "sourcecsv",
                        "type": "DatasetReference"
                    },
                    "firstRowOnly": false
                }
            },
            {
                "name": "ForEach1",
                "type": "ForEach",
                "dependsOn": [
                    {
                        "activity": "Lookup1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "items": {
                        "value": "@skip(activity('Lookup1').output.value, 1)",
                        "type": "Expression"
                    },
                    "isSequential": true,
                    "activities": [
                        {
                            "name": "Append variable1",
                            "type": "AppendVariable",
                            "dependsOn": [],
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "arr",
                                "value": {
                                    "value": "@json(concat('{\"',item().Prop_0,'\":{\"',activity('Lookup1').output.value[0].Prop_1,'\":\"',item().Prop_1,'\",\"',activity('Lookup1').output.value[0].Prop_2,'\":\"',item().Prop_2,'\"}}'))",
                                    "type": "Expression"
                                }
                            }
                        }
                    ]
                }
            },
            {
                "name": "Set variable1",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "ForEach1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "variableName": "newarr",
                    "value": {
                        "value": "@variables('arr')",
                        "type": "Expression"
                    }
                }
            }
        ],
        "variables": {
            "arr": {
                "type": "Array"
            },
            "newarr": {
                "type": "Array"
            }
        },
        "annotations": []
    }
}