In Logic App, sometimes we need to merge two Json arrays into one based on reference ID.
The normal way is to iterate one array -> filter the items with same ID in another array -> insert the items.
But there's another achievement to do it is using "Azure Monitor Logs" connector and pass arrays as dynamic content in the query which will be less time consumption and higher performance.
Background
In Azure Monitoring query, we can not only retrieve the data which stored in the database, but also pass dynamic content into it and convert it as data table.
https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/scalar-data-types/dynamic
Scenario
I have 2 Json arrays like following:
Array 1:
[
{
"@odata.type": "#microsoft.graph.user",
"id": "e9b26715-b94e-xxxxxxxxxxxxxxxxx",
"userPrincipalName": "user2@xxx.onmicrosoft.com"
}
……
{
"@odata.type": "#microsoft.graph.user",
"id": "8e98ee70-d769-xxxxxxxxxxxxxxxxx",
"userPrincipalName": "user3@xxx.onmicrosoft.com"
}
]
Array2:
[
{
"@odata.type": "#microsoft.graph.user",
"id": "e9b26715-b94e-xxxxxxxxxxxxxxxxx",
"displayName": "user2"
},
……
{
"@odata.type": "#microsoft.graph.user",
"id": "8e98ee70-d769-xxxxxxxxxxxxxxxxx",
"displayName": "user3"
}
]
In this scenario, these 2 arrays need to be merged into one array which has id, displayName and userPrincipalName field.
We only need to create a Azure Monitor Log – Run query and list result action like following and pass these 2 json arrays as dynamic content ( Please use mv-expand instead of mvexpand since mvexpand limit the results to 128 items )
After run the LA, we can get the merged array.
Limitation
The values of type dynamic is limited to 1MB, so this workaround not support for handling large size data.