azure-logic-appsazure-logic-app-standard

How to select data from a collection?


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

Solution

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

    enter image description here

    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