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
Table of Contents
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.
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.
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.
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.
De-nest the nested array
So far so good! Picking up the process from the Compose 2 action …
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
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'),']'))
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.
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!
You are welcome!
Thanks. I am glad the site is working for you.
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.
Any unnesting of apply to each steps, I would nowadays do with child flows.
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.
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.
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.
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.
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.
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!