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

Notifications

Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Flattening an array in...
Power Automate
Unanswered

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



Categories:
General topics
I have the same question (0)
  • SudeepGhatakNZ Profile Picture
    SudeepGhatakNZ 14,394 Most Valuable Professional on at

    @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

    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,394 Most Valuable Professional on at

    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

    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,394 Most Valuable Professional on at

    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

    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,436 Super User 2025 Season 2 on at

    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

    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,436 Super User 2025 Season 2 on at

    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

    @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
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 522 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 364 Moderator

#3
abm abm Profile Picture

abm abm 243 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Share your ideas and struggles with building Power Automate flows!
Survey: Power Automate mobile app
How to Ask for Help with Your Power Automate Workflow
Flows are not visible for users who sign in with their personal accounts
Start and wait for an approval action
Restore a deleted flow
List of actions cheat sheet for Power Automate Desktop
Question for everyone : How are you using Create Text GPT in AI Builder?
Welcome to the Power Automate forum!

Product updates

Microsoft Power Platform Community release plans