Answered

Flattening an array in an efficient way avoiding for each loops

(0) Share

Report

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

All responses (14)

Answers (1)

DS-04102110-0 2 on at

Like (0)

Report
Flattening an array in an efficient way avoiding for each loops

@Chriddle

What did you use for the 'Xml' Action? Did you use this:

xml(json(concat('{"Root":{"Item":', @{outputs('Data')}, '}}')))
Chriddle 7,839 Super User 2025 Season 1 on at

Like (1)

Report

Re: Flattening an array in an efficient way avoiding for each loops

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

When I posted the first approach, I already felt like I was overcomplicating things 🤣
SudeepGhatakNZ 14,381 Most Valuable Professional on at

Like (1)

Report

Re: Flattening an array in an efficient way avoiding for each loops

Impressive indeed!
CH-22072116-0 on at

Like (0)

Report

Re: Flattening an array in an efficient way avoiding for each loops

@Chriddle
Implemented and working like a charm and extremely fast 🎉

This will now allow me to work with regular filters to filter out the data quality issues. I was exploring filtering everything out with xpath but definitely not has easy has using the filter option.

Thanks again, really impressive result. 💪🏼🙏🏻
Chris
CH-22072116-0 on at

Like (0)

Report

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.
Verified answer

Chriddle 7,839 Super User 2025 Season 1 on at

Like (1)

Report
Re: Flattening an array in an efficient way avoiding for each loops

And there is a better way 😁

(Same input and output as above)

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 on at

Like (0)

Report

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
Chriddle 7,839 Super User 2025 Season 1 on at

Like (1)

Report
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 😉

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 on at

Like (0)

Report

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.
SudeepGhatakNZ 14,381 Most Valuable Professional on at

Like (1)

Report

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