Filtering child array by attribute in Logic Apps
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?
Solution
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
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.
- Cannot add data to a ComplexField using Python SDK for Azure AI Search
- letsencrypt cert request failing for AKS ingress
- Playwright Test execution on Azure Windows machine fails in pipeline with error as No test found, It works perfectly with same command
- How to Combining PowerShell Output from multiple server to Single csv
- How can I find all Azure app registrations with API permissions to a specific app registration?
- Azure Deployment Groups vs Agent Pools
- I cannot extract .sql CREATE TABLEs for each table in my Azure SQL Database in my Azure Repo
- Can not read blobs through BlobContainerClient for blobs with empty folder prefixes
- Are task logs deleted when the node is deleted due to autoscaling in Azure Batch service?
- Azure Function Blob Storage Monitor
- I want to fetch Azure SQL table data from ADF to use as input in copy activity
- How to access code of my Azure website?
- Run JMeter script in AzureDevOps - Bearer Access Token generation
- Azure Function - HTTP trigger request length exceeded
- How and where to define an environment variable on Azure
- Stream Analytics doesn't output the data to SQL but does to a blob storage
- While Hierarchical namespace enabled cannot upload blob with metadata hdi_isfolder
- Cannot log in to Visual Studio Account in VS 2022
- SchemaColumnConvertNotSupportedException: column: [Col_Name], physicalType: INT64, logicalType: string
- create Azure Keyvault secret without exposing values
- Can you create 'User Flows' in Azure with a pay-as-you-go account
- Deploy ARM template at management group scope with Azure DevOps Pipeline
- How to make an azure function that deploys my bicep script from Azure Storage Account
- Basic SQL commands in Terraform
- Can we Deploy Web Application in Azure OpenAI of Production Level
- Get-MgGroupMember by display name instead of userID
- Azure DevOps REST API parent relation link to existing work item error
- What should be put to 'repoOwners' in Managed Identity Federated Credentials policy?
- How to get list of users from CSV file whose LastSignInDate column has a date that is before 90 days from current date or is empty?
- Langchain cannot connect with Azure SQL Database