Split an array into a JSON with two arrays in a logic app

Question

I have this JSON:

[
  {
    "ParentReasonId": 2,
    "ParentReason": "Violent or repulsive content",
    "ReasonId": 15,
    "Reason": "Adults fighting"
  }, 
  {
    "ParentReasonId": 2,
    "ParentReason": "Violent or repulsive content",
    "ReasonId": 16,
    "Reason": "Physical attack"
  }
]

With azure logic apps i'm trying to transform the array into a json of two arrays:

{
    "categories": [
         {
        "categoryId": 2,
        "category": "Violent or repulsive content"
     }
        ],
    "reasons": [
     {
                "categoryId": 2,
        "reasonId": 15,
        "reason": "Adults fighting"
     },
     {
                "categoryId": 2,
        "reasonId": 16,
        "reason": "Physical attack"
     }
    ]
}

How can i achieve this using azure logic apps? The data is coming from a sql stored procedure action.

Can you please explain the mapping a bit more. For example, why categories have only 1 array item? — kgalic
@kgalic The ParentReasonId and ParentReason would be mapped to the categories array. There could be more array items but in the original array, both array items have the same ParentReasonId and ParentReason so they would get grouped together in the categories array. Then the ParentReasonId, ReasonId and Reason would be grouped to the reasons array. — joey0xx
Based on your explanation, in your second JSON, shouldn't category be Violent or repulsive content? — PramodValavala-MSFT

PramodValavala-MSFT PramodValavala-MSFT · Accepted Answer · 2019-05-03T01:38:06

After the data from your SQL Server you could do one of the following

Use Azure Functions
You could simply have an azure function which performs the transformation that you need and which will be called from your Logic Apps.

Refer the Custom Code with Azure Functions doc for more information on how to achieve this.

This is likely the easiest and more cost effective solution.
Use Integration Account & Liquid Templates
If you'd want to avoid having code to write and maintain, you could take this approach which involves writing a liquid template for your transformation, uploading it to an integration account and calling it from your Logic App.

Refer the Transform JSON doc for more information on how to achieve this.

Though this approach avoids maintaining code, note that Integration Accounts incur an hourly fee. If you have lots of such transformations, it would probably make sense to go with this.

Also, you could try to achieve the same with the built-in connectors & workflow definition language functions that Logic Apps provide but would probably be a bit too complex.

Split an array into a JSON with two arrays in a logic app

1 Answers