Question posted in 
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
You can use below design for it:
In compose i have stored your data.
Then:
Parse_Json Schema:
then next actions:
Output:
If you don’t want to deal with multiple
For_eachactions, the easiest way to filter the Data array leaving only MEMBERSHIP_DATA items with Status = "Previous" is to use theExecute JavaScript Codeaction like this:Result:
If the result is not what you expect, you can adjust the JavaScript code accordingly.