nested arrays

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 Nested array in Power Automate 1

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 image 13

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 image 17

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 image 16

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 image 14

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 image 18

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 image 15

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 image 19

By Pieter Veenstra

Business Applications and Office Apps & Services Microsoft MVP working as a Microsoft Productivity Principal Consultant at HybrIT Services. You can contact me using contact@veenstra.me.uk.

11 thoughts on “Unnest nested arrays in Power Automate”
  1. What a life saver! Thank you so much!

    I’m quite new to Power Automate and your website has helped me a lot and already is the go-to place for me!

  2. I wanted to provide some feedback on this solution. I appreciate the article as it has saved me a bunch of time! I did find the presentation a bit confusing mostly due to your action naming. I was able to get it sorted but in my case, this code failed on a larger Json. After a lot of trail and error, I removed the step that converted double to single quotes and everything worked. This could simply be an anomaly in my SharePoint data but, I think this specific conversion step is likely for some very specific data and could be left out for many.

      1. The typical user in a large organization cannot use this Child Flows.

        They require either a license for HTTP (which costs more money) or the use of solutions. In most companies, getting funding or a new environment can be quite a chore. With regard to solutions, the infrastructure is a big mystery to most large company IT departments. So your approach, while inelegant, is useful.

        To clarify my other message, I found that I need that compose replace action in the flow for it to work properly but….I have to define it this way which makes no sense:

        replace(string(items(‘Apply_to_each’)),’”‘,’”‘)

        basically changing a double-quote to a double-quote. I do not understand enough about the need for changing quotes to understand what is actually happening.

      2. Child flows indeed cost premium licences. However when an organisation is picking up the platform in a serious way I have found so far that 1 premium licence isn’t a problem most of the time.

      3. That’s not necessarily true, and correct me if I am wrong….but any user who might trigger a flow with HTTP will also need a license unless the license is at the enterprise level. If the company is a huge consumer of SharePoint that can be quite problematic and expensive. Don’t get me wrong, I agree that Child Flows are the way to go, but Microsoft seems to think the opposite hence the premium price tag.

        BTW, I am looking for your insight into the replace code segment I mentioned above. Can you provide a sample of input it would me changing or why changing ” to ” makes it work and taking it out completely makes it fail.

      4. It should replace the double quotes with a single quote to fix the json from double quote format to single quote format. A single quote inside the quotes of the text needs to be escaped. This is done by repeating the single quote. Hence the ”” is an escaped single quote as a text.

      5. I understand what you are saying. My data is very simple and there are no single quotes. When I placed your replace in the conversion did not work. If I remove that same action the flow also does not work. But, when I use that action to replace a double-quote with a double-quote, everything works. Bizarre I know, but that is what is happening. Anyway, it must be me as others would have reported this if it was an issue. Thanks again.

  3. Hi Pieter!
    Just wanted to say that your solutions are extremely useful and I really appreciate your contribution to the Power community.
    One potential improvement to consider is actually escaping single quote characters, which are already there in the nested arrays, before replacing double-quotes with single-quotes.
    In my use case I kept getting errors as the JSON formatting was incorrect in the ‘Compose – Fixed Array’.
    I simply:
    – added an additional Compose action with the replace(string(items(‘Apply_to_each’)), ””, ‘\”’) expression before the Compose 3 action
    – and then referenced its output in the action replacing ” with ‘

    Hope this helps someone.
    Keep up the good work!

Leave a Reply

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