Getting the data out of array of objects

[Chmmi]

Hi,

I working on pulling a list of agents from the system using Power Automate. I want to list users inactive in last 60 days.
I am using a HTTP request to get the list of all agents (max 50 agents). I have more than 50 agents so I have put the request in do-until loop working till he response is empty.

The command iterates 5 times and gives 5 lists of agents. I have used "Append to array variable" to add the lists together to have them all in one. I have put this in a separate variable.

Now I want to pull only Name, Surname, email and Last activity day from the combined set of data and then put that into HTML table.

The problem is I cannot do this.
Current Flow:

Page - variable for changing the page the API requests (set as1)
UserVariable - array variable where the response of the API is stored (first one is empty)

In the Do-Until loop:
Get Users - the API request getting the data about users, each request has different page variable listing more users

Incement Page - adds 1 to the Page variable (so the nest Get Users action shows the nest list of users)

LoopCheck - checks if the response of Get Users is not empty

Update users Variable - is the "Append to array variable" where the Get Users responses are merged.

Outside of the loop:
AllUsers - Compose action containing the result for Update users Variable.

All Users has looks more or less like this:

[
{
   "agents":[
      {
         "id":1434,
         "first_name":"Rubeus",
         "last_name":"Hagrid",
         "occasional":false,
         "active": true,
         "job_title":"Gamekeeper",
         "email":"rubeus.hagrid@hogwarts.edu",
         "work_phone_number":"5564435",
         "mobile_phone_number":"664345",
         "department_ids":[
            554
         ],
         "can_see_all_tickets_from_associated_departments":false,
         "reporting_manager_id":32,
         "address":"Gryffindor Tower",
         "time_zone":"Edinburgh",
         "time_format":"12h",
         "language":"en",
         "location_id":34,
         "background_information":"",
         "scoreboard_level_id":1,
         "member_of":[
            3,
            6
         ],
         "observer_of": [
            9
         ],
         "member_of_pending_approval": [],
         "observer_of_pending_approval": [],
         "roles": [
            {"role_id":7,"assignment_scope":"member_groups","groups":[]}
         ],
         "last_login_at":"2018-08-30T07:46:41Z",
         "last_active_at":"2018-08-30T07:46:41Z",
         "custom_fields":{
           "house": "Gryffindor"
         },
         "has_logged_in":false
      },
      {
         "id":4223,
         "first_name":"Filius",
         "last_name":"Flitwick",
         "occasional":false,
         "active": true,
         "job_title":"Professor - Charms",
         "email":"filius.flitwick@hogwarts.edu",
         "work_phone_number":"5564436",
         "mobile_phone_number":"664346",
         "reporting_manager_id":32,
         "address":"Gryffindor Tower",
         "time_zone":"Edinburgh",
         "time_format":"12h",
         "language":"en",
         "location_id":34,
         "background_information":"",
         "scoreboard_level_id":1,
         "member_of":[
            7,
            8
         ],
         "observer_of":[
            9
         ],
         "member_of_pending_approval": [],
         "observer_of_pending_approval": [],
         "roles": [
           {"role_id":6,"assignment_scope":"specified_groups","groups":[7]},
           {"role_id":8,"assignment_scope":"specified_groups","groups":[8]}
         ],
         "last_login_at":"2020-06-01T07:46:41Z",
         "last_active_at":"2020-08-30T07:46:41Z",
         "custom_fields":{
           "house": "Ravenclaw"
         },
         "has_logged_in":false
      }
   ]
},
{
   "agents":[
      {
         "id":1434,
         "first_name":"Rubeus",
         "last_name":"Hagrid",
         "occasional":false,
         "active": true,
         "job_title":"Gamekeeper",
         "email":"rubeus.hagrid@hogwarts.edu",
         "work_phone_number":"5564435",
         "mobile_phone_number":"664345",
         "department_ids":[
            554
         ],
         "can_see_all_tickets_from_associated_departments":false,
         "reporting_manager_id":32,
         "address":"Gryffindor Tower",
         "time_zone":"Edinburgh",
         "time_format":"12h",
         "language":"en",
         "location_id":34,
         "background_information":"",
         "scoreboard_level_id":1,
         "member_of":[
            3,
            6
         ],
         "observer_of": [
            9
         ],
         "member_of_pending_approval": [],
         "observer_of_pending_approval": [],
         "roles": [
            {"role_id":7,"assignment_scope":"member_groups","groups":[]}
         ],
         "last_login_at":"2018-08-30T07:46:41Z",
         "last_active_at":"2018-08-30T07:46:41Z",
         "custom_fields":{
           "house": "Gryffindor"
         },
         "has_logged_in":false
      },
      {
         "id":4223,
         "first_name":"Filius",
         "last_name":"Flitwick",
         "occasional":false,
         "active": true,
         "job_title":"Professor - Charms",
         "email":"filius.flitwick@hogwarts.edu",
         "work_phone_number":"5564436",
         "mobile_phone_number":"664346",
         "reporting_manager_id":32,
         "address":"Gryffindor Tower",
         "time_zone":"Edinburgh",
         "time_format":"12h",
         "language":"en",
         "location_id":34,
         "background_information":"",
         "scoreboard_level_id":1,
         "member_of":[
            7,
            8
         ],
         "observer_of":[
            9
         ],
         "member_of_pending_approval": [],
         "observer_of_pending_approval": [],
         "roles": [
           {"role_id":6,"assignment_scope":"specified_groups","groups":[7]},
           {"role_id":8,"assignment_scope":"specified_groups","groups":[8]}
         ],
         "last_login_at":"2020-06-01T07:46:41Z",
         "last_active_at":"2020-08-30T07:46:41Z",
         "custom_fields":{
           "house": "Ravenclaw"
         },
         "has_logged_in":false
      }
   ]
}
]

For my purposes it has 5 agents objects though.

I have tried to use ParseJson for this but it is always returning with the error because not all users are filled the same and some have some empty fields where some have content in there. I was also thinking about using apply to each but failed at choosing the inputs there.

My goal is to pull from All users the data about First NAme, Surnames, emails and last activity at. hen put it into HTML table and send it via teams to a team.

Can you help me?

[SudeepGhatakNZ]

@Chmmi 

There are two arrays in your object, probably that's why the default schema isn't working.

Extract the "Agents" first and then apply Parse JSON. Use the attached flow as reference

[Chriddle · Accepted solution]

Flattening an array is easy with xpath():

Compose

Your JSON

Select

From

xpath(
	xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))),
	'//Item/agents'
)

Map

json(item())['agents']

Create HTML table

From

body('Select')

Columns

Custom

Header

item()['first_name']

etc.

[Chmmi]

@Chriddle, I came across xpath solutions when trying to resolve this but had problems with applying it. Thanks for providing a detailed response.