Using Select to get data from a JSON object that sometimes has a nested array

[MBaldo]

Hi all,

I'm having an issue with a select object taking values from a JSON file, the issue is with the object 'Executive Responsible' is sometime an object and sometimes an array

Here's an example of two objects from the JSON file, the first, Sector, works fine:

"Sector": {
"url": "https://*******************/entries/3697708",
"id": 3697708,
"name": "******** Technology",
"entryListId": 18876
},

In the select I use

Item()?['Sector']?['name']

"Executive Responsible": {
"url": "https://*******************/4553582",
"id": 4553582,
"name": "Donald Duck",
"entryListId": -1
},

But Executive Responsible can also have an array if there's multiple items

"Executive Responsible": [
{
"url": "https://*******************/4553676",
"id": 4553676,
"name": "Scrooge McDuck",
"entryListId": -1
},
{
"url": "https://*******************/4553618",
"id": 4553618,
"name": "Mickey Mouse",
"entryListId": -1
}
],

So

Item()?['Executive Responsible']?['name']

returns the error

'The template language expression 'item()?['Executive Responsible']?['name']' cannot be evaluated because property 'name' cannot be selected. Array elements can only be selected using an integer index.

Ideally I would like to join the multiple name records and return a single value with a separator

which i expect would look like:

join(item()?['Executive Responsible']?['name'], ':')

But this returns the error

failed: 'The template language function 'join' expects its first parameter to be an array. The provided value is of type 'String'. Please see https://aka.ms/logicexpressions#join for usage details.'.

So I guess I need to allow for the fact not all of the entries are arrays but not sure how to do this, any suggestions?

[rzaneti · Accepted solution]

Hi @MBaldo ,

I ran some tests here. How many 'Executive Responsible' can you have in an item? I could manage to get their name either if we have only 1 (without array) or 2 Executive Responsible by using an if into the expression, but this solution is not scalable if you have an undefined qty of Executive Responsible.

Here is the expression: 

if(contains(item()['Executive Responsible'], 'name'), item()['Executive Responsible']['name'], concat(item()['Executive Responsible'][0]['name'], ':', item()['Executive Responsible'][1]['name']))

If you have a small quantity of possible entries (let's say, 5), we can manually create nested IFs to handle it. This is far away to be the best approach (as it is not scalable at all), but at least will solve your problem for now. 

And here is my test:

Input

Output

[MBaldo]

Thanks for responding Rzaneti, I've tried adding 

if(contains(item()['Executive Responsible'], 'name'), item()['Executive Responsible']['name'], concat(item()['Executive Responsible'][0]['name'], ':', item()['Executive Responsible'][1]['name']))

It returns the error

 cannot be evaluated because property 'Executive Responsible' doesn't exist, available properties are 'EntryId, Case Number, Deal, Project Name,  Completed Date, Transaction Type, Sector . Please see https://aka.ms/logicexpressions for usage details.

It doesn't appear in the available fields list in the error, is that as it's an array?

[Paulie78]

You could deal with this by using an xpath query, something like:

//Executive_x0020_Responsible/name/text()

Then it would always return the Executive name as an array which could be joined regardless of how many there are.

[rzaneti]

Hi @MBaldo ,

For any reason, the 'Executive Responsible' cannot be read by Power Automate. From which action are you pulling this JSON? Please check the raw outputs of the action that is generating the JSON and try to identify the name used for the 'Executive Responsible' property, then you will just need to replace it in your expression. 

If you don't know how to access the raw outputs, follow the instructions in this article: http://digitalmill.net/2023/06/29/how-to-access-the-raw-outputs-in-power-automate/ 

If you find trouble to identify the property name, share the outputs here (removing any sensitive data) and we can help you with.

[MBaldo]

Hi all,

Thanks for your help on this, primarily what I found however is doing this kind of work in Power Automate is quite frustrating. I decided to move the actual work downstream and used Power Query in Power BI to do the heavy work, where it took me about 20 minutes to complete. Pretty tough to do this in PA as it stands.