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

Azure – Data factory expression builder with variables

I’m using a third party REST API, to which I pass a serial number of a device, and it returns a series of values.
This API returns a JSON with a node that is named after the serial, so I have to address it this way to retrieve the values

activity('Web1').output.root.SERIALXXXXX.values.variousArrays

How can I dynamically change the node name in the expression builder? Is there some "evaluate" function that permits to build up the node names?

If I don’t find any answer, I’ll encapsulate the third party rest api in an azure function made by me, to reconstruct the data in a more usable and stable structure, but I’d like to avoid this kind of work every time I’m stuck with third party jobs. I searched Microsoft Learn but I do not even know how to pose the question.

The JSon structure is this one

{
    "historicData": {
        "success": true,
        "params": {
            "value": [
                {
                    "SERIAL123": {
                        "Array1": [
                            0.0,
                           ...
                            0.0
                        ],
                        "Array2": [
                            0,
                           ...
                            0
                        ],
                        "timestamp": [
                            "2023-07-18T00:03:02Z",
                            ...
                            "2023-07-18T02:21:42Z"
                        ]
                    },
                    {
                    "SERIAL456": {
                        "Array1": [
                            0.0,
                           ...
                            0.0
                        ],
                        "Array2": [
                            0,
                            ...
                            0
                        ],
                        "timestamp": [
                            "2023-07-18T00:03:02Z",
                            ...
                            "2023-07-18T02:21:42Z"
                        ]
                    }
                }
            ]
        }
    }
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Sorry, I've found the solution by myself: the command uriComponent is the one that I needed for this problem. For example, to obtain the size of the Array1 I'm using the following code

    @string(length(uriComponent(concat('activity(''Web1'').output.root.params.value[0].',pipeline().parameters.serial,'.Array1'))))

    The first time I read about uriComponent command I thought that it was only meant to dynamically create web links, but it works even to navigate JSON and XML files.


  2. 0

    I tried your scenario with a sample JSON like below in a blob.

    {
    "root":{
        "SERIAL12345":{
            "values":{
                "variousArrays":[
                {
                    "id":"24",
                    "name":"Rakesh"
                },
                {
                    "id":"26",
                    "name":"Laddu"
                }
                ]
            }
        },
        "SERIAL12346":{
            "values":{
                "variousArrays":[
                {
                    "id":"1",
                    "name":"Virat"
                },
                {
                    "id":"2",
                    "name":"Kohli"
                }
                ]
            }
        },
        "SERIAL12347":{
            "values":{
                "variousArrays":[
                {
                    "id":"10",
                    "name":"MS"
                },
                {
                    "id":"16",
                    "name":"Dhoni"
                }
                ]
            }
        }
    }
}

    To get the JSON in ADF, I have used lookup activity on this.

    To access the array, first you need to get the list of SERIALXXXXX keys. To do that, first I have converted the above JSON into string and used split on ""SERIAL" with below expression and stored in an array variable using set variable activity.

    @split(string(activity('Lookup1').output.value[0].root),'"SERIAL')

    As you are using web activity, use the web activity expression @activity('Web1').output.root instead of lookup expression.

    This will give an array like below.

    enter image description here

    Now, give this array to a ForEach but skip first element(@skip(variables('split_with_serial'),1) ) as we don’t need that.

    Inside Foreach, use append variable activity to an array and use the following expression.

    @concat('SERIAL',split(item(),'":{')[0])

    enter image description here

    This will give the array of keys. Here, for showing output I have stored the above array in another array.

    enter image description here

    To retrieve the values inside your JSON, give this array to a ForEach and use the below expression inside of it.

    @activity('Web1').output.root[item()].values.variousArrays
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search