ADF access Json Array attribute when Square Brackets in Attribute name

Question

Hello,

I am getting the below response from a Web Activity:

And I am trying to loop through a ForEach Activity in the array and do something per UserId. Hower I haven't found a way to access [UserId] attribute with item() function most likely due to the [] brackets in the name.

I have tried:

@item()['[UserId]']
or
@item().['[UserId]']

but does not allow me to pass this dynamic content. It looks like it works with the opening bracket @item()['[UserId'] but could not find a way so far to escape the cloisng bracket. Is there any way to do so?

Many thanks in advance!!

Answer 1

Okay @Panos Pateas , I found a workaround. I'll also inquire internally whether this is a bug.

The workaround was taking the "[UserId]" out of the expression, while still using it.

So what I did was, create a string type parameter named "key". I set the default value to [UserId].
Then I went to the Set Variable and used @item()[pipeline().parameters.key].

{  
    "name": "bracketInKeyWorkaround",  
    "properties": {  
        "activities": [  
            {  
                "name": "Lookup1",  
                "type": "Lookup",  
                "dependsOn": [],  
                "policy": {  
                    "timeout": "7.00:00:00",  
                    "retry": 0,  
                    "retryIntervalInSeconds": 30,  
                    "secureOutput": false,  
                    "secureInput": false  
                },  
                "userProperties": [],  
                "typeProperties": {  
                    "source": {  
                        "type": "JsonSource",  
                        "storeSettings": {  
                            "type": "AzureBlobStorageReadSettings",  
                            "recursive": true,  
                            "enablePartitionDiscovery": false  
                        },  
                        "formatSettings": {  
                            "type": "JsonReadSettings"  
                        }  
                    },  
                    "dataset": {  
                        "referenceName": "bracketinKey",  
                        "type": "DatasetReference"  
                    },  
                    "firstRowOnly": false  
                }  
            },  
            {  
                "name": "ForEach1",  
                "type": "ForEach",  
                "dependsOn": [  
                    {  
                        "activity": "Lookup1",  
                        "dependencyConditions": [  
                            "Succeeded"  
                        ]  
                    }  
                ],  
                "userProperties": [],  
                "typeProperties": {  
                    "items": {  
                        "value": "@activity('Lookup1').output.value[0].rows",  
                        "type": "Expression"  
                    },  
                    "activities": [  
                        {  
                            "name": "Set variable1",  
                            "type": "SetVariable",  
                            "dependsOn": [],  
                            "userProperties": [],  
                            "typeProperties": {  
                                "variableName": "val",  
                                "value": {  
                                    "value": "@item()[pipeline().parameters.key]",  
                                    "type": "Expression"  
                                }  
                            }  
                        }  
                    ]  
                }  
            }  
        ],  
        "parameters": {  
            "key": {  
                "type": "string",  
                "defaultValue": "[UserId]"  
            }  
        },  
        "variables": {  
            "val": {  
                "type": "String"  
            }  
        },  
        "annotations": []  
    }  
}  

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Hello @Panos Pateas and welcome to Microsoft Q&A.

As I understand, someoen gave you a JSON data, where the property names included brackets like "[foo]". You are having trouble writing an expression to acess the property values.

I immediately tried to repro myself and came up with below code. While mine worked, and yours did not, I must conclude there msut be something different. In mine I did not use the item() function. My next attempt will use item().

{  
    "name": "pipeline5",  
    "properties": {  
        "activities": [  
            {  
                "name": "Set variable1",  
                "type": "SetVariable",  
                "dependsOn": [],  
                "userProperties": [],  
                "typeProperties": {  
                    "variableName": "output",  
                    "value": {  
                        "value": "@pipeline().parameters.input['[foo]']",  
                        "type": "Expression"  
                    }  
                }  
            }  
        ],  
        "parameters": {  
            "input": {  
                "type": "object",  
                "defaultValue": {  
                    "[foo]": "bar"  
                }  
            }  
        },  
        "variables": {  
            "output": {  
                "type": "String"  
            }  
        },  
        "annotations": []  
    }  
}  

----------

Okay, so my next attempt used item() butstrangely the problem I run into is "pipeline failed to run", rather than any useful error message. Did you get any error messages?

Also, this wouldn't paste as comment...

{  
    "name": "bracketinjsonkey",  
    "properties": {  
        "activities": [  
            {  
                "name": "ForEach1",  
                "type": "ForEach",  
                "dependsOn": [],  
                "userProperties": [],  
                "typeProperties": {  
                    "items": {  
                        "value": "@pipeline().parameters.input",  
                        "type": "Expression"  
                    },  
                    "activities": [  
                        {  
                            "name": "Set variable1",  
                            "type": "SetVariable",  
                            "dependsOn": [],  
                            "userProperties": [],  
                            "typeProperties": {  
                                "variableName": "output",  
                                "value": {  
                                    "value": "@json(item()).['[foo]']",  
                                    "type": "Expression"  
                                }  
                            }  
                        }  
                    ]  
                }  
            }  
        ],  
        "parameters": {  
            "input": {  
                "type": "array",  
                "defaultValue": [  
                    {  
                        "[foo]": "bar"  
                    },  
                    {  
                        "[foo]": "boing"  
                    },  
                    {  
                        "[foo]": "hello"  
                    }  
                ]  
            }  
        },  
        "variables": {  
            "output": {  
                "type": "String"  
            }  
        },  
        "annotations": []  
    }  
}