web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate - General Discussion / Flattening an array in...
Power Automate - General Discussion
Answered

Flattening an array in an efficient way avoiding for each loops

(0) ShareShare
ReportReport
Posted on by CH-22072116-0

Dear Forum,

 

I'm struggling simplifying / flattening an array and I hope some experts can help me finding an efficient solution avoiding as much as possible for each loops seeing the amount of records. I know it's possible using xpath and xml but I did not succeed solving it myself  😞

Here's my array:

 

 

 

[
	{
	 "attribute1": "12625",
	 "attribute2": "2285",
	 "attribute3": "Alabama",
	 "attribute4": "USA",
	 "attribute5": "Forest",
	 "attribute6": "Exception",
	 "attribute7": "Restricted Area",
	 "attribute8": null,
	 "attribute9": null,
	 "attribute10": [
		{
		 "subattribute1": "ABC123",
		 "subattribute2": "NO",
		 "subattribute3": "",
		 "subattribute4": "",
		 "subattribute5": 0,
		 "subattribute6": "q_0",
		 "subattribute7": null,
		 "subattribute8": false
		},
		{
		 "subattribute1": "AZE985",
		 "subattribute2": "YES",
		 "subattribute3": "",
		 "subattribute4": "",
		 "subattribute5": 1,
		 "subattribute6": "q_1",
		 "subattribute7": {
			"subsubattribute1": 2084,
			"subsubattribute2": "resolved",
			"subsubattribute3": "",
			"subsubattribute4": true,
			"subsubattribute5": "HIGH",
			"subsubattribute6": "FAC"
		 },
		 "subattribute8": true
		}
	 ]
	},
	{
	 "attribute1": "18001",
	 "attribute2": "3768",
	 "attribute3": "Dubai",
	 "attribute4": "UAE",
	 "attribute5": "Level 1",
	 "attribute6": "Exception",
	 "attribute7": "Public",
	 "attribute8": null,
	 "attribute9": null,
	 "attribute10": [
		{
		 "subattribute1": "ADE865",
		 "subattribute2": "NO",
		 "subattribute3": "",
		 "subattribute4": "",
		 "subattribute5": 0,
		 "subattribute6": "q_0",
		 "subattribute7": {
			"subsubattribute1": 4706,
			"subsubattribute2": "open",
			"subsubattribute3": "",
			"subsubattribute4": false,
			"subsubattribute5": "SEVERE",
			"subsubattribute6": "FAB"
		 },
		 "subattribute8": true
		}
	 ]
	}
]

 

 

 

 

My expected final results needs to be flatten with information from the different levels

 

 

 

[
	{
		"attribute3": "Alabama",
		"attribute4": "USA",
		"subattribute1": "ABC123",
		"subattribute2": "NO",
		"subsubattribute2": null,
		"subsubattribute5": null
	},
	{
		"attribute3": "Alabama",
		"attribute4": "USA",
		"subattribute1": "AZE985",
		"subattribute2": "YES",
		"subsubattribute2": "resolved",
		"subsubattribute5": "HIGH"
	},
	{
		"attribute3": "Dubai",
		"attribute4": "UAE",
		"subattribute1": "ADE865",
		"subattribute2": "NO",
		"subsubattribute2": "open",
		"subsubattribute5": "SEVERE"
	}
]

 

 

 


I have been playing with :

join(xpath(xml(json(concat('{"body":{"value":', item()?['attribute10'] , '}}'))), '/body/value/attribute10/subattribute1/text()'), ', ')
 
But unfortunately no positive outcome.
 
Thanks you very much for. your precious help.
 
Chris



