Merge 2 Json arrays in Logic App by using Azure Monitor Query

Published Apr 27 2021 03:10 AM 1,566 Views
Microsoft

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:

Drac_Zhang_0-1618542612461.png

 

 

After run the LA, we can get the merged array.

Drac_Zhang_1-1618542701377.png

 

Limitation

The values of type dynamic is limited to 1MB, so this workaround not support for handling large size data.

 

1 Comment
%3CLINGO-SUB%20id%3D%22lingo-sub-2275487%22%20slang%3D%22en-US%22%3EMerge%202%20Json%20arrays%20in%20Logic%20App%20by%20using%20Azure%20Monitor%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2275487%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20Logic%20App%2C%20sometimes%20we%20need%20to%20merge%20two%20Json%20arrays%20into%20one%20based%20on%20reference%20ID.%3C%2FP%3E%0A%3CP%3EThe%20normal%20way%20is%20to%20iterate%20one%20array%20-%26gt%3B%20filter%20the%20items%20with%20same%20ID%20in%20another%20array%20-%26gt%3B%20insert%20the%20items.%3C%2FP%3E%0A%3CP%3EBut%20there's%20another%20achievement%20to%20do%20it%20is%20using%20%22Azure%20Monitor%20Logs%22%20connector%20and%20pass%20arrays%20as%20dynamic%20content%20in%20the%20query%20which%20will%20be%20less%20time%20consumption%20and%20higher%20performance.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EBackground%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EIn%20Azure%20Monitoring%20query%2C%20we%20can%20not%20only%20retrieve%20the%20data%20which%20stored%20in%20the%20database%2C%20but%20also%20pass%20dynamic%20content%20into%20it%20and%20convert%20it%20as%20data%20table.%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fscalar-data-types%2Fdynamic%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fscalar-data-types%2Fdynamic%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EScenario%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EI%20have%202%20Json%20arrays%20like%20following%3A%3C%2FP%3E%0A%3CP%3EArray%201%3A%3C%2FP%3E%0A%3CP%3E%5B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22%40odata.type%22%3A%26nbsp%3B%22%23microsoft.graph.user%22%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22id%22%3A%26nbsp%3B%22e9b26715-b94e-xxxxxxxxxxxxxxxxx%22%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22userPrincipalName%22%3A%26nbsp%3B%3CA%20href%3D%22mailto%3Auser2%40jiecao.onmicrosoft.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3E%22user2%40xxx.onmicrosoft.com%22%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%E2%80%A6%E2%80%A6%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22%40odata.type%22%3A%26nbsp%3B%22%23microsoft.graph.user%22%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22id%22%3A%26nbsp%3B%228e98ee70-d769-xxxxxxxxxxxxxxxxx%22%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22userPrincipalName%22%3A%20%22%3CA%20href%3D%22mailto%3Auser3%40jiecao.onmicrosoft.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Euser3%40xxx.onmicrosoft.com%22%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7D%3C%2FP%3E%0A%3CP%3E%5D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EArray2%3A%3C%2FP%3E%0A%3CP%3E%5B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22%40odata.type%22%3A%26nbsp%3B%22%23microsoft.graph.user%22%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22id%22%3A%26nbsp%3B%22e9b26715-b94e-xxxxxxxxxxxxxxxxx%22%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22displayName%22%3A%26nbsp%3B%22user2%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7D%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%E2%80%A6%E2%80%A6%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22%40odata.type%22%3A%26nbsp%3B%22%23microsoft.graph.user%22%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22id%22%3A%26nbsp%3B%228e98ee70-d769-xxxxxxxxxxxxxxxxx%22%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22displayName%22%3A%26nbsp%3B%22user3%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7D%3C%2FP%3E%0A%3CP%3E%5D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20scenario%2C%20these%202%20arrays%20need%20to%20be%20merged%20into%20one%20array%20which%20has%20id%2C%20displayName%20and%20userPrincipalName%20field.%3C%2FP%3E%0A%3CP%3EWe%20only%20need%20to%20create%20a%20%3CSTRONG%3EAzure%20Monitor%20Log%20%E2%80%93%20Run%20query%20and%20list%20result%3C%2FSTRONG%3E%20action%20like%20following%20and%20pass%20these%202%20json%20arrays%20as%20dynamic%20content%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Drac_Zhang_0-1618542612461.png%22%20style%3D%22width%3A%20625px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F273244i992D73578AEF02B9%2Fimage-dimensions%2F625x527%3Fv%3Dv2%22%20width%3D%22625%22%20height%3D%22527%22%20role%3D%22button%22%20title%3D%22Drac_Zhang_0-1618542612461.png%22%20alt%3D%22Drac_Zhang_0-1618542612461.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20run%20the%20LA%2C%20we%20can%20get%20the%20merged%20array.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Drac_Zhang_1-1618542701377.png%22%20style%3D%22width%3A%20625px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F273245i398132F381829493%2Fimage-dimensions%2F625x263%3Fv%3Dv2%22%20width%3D%22625%22%20height%3D%22263%22%20role%3D%22button%22%20title%3D%22Drac_Zhang_1-1618542701377.png%22%20alt%3D%22Drac_Zhang_1-1618542701377.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ELimitation%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThe%20values%20of%20type%20dynamic%20is%20limited%20to%201MB%2C%20so%20this%20workaround%20not%20support%20for%20handling%20large%20size%20data.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2275487%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELogic%20Apps%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2297938%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%202%20Json%20arrays%20in%20Logic%20App%20by%20using%20Azure%20Monitor%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2297938%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Drac%20for%20your%20sharing%2C%20very%20useful!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F990102%22%20target%3D%22_blank%22%3E%40Drac_Zhang%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Co-Authors
Version history
Last update:
‎Apr 27 2021 03:09 AM
Updated by: