Flattening an array in an efficient way avoiding for each loops

[ChrisBI]

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

[SudeepGhatakNZ]

@ChrisBI 

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

[ChrisBI]

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]

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.

[ChrisBI]

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]

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

[ChrisBI]

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]

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

Data (Compose)

Spoiler

[
  {
    "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"
  }
]

[ChrisBI]

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 · Accepted solution]

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)')
		),
		'"',
		'}'
	)
)

[ChrisBI]

@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.

[ChrisBI]

@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

[SudeepGhatakNZ]

Impressive indeed!

[Chriddle]

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 🤣