I have the same question (0)
  • SudeepGhatakNZ Profile Picture
    SudeepGhatakNZ 14,381 Most Valuable Professional on at
    Re: Flattening an array in an efficient way avoiding for each loops

    @ChrisBI 

    Would your array have multiple levels of nesting or would the structure remain the same?

     

  • CH-22072116-0 Profile Picture
    CH-22072116-0 on at
    Re: Flattening an array in an efficient way avoiding for each loops

    Hi @SudeepGhatakNZ 

    Level 1 (Attribute) keeps the structure and will always exist and be unique. Number of elements from 1 to x

    Level 2 (SubAttribute) keeps the structure and will always exist. Number of elements from 1 to x

    Level 3 (SubSubAttribute) keeps the structure but might not exist for some SubAttributes. Will then be null. If it exists then it will be unique. This level is not an array.

     

    I hope this helps. I assume you need this info for the JSON schema.

     

    Thanks in advance for your help !

  • SudeepGhatakNZ Profile Picture
    SudeepGhatakNZ 14,381 Most Valuable Professional on at
    Re: Flattening an array in an efficient way avoiding for each loops

    Okay, see if this approach works for you.

    I have used an array for every node that has child nodes.

    You can extrapolate it as per your needs.

    SudeepGhatakNZ_0-1714429678259.png

     

    Arrayexample_2024...
  • CH-22072116-0 Profile Picture
    CH-22072116-0 on at
    Re: Flattening an array in an efficient way avoiding for each loops

    Thanks @SudeepGhatakNZ 

    I will have to take a closer look at this but I'm afraid that this might be slow due to the for each. I currently have a solution in place with for each but it takes ages. I've seen posts where they optimise using root / xml / xpath and I wanted to give it a try to improve the performance.

  • SudeepGhatakNZ Profile Picture
    SudeepGhatakNZ 14,381 Most Valuable Professional on at
    Re: Flattening an array in an efficient way avoiding for each loops

    That is correct. You must leverage the xpath in that case. I will try to post an example later today. By then someone else might respond to you

     

  • CH-22072116-0 Profile Picture
    CH-22072116-0 on at
    Re: Flattening an array in an efficient way avoiding for each loops

    Thanks @SudeepGhatakNZ 

     

    I cross fingers that someone will be able to help using xpath... I've been struggling for hours now because I missed adding "normalize-space" and triple ''' for the concat... but I'm moving on in the right direction making few steps and learning at the same time !

    xpath(outputs('Compose_-_XML_comverted'), concat('count(/root/array[normalize-space(Attribute3)=''', item(), '''])'))

    Looking forward for some extra help.

  • Chriddle Profile Picture
    Chriddle 8,279 Super User 2025 Season 2 on at
    Re: Flattening an array in an efficient way avoiding for each loops

    Probably not the most elegant way to do it, but I couldn't think of anything better 😉

     

    Chriddle_1-1714482088150.png

     

    Data (Compose)

     

    Spoiler (Highlight to read)
    [
 {
 "attribute1": "12625",
 "attribute2": "2285",
 "attribute3": "Alabama",
 "attribute4": "USA",
 "attribute5": "Forest",
 "attribute6": "Exception",
 "attribute7": "Restricted Area",
 "attribute8": null,
 "attribute9": null,
 "attribute10": [
 {
 "subattribute1": "ABC123",
 "subattribute2": "NO",
 "subattribute3": "",
 "subattribute4": "",
 "subattribute5": 0,
 "subattribute6": "q_0",
 "subattribute7": null,
 "subattribute8": false
 },
 {
 "subattribute1": "AZE985",
 "subattribute2": "YES",
 "subattribute3": "",
 "subattribute4": "",
 "subattribute5": 1,
 "subattribute6": "q_1",
 "subattribute7": {
 "subsubattribute1": 2084,
 "subsubattribute2": "resolved",
 "subsubattribute3": "",
 "subsubattribute4": true,
 "subsubattribute5": "HIGH",
 "subsubattribute6": "FAC"
 },
 "subattribute8": true
 }
 ]
 },
 {
 "attribute1": "18001",
 "attribute2": "3768",
 "attribute3": "Dubai",
 "attribute4": "UAE",
 "attribute5": "Level 1",
 "attribute6": "Exception",
 "attribute7": "Public",
 "attribute8": null,
 "attribute9": null,
 "attribute10": [
 {
 "subattribute1": "ADE865",
 "subattribute2": "NO",
 "subattribute3": "",
 "subattribute4": "",
 "subattribute5": 0,
 "subattribute6": "q_0",
 "subattribute7": {
 "subsubattribute1": 4706,
 "subsubattribute2": "open",
 "subsubattribute3": "",
 "subsubattribute4": false,
 "subsubattribute5": "SEVERE",
 "subsubattribute6": "FAB"
 },
 "subattribute8": true
 }
 ]
 }
]
    [ { "attribute1": "12625", "attribute2": "2285", "attribute3": "Alabama", "attribute4": "USA", "attribute5": "Forest", "attribute6": "Exception", "attribute7": "Restricted Area", "attribute8": null, "attribute9": null, "attribute10": [ { "subattribute1": "ABC123", "subattribute2": "NO", "subattribute3": "", "subattribute4": "", "subattribute5": 0, "subattribute6": "q_0", "subattribute7": null, "subattribute8": false }, { "subattribute1": "AZE985", "subattribute2": "YES", "subattribute3": "", "subattribute4": "", "subattribute5": 1, "subattribute6": "q_1", "subattribute7": { "subsubattribute1": 2084, "subsubattribute2": "resolved", "subsubattribute3": "", "subsubattribute4": true, "subsubattribute5": "HIGH", "subsubattribute6": "FAC" }, "subattribute8": true } ] }, { "attribute1": "18001", "attribute2": "3768", "attribute3": "Dubai", "attribute4": "UAE", "attribute5": "Level 1", "attribute6": "Exception", "attribute7": "Public", "attribute8": null, "attribute9": null, "attribute10": [ { "subattribute1": "ADE865", "subattribute2": "NO", "subattribute3": "", "subattribute4": "", "subattribute5": 0, "subattribute6": "q_0", "subattribute7": { "subsubattribute1": 4706, "subsubattribute2": "open", "subsubattribute3": "", "subsubattribute4": false, "subsubattribute5": "SEVERE", "subsubattribute6": "FAB" }, "subattribute8": true } ] } ]

    Xml (Compose)

     

    xml(json(concat('{"Root":{"Item":', outputs('Data'), '}}')))

     

    IncompleteObjects (Select)

    From

    range(
	0,
	length(
		xpath(
			outputs('Xml'),
			'//Item | //attribute10 '
		)
	)
)

    Map

    addProperty(	
	addProperty(
		coalesce(
			json(xpath(outputs('Xml'), '//Item | //attribute10 ')[item()])?['Item'],
			json(xpath(outputs('Xml'), '//Item | //attribute10 ')[item()])?['attribute10']
		),
		'IsBase',
		not(equals(
			json(xpath(outputs('Xml'), '//Item | //attribute10 ')[item()])?['Item'],
			null
		))
	),
	'Index',
	item()
)

     

    BaseIndexes (Select)

    From

    body('IncompleteObjects')

    Map

    if(
	item()['Isbase'],
	item()['Index'],
	0
)

     

    Objects (Select)

    From

    range(1, sub(length(body('IncompleteObjects')), 1))

    Map

    if(
	not(
		body('IncompleteObjects')[item()]['IsBase']
	),
	json(
		concat(
			'{',
			'"attribute3":"',
			body('IncompleteObjects')[
				max(
					take(
						body('BaseIndexes'),
						body('IncompleteObjects')[item()]['Index']
					)
				)
			]?['attribute3'], '",',
			'"attribute4":"',
			body('IncompleteObjects')[
				max(
					take(
						body('BaseIndexes'),
						body('IncompleteObjects')[item()]['Index']
					)
				)
			]?['attribute4'], '",',
			
			'"subattribute1":"', body('IncompleteObjects')[item()]?['subattribute1'], '",',
			'"subattribute2":"', body('IncompleteObjects')[item()]?['subattribute2'], '",',

			'"subsubattribute2":"', body('IncompleteObjects')[item()]?['subattribute7']?['subsubattribute2'], '",',
			'"subsubattribute5":"', body('IncompleteObjects')[item()]?['subattribute7']?['subsubattribute5'], '",',
			'}'
		)
	),
	null
)

     

    Filter array

    From

    body('Objects')

    Filter

    @not(equals(item(), null))

     

    Output

    [
 {
 "attribute3": "Alabama",
 "attribute4": "USA",
 "subattribute1": "ABC123",
 "subattribute2": "NO",
 "subsubattribute2": "",
 "subsubattribute5": ""
 },
 {
 "attribute3": "Alabama",
 "attribute4": "USA",
 "subattribute1": "AZE985",
 "subattribute2": "YES",
 "subsubattribute2": "resolved",
 "subsubattribute5": "HIGH"
 },
 {
 "attribute3": "Dubai",
 "attribute4": "UAE",
 "subattribute1": "ADE865",
 "subattribute2": "NO",
 "subsubattribute2": "open",
 "subsubattribute5": "SEVERE"
 }
]

     

  • CH-22072116-0 Profile Picture
    CH-22072116-0 on at
    Re: Flattening an array in an efficient way avoiding for each loops

    Waaww, thanks @Chriddle !

    I'll test that out asap and try to understand exactly what you're doing... I've read, reviewed a lot of videos during the last days to understand more about xpath. But this is another level 😉
    My inspiration video : https://www.youtube.com/watch?v=VSvoB7bTV6o

     

    Elegant or not if it does the trick then it works for me 😉 And it's not using for loops so I'll not face performance issues.

    Keep you posted.

     

    Many thanks

  • Verified answer
    Chriddle Profile Picture
    Chriddle 8,279 Super User 2025 Season 2 on at
    Re: Flattening an array in an efficient way avoiding for each loops

    And there is a better way 😁

    (Same input and output as above)

     

    Chriddle_0-1714492340691.png

    Select

    From

     

    range(
	0,
	length(
		xpath(
			outputs('Xml'),
			'//attribute10 '
		)
	)
)

     

    Map

     

    json(
	concat(
		'{',
		'"attribute3":"',
		xpath(
			outputs('Xml'),
			concat('string((//attribute10)[', add(item(), 1), ']/parent::*/attribute3)')
		),
		'",',
		'"attribute4":"',
		xpath(
			outputs('Xml'),
			concat('string((//attribute10)[', add(item(), 1), ']/parent::*/attribute4)')
		),
		'",',
		'"subattribute1":"', 
		xpath(
			outputs('Xml'),
			concat('string((//attribute10)[', add(item(), 1), ']/subattribute1)')
		),
		'",',
		'"subattribute2":"', 
		xpath(
			outputs('Xml'),
			concat('string((//attribute10)[', add(item(), 1), ']/subattribute2)')
		),
		'",',
		'"subsubattribute2":"', 
		xpath(
			outputs('Xml'),
			concat('string((//attribute10)[', add(item(), 1), ']/subattribute7/subsubattribute2)')
		),
		'",',
		'"subsubattribute5":"', 
		xpath(
			outputs('Xml'),
			concat('string((//attribute10)[', add(item(), 1), ']/subattribute7/subsubattribute5)')
		),
		'"',
		'}'
	)
)

     

  • CH-22072116-0 Profile Picture
    CH-22072116-0 on at
    Re: Flattening an array in an efficient way avoiding for each loops

    @Chriddle 

    This look even more amazing !

     

    I was still trying to understand and implementing the previous version that you come with this super improved version... 

    Let me have a look and confirm if this does the trick.

    Thank you soo much, appreciate your help.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Tom Macfarlan – Community Spotlight

We are honored to recognize Tom Macfarlan as our Community Spotlight for October…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 740 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 489 Moderator

#3
developerAJ Profile Picture

developerAJ 403

Last 30 days Overall leaderboard

Featured topics

Survey: Power Automate mobile app
How to Ask for Help with Your Power Automate Workflow

Product updates

Microsoft Power Platform Community release plans