Data factory expression builder with variables

Question

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"
                        ]
                    }
                }
            ]
        }
    }
}

Answer 1

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.

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])

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

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

Answer 2

After some rework, I've resolved as follow:

1. Converted JSon structure in string
2. Replaced the serial number with a static description
3. Reconverted the string in JSON using the JSON() function

This way I was able to navigate the JSON with a static strong structure.