Azure - How can I transform the JSON in app logic?

Yugo
June 3, 2022
182 views
1 vote
2 Answers

I got to as far as a json result from log analytic query API HTTP action call:

{
  "tables": [
    {
      "name": "PrimaryResult",
      "columns": [
        {
          "name": "TimeGenerated",
          "type": "datetime"
        },
        {
          "name": "_queue",
          "type": "string"
        },
        {
          "name": "_messages",
          "type": "real"
        }
      ],
      "rows": [
        [
          "2022-06-03T03:20:00Z",
          "queue1",
          8073
        ],
        [
          "2022-06-03T03:20:00Z",
          "queue2",
          570
        ]
      ]
    }
  ]
}

I need transform it to this following format, which essentially an adaptivecards.io card

{
  "type": "ColumnSet",
  "columns": [
    {
      "type": "Column",
      "items": [
        {
          "type": "TextBlock",
          "text": "**TimeGenerated**" //Columns[0].Name
        },
        {
          "type": "TextBlock",
          "text": "2022-06-03T03:20:00Z" //Rows[0][0]
        },
        {
          "type": "TextBlock",
          "text": "2022-06-03T03:20:00Z" //Rows[1][0]
        }
      ]
    },
    {
      "type": "Column",
      "items": [
        {
          "type": "TextBlock",
          "text": "**_queue**" //Columns[1].Name
        },
        {
          "type": "TextBlock",
          "text": "queue1" //Rows[0][1]
        },
        {
          "type": "TextBlock",
          "text": "queue2" //Rows[1][1]
        }
      ]
    },
    {
      "type": "Column",
      "items": [
        {
          "type": "TextBlock",
          "text": "**_messages**"
        },
        {
          "type": "TextBlock",
          "text": "8073"
        },
        {
          "type": "TextBlock",
          "text": "570"
        }
      ]
    }
  ]
}

How can I do this? I tried with nested for-loop action, however I’m stuck in composing the final variable that can be inserted in the right place.

It would be ideal to hold a transformed variable ahead of time so that I can just include it in the later stage as POST to another webhook.

Edit: Whilst the column is static, the number rows are dynamic.

Tags: azure azure-logic-apps

Answers

Chosen as BEST ANSWER

My own solution. Essentially it's broken down into two stages: Initialize the column header and then append the rows at 2nd stage.

Pseudo code

var columns = [];

// initialize column header
for (i=0; i<input.tables[0].columns.length; i++) {

  columns.add({
    "type": "Column",
    "items": [{
      "type": "TextBlock",
      "text": "**input.tables[0].columns[i].name**",
    }]
  });
}

// populate row
for (j=0; j<input.tables[0].rows.length; j++) {
  for (i=0; i<input.tables[0].columns.length; i++) {
  
    var thisColumn = columns[i];
  
    thisColumn.items.add({
      "type": "TextBlock",
      "text": "input.tables[0].row[j][i]"
    });
  }
}

