nested arrays

Unnest nested arrays in Power Automate

We all know this problem, you have a nested array in Power Automate but how do we unnest these arrays? It is not easy!

Nested Arrays

Nested arrays can appear in many places. You can also call them arrays of arrays. Quite often you might be processing multiple batches of data.

In the past I have looked at avoiding single item nested arrays. Today I’m looking at the more complicated merging of nested arrays.

In my example I’m going to use the following array:

[
  [
    {
      "title": "rec1"
    },
    {
      "title": "rec2"
    },
    {
      "title": "rec3"
    }
  ],
  [
    {
      "title": "rec4"
    },
    {
      "title": "rec5"
    },
    {
      "title": "rec6"
    }
  ],
  [
    {
      "title": "rec7"
    },
    {
      "title": "rec8"
    },
    {
      "title": "rec9"
    }
  ]
]

In my example flow I’m going to set a compose action to the above array.

Each of the {“title”: “rec1”} sections I will refer to as a record and there are 3 batches of 3 records in my example.

Unnest nested arrays in Power Automate Microsoft Power Automate

Pieter’s method

Remember Pieter’s method? Well I’m going to make use of this.

Inside an Apply to each I’m creating a Compose. This compose uses the following expression

replace(string(items('Apply_to_each')),'"','''')

This is the expression that will replace every double quote with a single quote. This quote replacement is very important as you will find escaped double quotes that will appear as \” in the output in the flows. To avoid this I’m replacing the double quotes to single quotes.

Unnest nested arrays in Power Automate Microsoft Power Automate

Then the second Compose references the compose inside the apply to each as Described in my Pieter’s method post. But this time it does a little bit more.

json(replace(replace(string(outputs('Compose_3')),'[{','{'),'}]','}'))

The [ and the ] around the batches will now be removed and we end up with just the records of the nested array that we started off with.

We should probably have a look at the flow runs at the moment. The apply to each generates texts that look like a json array ( but they are not!). They are just text.

Unnest nested arrays in Power Automate Microsoft Power Automate

The compose after the apply to each will show you that we now have an array with 3 items. And each item is a text matching the texts that we saw inside the apply to each.

Unnest nested arrays in Power Automate Microsoft Power Automate

De-nest the nested array

So far so good! Picking up the process from the Compose 2 action …

Unnest nested arrays in Power Automate Microsoft Power Automate

We now have a text that represents an array with all 9 items. But the opening and closing brackets of the array are missing still

Unnest nested arrays in Power Automate Microsoft Power Automate

The above Join picks up each set of 3 texts and adds a comma in between.

In front of that all we need to add a [ and after the joint texts we add a ]. We can do this in the final compose. This is also where we turn the whole construction back into a json array.

json(concat('[',body('Join'),']'))
Unnest nested arrays in Power Automate Microsoft Power Automate

Remember that we replaced the double quotes with single quotes. The json function is actually happy to accept either format. Therefore there is no need to convert back to double quotes.

Looking at the output we now have a single array instead of a nested array.

Unnest nested arrays in Power Automate Microsoft Power Automate

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Archive
%d bloggers like this: