skip to Main Content

I have a sample payload which I retrieve from a third party endpoint, using Logic Apps I want to return a subset of this data. This is what my third party payload looks like

{
    "SearchSummary": {
        "TotalRecordsFound": 48,
        "Offset": 0,
        "RecordsReturned": 48,
        "DatabaseInfo": {
            "ReleaseNumber": "43",
            "UpdateNumber": "431",
            "UpdateDate": "2024-08-20T00:00:00",
            "VersionNumber": "129.00",
            "IndexationDate": null
        },
        "Sort": null
    },
    "Data": [
        {
            "birthdate": "1950-06-01T00:00:00",
            "firstName": "Ian",
            "middleName": "Charlie",
            "lastName": "Jones",
            "dirId": "P003577888",
            "MEMBERSHIP_DATA": [
                {
                    "companyId": null,
                    "companyName": null,
                    "startDate": null,
                    "status": null,
                    "endDate": null,
                    "title": null,
                    "dept": null
                }
            ]
        },
        {
            "birthdate": "1919-05-11T00:00:00",
            "firstName": "Dorothy",
            "middleName": "June",
            "lastName": "Perkins",
            "dirId": "P003890700",
            "MEMBERSHIP_DATA": [
                {
                    "companyId": null,
                    "companyName": null,
                    "startDate": null,
                    "status": null,
                    "endDate": null,
                    "title": null,
                    "dept": null
                }
            ]
        },
        {
            "birthdate": "1947-11-09T00:00:00",
            "firstName": "Paul",
            "middleName": "Dean",
            "lastName": "Doe",
            "dirId": "P003890600",
            "MEMBERSHIP_DATA": [
                {
                    "companyId": "GB04969800",
                    "companyName": "Acme Ltd 1",
                    "startDate": "2020-04-15T00:00:00",
                    "status": "Previous",
                    "endDate": "2021-04-15T00:00:00",
                    "title": "Director",
                    "dept": "Board of Directors; Senior management"
                },
                {
                    "companyId": "GB07346800",
                    "companyName": "Acme Ltd 2",
                    "startDate": "2010-08-16T00:00:00",
                    "status": "Current",
                    "endDate": null,
                    "title": "Director",
                    "dept": "Board of Directors; Senior management"
                }
            ]
        }
    ]
}

In my Logic App, I want to query this and then return only the "Data" array. In addition to this, I want to filter our any MEMBERSHIP_DATA objects if MEMBERSHIP_DATA.Status = Previous.

