How to select data from a collection?

Question

For example, I have some kind of query in the DB, which I write to a variable:

   "Initialize_QueryCollection": {
        "type": "InitializeVariable",
        "inputs": {
            "variables": [
                {
                    "name": "QueryCollection",
                    "type": "Array",
                    "value": "@outputs('Execute_stored_procedure_(V2)')?['body']?['ResultSets']?['Table1']"
                }
            ]
        },
        "runAfter": {
            "Execute_stored_procedure_(V2)": [
                "SUCCEEDED"
            ]
        }
    }

then I need to select data from this variable according to certain parameters

            "Return_Filtered_Result": {
                "type": "Response",
                "inputs": {
                    "statusCode": 200,
                    "body": "@filter(variables('QueryCollection'), contains(triggerBody()?['FundKeyArray'], item()?['FundKey']))"
                }
            }

Code is saving withoput any error, but when try to run the workflow, receivning:

Receiving: "column '0': 'The template function 'filter' is not defined or not valid.'."

Please help me figure out the problem and how to resolve it. Thank you in advance. maybe I'm missing something and don't see it.

This is the trigger:

"triggers": {
            "Invoke_Funds": {
                "type": "Request",
                "kind": "Http",
                "inputs": {
                    "schema": {
                        "type": "object",
                        "properties": {
                            "FundKeyArray": {
                                "type": "array",
                                "items": {
                                    "type": "string"
                                }
                            }
                        },
                        "required": [
                            "FundKeyArray"
                        ]
                    }
                }
            }
        }

Output of QueryCollection:

[
  {
    "FundKey": 1,
    "FundName": "X 1", 
    "FundCategory": "a" 
  },
  {
    "FundKey": 2, 
    "FundName": "X 2", 
    "FundCategory": "b" 
  }
]

and need to select by FundKey from trigger payload:

{
    "FundKeyArray": ["1"]
}

Can you share the data which is there in variable QueryCollection and also specify which parameter you want to fetch. Please share the payload of triggerbody @slowpoke — Ikhtesam Afrin
– Ikhtesam Afrin, Commented Nov 25, 2024 at 10:03
@IkhtesamAfrin added to my question params needed to filter and output of queryCollection — slowpoke
– slowpoke, Commented Nov 25, 2024 at 14:48

Ikhtesam Afrin · Accepted Answer · 2024-11-25 16:20:00Z

Alternatively, you can achieve this using condition action in for each loop.

enter image description here

Code-

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Initialize_variable": {
                "type": "InitializeVariable",
                "inputs": {
                    "variables": [
                        {
                            "name": "QueryCollection",
                            "type": "array"
                        }
                    ]
                },
                "runAfter": {}
            },
            "Set_variable": {
                "type": "SetVariable",
                "inputs": {
                    "name": "QueryCollection",
                    "value": [
                        {
                            "FundKey": 1,
                            "FundName": "X 1",
                            "FundCategory": "a"
                        },
                        {
                            "FundKey": 2,
                            "FundName": "X 2",
                            "FundCategory": "b"
                        }
                    ]
                },
                "runAfter": {
                    "Initialize_variable": [
                        "SUCCEEDED"
                    ]
                }
            },
            "For_each": {
                "type": "Foreach",
                "foreach": "@variables('QueryCollection')",
                "actions": {
                    "Condition": {
                        "type": "If",
                        "expression": {
                            "and": [
                                {
                                    "contains": [
                                        "@triggerBody()?['FundKeyArray']",
                                        "@string(item()?['FundKey'])"
                                    ]
                                }
                            ]
                        },
                        "actions": {
                            "Compose": {
                                "type": "Compose",
                                "inputs": "@items('For_each')"
                            }
                        },
                        "else": {
                            "actions": {}
                        }
                    }
                },
                "runAfter": {
                    "Set_variable": [
                        "SUCCEEDED"
                    ]
                }
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "triggers": {
            "When_a_HTTP_request_is_received": {
                "type": "Request",
                "kind": "Http",
                "inputs": {
                    "schema": {
                        "type": "object",
                        "properties": {
                            "FundKeyArray": {
                                "type": "array",
                                "items": {
                                    "type": "string"
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    "kind": "Stateful"
}

By comparing the FundKeyArray with FundKey, I am getting expected response.

enter image description here

Skin · Accepted Answer · 2024-11-25 10:10:43Z

0

There is no such thing as a filter function. The problem is explained in the error.

If there was such a function, the it would be included in the list of functions related to collections ...

https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#collection-functions

You need to use a Filter array action prior to the response and pass the results of that action into any subsequent steps.

answered Nov 25, 2024 at 10:10

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to select data from a collection?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related