How to get a item in an array which is in another array

[EricCai1999]

Hi,

I am trying to use get a item from array that is inside another array, 

but it shows the error of 'The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{
"Firstname": "@item()['first_name']",
"lastname": "@item()['last_name']",
"email": "@item()['parent_guardian']['email']"
}' failed: 'The template language expression 'item()['parent_guardian']['email']' cannot be evaluated because property 'parent_guardian' doesn't exist, available properties are 'id, '

my output file from a Http request and select screenshot in power automate  as below

the parent information

I am able to get first name and last name by using item().first_name and item().last_name, but I does not how to get the email item of parent_guardian array , I will output those information  to a csv file, could anyone please help me how to get the email information ? I really appreciate your help on this.

Best Regards,
Eric

[DamoBird365]

Hi @EricCai1999 

Try item()['parent_guardian'][0]['email'] or first(item()['parent_guardian'])['email'], noting that by adding ? it will return null if no key value is found e.g. first(item()['parent_guardian'])?['email'].  You need to use integer indexes if you are working within an array, as there could be multiple objects - even if there is only one.

Damien

[EricCai1999]

Thanks Damien, I try use first(item()['parent_guardian'])?['email'],  but it shows 'The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{
"Firstname": "@item()['first_name']",
"lastname": "@item()['last_name']",
"email": "@ first(item()['parent_guardian'])?['email']"
}' failed: 'The template language expression ' first(item()['parent_guardian'])?['email']' cannot be evaluated because property 'parent_guardian' doesn't exist'

I check 'parent_guardian' this property exist  in students array, I am not sure why it shows this error.

Best Regards,
Eric

[Mister_Shaik]

Hi @EricCai1999 ,

Assuming that the input of your Select action is the students array and not an object, you can try the below formula, I've ran a test with your data and it worked.

item()['parent_guardian']?[0]?['email']

If it still doesn't work, please check your input of your Select inputs, and let us know 🙂

Kind Regards,

Shaik Sha
________________________________________________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[EricCai1999]

many thanks Shaik,  I use item()['parent_guardian']?[0]?['email'], but the error message is still the same, 

the input is array , its data is from a http request , I does not use Parse Json for the request data , instead I get the data by using output function to get the student data from body

Best Regards,
Eric

[Mister_Shaik]

@EricCai1999 ,

Have you checked the output of initialize variable? 

I suspect that one of your student object's don't have a "Parent_Guardian" value.

Why don't you change your formula to check that, 

if(empty(item()['parent_guardian']),'',item()['parent_guardian']?[0]?['email'])

Kind Regards,

Shaik Sha
________________________________________________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[EricCai1999]

Thanks Shaik, some of student does not parent_guardian, I does not know what is the reason in our system, I am thinking another solution,  as the student data have student ID, and  we have another API to get parent data by passing this parent ID, how can I submit a http request with student ID and combine those 2 http requests to save student first name ,last name, parent email to a CSV file ?

Thank you.

[Mister_Shaik]

@EricCai1999 

Are you sure that the second API that you have would give the parent_guardian info for sure? If it does not, then it's back to square one, the flow would fail.

The formula I gave would save the guardian info as empty string when it's empty, instead of breaking the flow. You can first try that and check if you're getting the details without any issue.

[EricCai1999]

I am sure another API can provide the parent or guardian information when pass the student id as the parameter.

but I does not know how to this student ID to a http request url with this parameter.

I have try the formula you provide to me, but the error message is the same

[DamoBird365]

Hi @EricCai1999 ,

If it’s possible that this array might not exist, insert a ? at all levels.

item()?['parent_guardian']?[0]?['email']

There is a new feature coming in April to handle null values but for now you must specify if it could be null using ?.

See https://docs.microsoft.com/en-us/power-platform-release-plan/2022wave1/power-automate/handle-null-va...

Damien

[EricCai1999]

Thanks Damien and Shaik for the help . it is a strange issue that item()?['parent_guardian']?[0]?['email'] is not working for this project, i will wait for new feature is released

[Mister_Shaik · Accepted solution]

Hi @EricCai1999 ,

Can you give one last try, instead of using "item()?['parent_guardian']?[0]?['email']", try using "if(empty(item()['parent_guardian']),'',item()['parent_guardian']?[0]?['email'])"

[EricCai1999]

Thanks Shaik, it seems 'parent_guardian' does not exist , it is real frustrated , but I have 2 API, one is to get students data, another is to get parent data,

1. with student api, I can get fist name, last name ,email , parent id  for each student

2. with parent api, I can get parent name, parent email, parent id

I want to combine those 2 API to get  first name, student name, parent email for each student,  i have no idea how to accomplish  this, can you show me how to do this ?

Thank you for the great help.