You’re offline. This is a read only version of the page.
Announcements
53
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:
Share
Report[
{
"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 :
All responses (
Answers (
xml(json(concat('{"Root":{"Item":', @{outputs('Data')}, '}}')))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 🤣
Impressive indeed!
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
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.
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)')
),
'"',
'}'
)
)
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
Probably not the most elegant way to do it, but I couldn't think of anything better 😉
Data (Compose)
[
{
"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"
}
]
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.
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