So for example from my sample payload above Paul Doe’s first membership record should be removed in my returned response. This is what my Logic App definition looks like

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Create_CompanyContact_Array": {
                "inputs": {
                    "variables": [
                        {
                            "name": "CompanyContacts",
                            "type": "array",
                            "value": "@body('Parse_JSON')?['Data']"
                        }
                    ]
                },
                "runAfter": {
                    "Parse_JSON": [
                        "SUCCEEDED"
                    ]
                },
                "type": "InitializeVariable"
            },
            "HTTP": {
                "inputs": {
                    "headers": {
                        "ApiToken": "XXX"
                    },
                    "method": "GET",
                    "uri": "https://companytest.free.beeceptor.com/contact"
                },
                "runAfter": {},
                "type": "Http"
            },
            "Loop_Contacts": {
                "actions": {
                    "Loop_Membership": {
                        "actions": {
                            "Filter_array": {
                                "inputs": {
                                    "from": "@items('Loop_Contacts')?['MEMBERSHIP_DATA']",
                                    "where": "@equals(items('Loop_Membership')['Status'],'Current')"
                                },
                                "type": "Query"
                            }
                        },
                        "foreach": "@items('Loop_Contacts')?['MEMBERSHIP_DATA']",
                        "type": "foreach"
                    }
                },
                "foreach": "@variables('CompanyContacts')",
                "runAfter": {
                    "Create_CompanyContacts_Array": [
                        "SUCCEEDED"
                    ]
                },
                "type": "foreach"
            },
            "Parse_JSON": {
                "inputs": {
                    "content": "@body('HTTP')",
                    "schema": {
                        "properties": {
                            "Data": {
                                "items": {
                                    "properties": {
                                        "MEMBERSHIP_DATA": {
                                            "items": {
                                                "properties": {
                                                    "companyId": {
                                                        "type": [
                                                            "string",
                                                            "null"
                                                        ]
                                                    },
                                                    "companyName": {
                                                        "type": [
                                                            "string",
                                                            "null"
                                                        ]
                                                    },
                                                    "dept": {
                                                        "type": [
                                                            "string",
                                                            "null"
                                                        ]
                                                    },
                                                    "endDate": {
                                                        "type": [
                                                            "string",
                                                            "null"
                                                        ]
                                                    },
                                                    "startDate": {
                                                        "type": [
                                                            "string",
                                                            "null"
                                                        ]
                                                    },
                                                    "status": {
                                                        "type": [
                                                            "string",
                                                            "null"
                                                        ]
                                                    },
                                                    "title": {
                                                        "type": [
                                                            "string",
                                                            "null"
                                                        ]
                                                    }
                                                },
                                                "type": "object"
                                            },
                                            "type": "array"
                                        },
                                        "birthdate": {
                                            "type": [
                                                "string",
                                                "null"
                                            ]
                                        },
                                        "dirId": {
                                            "type": [
                                                "string",
                                                "null"
                                            ]
                                        },
                                        "firstName": {
                                            "type": [
                                                "string",
                                                "null"
                                            ]
                                        },
                                        "lastName": {
                                            "type": [
                                                "string",
                                                "null"
                                            ]
                                        },
                                        "middleName": {
                                            "type": [
                                                "string",
                                                "null"
                                            ]
                                        }
                                    },
                                    "type": "object"
                                },
                                "type": "array"
                            },
                            "SearchSummary": {
                                "properties": {
                                    "DatabaseInfo": {
                                        "properties": {
                                            "IndexationDate": {},
                                            "ReleaseNumber": {
                                                "type": "string"
                                            },
                                            "UpdateDate": {
                                                "type": "string"
                                            },
                                            "UpdateNumber": {
                                                "type": "string"
                                            },
                                            "VersionNumber": {
                                                "type": "string"
                                            }
                                        },
                                        "type": "object"
                                    },
                                    "Offset": {
                                        "type": "integer"
                                    },
                                    "RecordsReturned": {
                                        "type": "integer"
                                    },
                                    "Sort": {},
                                    "TotalRecordsFound": {
                                        "type": "integer"
                                    }
                                },
                                "type": "object"
                            }
                        },
                        "type": "object"
                    }
                },
                "runAfter": {
                    "HTTP": [
                        "SUCCEEDED"
                    ]
                },
                "type": "ParseJson"
            },
            "Response": {
                "inputs": {
                    "body": "@variables('CompanyContacts')",
                    "statusCode": "@outputs('HTTP')?['statusCode']"
                },
                "kind": "Http",
                "runAfter": {
                    "Loop_Contacts": [
                        "SUCCEEDED"
                    ]
                },
                "type": "Response"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "triggers": {
            "When_a_HTTP_request_is_received": {
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "kind": "Stateless"
}

The App returns the Data object, but it doesn’t filter as described above and returns all MEMBERSHIP_DATA rows. Can anyone explain what I’m doing wrong?

2

Answers


  1. In my Logic App, I want to query this and then return only the "Data" array. In addition to this, I want to filter our any MEMBERSHIP_DATA objects if MEMBERSHIP_DATA.Status = Previous.

    You can use below design for it:

    enter image description here

    In compose i have stored your data.

    Then:

    enter image description here

    Parse_Json Schema:

    {
        "type": "object",
        "properties": {
            "SearchSummary": {
                "type": "object",
                "properties": {
                    "TotalRecordsFound": {
                        "type": "integer"
                    },
                    "Offset": {
                        "type": "integer"
                    },
                    "RecordsReturned": {
                        "type": "integer"
                    },
                    "DatabaseInfo": {
                        "type": "object",
                        "properties": {
                            "ReleaseNumber": {
                                "type": "string"
                            },
                            "UpdateNumber": {
                                "type": "string"
                            },
                            "UpdateDate": {
                                "type": "string"
                            },
                            "VersionNumber": {
                                "type": "string"
                            },
                            "IndexationDate": {}
                        }
                    },
                    "Sort": {}
                }
            },
            "Data": {
                "type": "array",
                "items": {
                    "type": "object",
                    "properties": {
                        "birthdate": {
                            "type": "string"
                        },
                        "firstName": {
                            "type": "string"
                        },
                        "middleName": {
                            "type": "string"
                        },
                        "lastName": {
                            "type": "string"
                        },
                        "dirId": {
                            "type": "string"
                        },
                        "MEMBERSHIP_DATA": {
                            "type": "array",
                            "items": {
                                "type": "object",
                                "properties": {
                                    "companyId": {},
                                    "companyName": {},
                                    "startDate": {},
                                    "status": {},
                                    "endDate": {},
                                    "title": {},
                                    "dept": {}
                                },
                                "required": [
                                    "companyId",
                                    "companyName",
                                    "startDate",
                                    "status",
                                    "endDate",
                                    "title",
                                    "dept"
                                ]
                            }
                        }
                    },
                    "required": [
                        "birthdate",
                        "firstName",
                        "middleName",
                        "lastName",
                        "dirId",
                        "MEMBERSHIP_DATA"
                    ]
                }
            }
        }
    }
    

    then next actions:

    enter image description here

    Output:

    enter image description here

    enter image description here

    Login or Signup to reply.
  2. If you don’t want to deal with multiple For_each actions, the easiest way to filter the Data array leaving only MEMBERSHIP_DATA items with Status = "Previous" is to use the Execute JavaScript Code action like this:

    const data = workflowContext.actions.Parse_JSON.outputs.body.Data
    
    const filteredData = data.map(function(item) {
        var newItem = Object.assign({}, item);
        newItem.MEMBERSHIP_DATA = item.MEMBERSHIP_DATA.filter(item => item.status === "Previous");
        return newItem;
      });
    
    return filteredData
    

    Screenshot

    Result:

    [
      {
        "MEMBERSHIP_DATA": [],
        "birthdate": "1950-06-01T00:00:00",
        "dirId": "P003577888",
        "firstName": "Ian",
        "lastName": "Jones",
        "middleName": "Charlie"
      },
      {
        "MEMBERSHIP_DATA": [],
        "birthdate": "1919-05-11T00:00:00",
        "dirId": "P003890700",
        "firstName": "Dorothy",
        "lastName": "Perkins",
        "middleName": "June"
      },
      {
        "MEMBERSHIP_DATA": [
          {
            "companyId": "GB04969800",
            "companyName": "Acme Ltd 1",
            "dept": "Board of Directors; Senior management",
            "endDate": "2021-04-15T00:00:00",
            "startDate": "2020-04-15T00:00:00",
            "status": "Previous",
            "title": "Director"
          }
        ],
        "birthdate": "1947-11-09T00:00:00",
        "dirId": "P003890600",
        "firstName": "Paul",
        "lastName": "Doe",
        "middleName": "Dean"
      }
    ]
    

    If the result is not what you expect, you can adjust the JavaScript code accordingly.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search