Question 103638 in Azure Question posted in
The official documentation to get you started can be found here.

Azure data factory appending to Json

Wanted to pick your brains on something
So, in Azure data factory, I am running a set of activities which at the end of the run produce a json segment

{"name":"myName", "email":"[email protected]", .. <more elements> }

This set of activities occurs in a loop – Loop Until activity.
My goal is to have a final JSON object like this:

 "profiles":[
{"name":"myName", "email":"[email protected]", .. <more elements> },
{"name":"myName", "email":"[email protected]", .. <more elements> },
{"name":"myName", "email":"[email protected]", .. <more elements> },
...
{"name":"myName", "email":"[email protected]", .. <more elements> }
 ]

That is a concatenation of all the individual ones.
To put in perspective, each individual item is a paged data from a rest api – and all them constitute the final response. I have no control over how many are there.

I understand how to concatenate individual items using 2 variables

jsonTemp = @concat(finalJson, individualResponse)
finalJson = jsonTemp

But, I do not know how to make it all under the single roof "profiles" afterwards.

Tags:

2

Answers


  1. 0

    So this is a bit of a hacky way of doing it and happy to hear a better solution.
    I’m assuming you have stored all your results in an array variable (let’s call this A).

    1. First step is to find the number of elements in this array. You can
      do this using the length(..) function.

    2. Then you go into a loop, interating a counter variable and
      concatenating each of the elements of the array making sure you add
      a ‘,’ in between each element. You have to make sure you do not add
      the ‘,’ after the last element(You will need to use an IF condition
      to check if your counter has reached the length of the array. At the
      end of this you should have 1 string variable like this.

      {"name":"myName","email":"[email protected]"},{"name":"myName","email":"[email protected]"},{"name":"myName","email":"[email protected]"},{"name":"myName","email":"[email protected]"}

    3. Now all you need to do this this expression when you are pushing the
      response anywhere.

      @json(concat(‘{"profiles":[‘,<your_string_variable_here>,’]}’))

    Login or Signup to reply.
  2. 0

    I agree with @Anupam Chand and I am following the same process with a different Second step.

    You mentioned that your object data comes from API pages and to end the until loop you have to give a condition to about the number of pages(number of iterations).

    This is my pipeline:

    enter image description here

    First I have initilized a counter and used that counter each web page URL and in until condition to meet a certain number of pages. As ADF do not support self referencing variables, I have used another temporary variable to increment the counter.

    To Store the objects of each iteration from web activity, I have created an array variable in pipeline.

    Inside ForEach, use append variable activity to append each object to the array like below.

    enter image description here

    For my sample web activity the dynamic content will be @activity('Data from REST').output.data[0]. for you it will be like @activity('Web1').output. change it as per your requirement.

    This is my Pipeline JSON:

    {
"name": "pipeline3",
"properties": {
    "activities": [
        {
            "name": "Until1",
            "type": "Until",
            "dependsOn": [
                {
                    "activity": "Counter intialization",
                    "dependencyConditions": [
                        "Succeeded"
                    ]
                }
            ],
            "userProperties": [],
            "typeProperties": {
                "expression": {
                    "value": "@equals('3', variables('counter'))",
                    "type": "Expression"
                },
                "activities": [
                    {
                        "name": "Data from REST",
                        "type": "WebActivity",
                        "dependsOn": [
                            {
                                "activity": "counter in temp variable",
                                "dependencyConditions": [
                                    "Succeeded"
                                ]
                            }
                        ],
                        "policy": {
                            "timeout": "0.12:00:00",
                            "retry": 0,
                            "retryIntervalInSeconds": 30,
                            "secureOutput": false,
                            "secureInput": false
                        },
                        "userProperties": [],
                        "typeProperties": {
                            "url": {
                                "value": "https://reqres.in/api/users?page=@{variables('counter')}",
                                "type": "Expression"
                            },
                            "method": "GET"
                        }
                    },
                    {
                        "name": "counter in temp variable",
                        "type": "SetVariable",
                        "dependsOn": [],
                        "userProperties": [],
                        "typeProperties": {
                            "variableName": "tempCounter",
                            "value": {
                                "value": "@variables('counter')",
                                "type": "Expression"
                            }
                        }
                    },
                    {
                        "name": "Counter increment using temp",
                        "type": "SetVariable",
                        "dependsOn": [
                            {
                                "activity": "Data from REST",
                                "dependencyConditions": [
                                    "Succeeded"
                                ]
                            }
                        ],
                        "userProperties": [],
                        "typeProperties": {
                            "variableName": "counter",
                            "value": {
                                "value": "@string(add(int(variables('tempCounter')),1))",
                                "type": "Expression"
                            }
                        }
                    },
                    {
                        "name": "Append web output to array",
                        "type": "AppendVariable",
                        "dependsOn": [
                            {
                                "activity": "Counter increment using temp",
                                "dependencyConditions": [
                                    "Succeeded"
                                ]
                            }
                        ],
                        "userProperties": [],
                        "typeProperties": {
                            "variableName": "arr",
                            "value": {
                                "value": "@activity('Data from REST').output.data[0]",
                                "type": "Expression"
                            }
                        }
                    }
                ],
                "timeout": "0.12:00:00"
            }
        },
        {
            "name": "Counter intialization",
            "type": "SetVariable",
            "dependsOn": [],
            "userProperties": [],
            "typeProperties": {
                "variableName": "counter",
                "value": {
                    "value": "@string('1')",
                    "type": "Expression"
                }
            }
        },
        {
            "name": "To show res array",
            "type": "SetVariable",
            "dependsOn": [
                {
                    "activity": "Until1",
                    "dependencyConditions": [
                        "Succeeded"
                    ]
                }
            ],
            "userProperties": [],
            "typeProperties": {
                "variableName": "res_show",
                "value": {
                    "value": "@variables('arr')",
                    "type": "Expression"
                }
            }
        }
    ],
    "variables": {
        "arr": {
            "type": "Array"
        },
        "counter": {
            "type": "String"
        },
        "tempCounter": {
            "type": "String"
        },
        "res_show": {
            "type": "Array"
        },
        "arr_string": {
            "type": "String"
        }
    },
    "annotations": []
}
}

    Result in an array variable:

    enter image description here

    You can access this array by the variable name. If you want the output to be like yours, you can use the below dynamic content.

    @json(concat('{','"profile":',string(variables('res_show')),'}')))

    However, if you want to store this in a variable, you have to wrap it in @string() as currently, ADF variables only supports int, string and array type only.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search