ADF access Json Array attribute when Square Brackets in Attribute name

Panos Pateas 26 Reputation points
2022-07-13T10:23:50.603+00:00

Hello,

I am getting the below response from a Web Activity:

220279-capture.png

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!!

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,136 questions
0 comments No comments
{count} votes

Accepted answer
  1. MartinJaffer-MSFT 25,996 Reputation points
    2022-07-14T20:14:38.113+00:00

    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 130616-image.png or upvote 130671-image.png 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

1 additional answer

Sort by: Most helpful
  1. MartinJaffer-MSFT 25,996 Reputation points
    2022-07-14T19:15:27.973+00:00

    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": []  
        }  
    }  
    
    0 comments No comments