Full solution:

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Compose_Final": {
                "inputs": {
                    "columns": "@variables('columns')",
                    "type:": "ColumnSet"
                },
                "runAfter": {
                    "Until": [
                        "Succeeded"
                    ]
                },
                "type": "Compose"
            },
            "Initialize_Input": {
                "inputs": {
                    "variables": [
                        {
                            "name": "input",
                            "type": "object",
                            "value": {
                                "tables": [
                                    {
                                        "columns": [
                                            {
                                                "name": "_queue",
                                                "type": "string"
                                            },
                                            {
                                                "name": "_messages",
                                                "type": "real"
                                            }
                                        ],
                                        "name": "PrimaryResult",
                                        "rows": [
                                            [
                                                "queue1",
                                                8073
                                            ],
                                            [
                                                "queue2",
                                                570
                                            ],
                                            [
                                                "queue3",
                                                666
                                            ]
                                        ]
                                    }
                                ]
                            }
                        }
                    ]
                },
                "runAfter": {},
                "type": "InitializeVariable"
            },
            "Initialize_variable_columnIdx": {
                "inputs": {
                    "variables": [
                        {
                            "name": "columnIdx",
                            "type": "integer",
                            "value": 0
                        }
                    ]
                },
                "runAfter": {
                    "Initialize_variable_columns": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_variable_columns": {
                "inputs": {
                    "variables": [
                        {
                            "name": "columns",
                            "type": "array",
                            "value": []
                        }
                    ]
                },
                "runAfter": {
                    "Initialize_Input": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Select_Column_Header": {
                "inputs": {
                    "from": "@variables('input')?['tables'][0]['columns']",
                    "select": {
                        "items": [
                            {
                                "text": "**@{item()['name']}**",
                                "type": "TextBlock"
                            }
                        ],
                        "type": "Column"
                    }
                },
                "runAfter": {
                    "Initialize_variable_columnIdx": [
                        "Succeeded"
                    ]
                },
                "type": "Select"
            },
            "Until": {
                "actions": {
                    "Append_to_array_variable": {
                        "inputs": {
                            "name": "columns",
                            "value": {
                                "items": "@outputs('Compose')",
                                "type": "Column"
                            }
                        },
                        "runAfter": {
                            "Compose": [
                                "Succeeded"
                            ]
                        },
                        "type": "AppendToArrayVariable"
                    },
                    "Compose": {
                        "inputs": "@union(body('Select_Column_Header')[variables('columnIdx')]['items'], body('Select'))",
                        "runAfter": {
                            "Select": [
                                "Succeeded"
                            ]
                        },
                        "type": "Compose"
                    },
                    "Increment_variable": {
                        "inputs": {
                            "name": "columnIdx",
                            "value": 1
                        },
                        "runAfter": {
                            "Append_to_array_variable": [
                                "Succeeded"
                            ]
                        },
                        "type": "IncrementVariable"
                    },
                    "Select": {
                        "inputs": {
                            "from": "@variables('input')?['tables'][0]['rows']",
                            "select": {
                                "text": "@item()[variables('columnIdx')]",
                                "type": "TextBlock"
                            }
                        },
                        "runAfter": {},
                        "type": "Select"
                    }
                },
                "expression": "@greaterOrEquals(variables('columnIdx'), length(variables('input')?['tables'][0]['columns']))",
                "limit": {
                    "count": 60,
                    "timeout": "PT1H"
                },
                "runAfter": {
                    "Select_Column_Header": [
                        "Succeeded"
                    ]
                },
                "type": "Until"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {},
        "triggers": {
            "Recurrence": {
                "evaluatedRecurrence": {
                    "frequency": "Month",
                    "interval": 12
                },
                "recurrence": {
                    "frequency": "Month",
                    "interval": 12
                },
                "type": "Recurrence"
            }
        }
    },
    "parameters": {}
}

(Edit)

Create a LogicApp and load in this definition to see how to do it …

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Initialize_AdaptiveCard_JSON": {
                "inputs": {
                    "variables": [
                        {
                            "name": "XML",
                            "type": "string",
                            "value": "{n  "type": "ColumnSet",n  "columns": [n    {n      "type": "Column",n      "items": [n        {n          "type": "TextBlock",n          "text": "**@{variables('Object Variable')['tables'][0]['columns'][0]['Name']}**"n        },n        {n          "type": "TextBlock",n          "text": "@{variables('Object Variable')['tables'][0]['rows'][0][0]}"n        },n        {n          "type": "TextBlock",n          "text": "@{variables('Object Variable')['tables'][0]['rows'][1][0]}"n        }n      ]n    },n    {n      "type": "Column",n      "items": [n        {n          "type": "TextBlock",n          "text": "**@{variables('Object Variable')['tables'][0]['columns'][1]['Name']}**"n        },n        {n          "type": "TextBlock",n          "text": "@{variables('Object Variable')['tables'][0]['rows'][0][1]}"n        },n        {n          "type": "TextBlock",n          "text": "@{variables('Object Variable')['tables'][0]['rows'][1][1]}"n        }n      ]n    },n    {n      "type": "Column",n      "items": [n        {n          "type": "TextBlock",n          "text": "**@{variables('Object Variable')['tables'][0]['columns'][2]['Name']}**"n        },n        {n          "type": "TextBlock",n          "text": "@{variables('Object Variable')['tables'][0]['rows'][0][2]}"n        },n        {n          "type": "TextBlock",n          "text": "@{variables('Object Variable')['tables'][0]['rows'][1][2]}"n        }n      ]n    }n  ]n}"
                        }
                    ]
                },
                "runAfter": {
                    "Initialize_Object": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_Object": {
                "inputs": {
                    "variables": [
                        {
                            "name": "Object Variable",
                            "type": "object",
                            "value": {
                                "tables": [
                                    {
                                        "columns": [
                                            {
                                                "name": "TimeGenerated",
                                                "type": "datetime"
                                            },
                                            {
                                                "name": "_queue",
                                                "type": "string"
                                            },
                                            {
                                                "name": "_messages",
                                                "type": "real"
                                            }
                                        ],
                                        "name": "PrimaryResult",
                                        "rows": [
                                            [
                                                "2022-06-03T03:20:00Z",
                                                "queue1",
                                                8073
                                            ],
                                            [
                                                "2022-06-03T03:20:00Z",
                                                "queue2",
                                                570
                                            ]
                                        ]
                                    }
                                ]
                            }
                        }
                    ]
                },
                "runAfter": {},
                "type": "InitializeVariable"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {},
        "triggers": {
            "Recurrence": {
                "evaluatedRecurrence": {
                    "frequency": "Month",
                    "interval": 12
                },
                "recurrence": {
                    "frequency": "Month",
                    "interval": 12
                },
                "type": "Recurrence"
            }
        }
    },
    "parameters": {}
}

Basically, you just need to navigate to the specific section of the JSON to retrieve what you need for each individual value.

This is an example of what I’m talking about …

variables('Object Variable')['tables'][0]['columns'][1]['Name']

… that will get you to the name property for the second column in the first table.

Please signup or login to give your own answer.

Click here to cancel reply.

Azure – How can I transform the JSON in app logic?

Answers