dotLiquid template for Azure Logic Apps' SQL Execute Query output

Question

Azure Logic Apps' SQL Execute Query action returns JSON output like this:

[
  [
    {
      "ProductID": 7000,
      "ProductName": "Some name"
    },
    ...
  ]
]

What syntax would be correct to use with dotLiquid in the Azure Logic Apps JSON to JSON transform action to iterate over those nested arrays to get rid of the nesting, for example to generate something like this:

{
  "products": [
    {
      "productId": 7000,
      "productName": "Some name"
    },
    ...
  ]
}

score 0 · Answer 1 · answered Nov 02 '21 at 15:41

0

As you are going to use an Integration Account anyway, this transformation can be easily done using the Execute JavaScript Code action in your Logic App with the following code:

var input_array = workflowContext.actions.Compose.outputs;
var output = { "products": [] };

for (let i = 0; i < input_array.length; i++) {
  for (let j = 0; j < input_array[i].length; j++) {
    output.products.push(input_array[i][j]);
  }
}

return output;

Result:

answered Nov 02 '21 at 15:41

10p

5,488
22
30

Thanks! I see why you assume that I am using an integration account, but I am actually not in this instance. The SQL Server I am accessing is an Azure SQL Database with relatively broad firewall rules. – SvenAelterman Dec 17 '21 at 01:13
1

@SvenAelterman, how can you not use an integration account, if in your question you mentioned "JSON to JSON transform action", which works only with an integration account? https://learn.microsoft.com/en-us/azure/logic-apps/logic-apps-enterprise-integration-liquid-transform – 10p Dec 19 '21 at 11:14
1

Interesting Docs catch. I am using a Standard Logic App, which also allows using these transforms. However, Consumption plan Logic Apps do not have these features. – SvenAelterman Jan 03 '22 at 22:52

score 0 · Accepted Answer · answered Dec 17 '21 at 01:16

After continued trial-and-error, I arrived at this solution:

{
    "products": [
        {% for product in content[0] %}
        {
            "productId": {{ product.ProductID }}
        }{% unless forloop.last %},{% endunless %}
        {% endfor %}
    ]
}

dotLiquid template for Azure Logic Apps' SQL Execute Query output

2 Answers2