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"
    },
    ...
  ]
}

Answer 1

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

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

Answer 2

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: