Need help in getting data from Array

[vikrantyadav11]

Hi Team,

I am trying to create an excel file using array coming via API. I am able to get of the data but not sure how to get data from child array. 

Here is the sample date : 

I am able to get account_id , name, email but I am not able to get each product last_active date. 
I want to store account_id,name, email and last_active only for those users in which under product_access key : 'jira-software' and  "workspaceAri": "ari:cloud:jira-software::site/95531a2e-c5af-4074-bf24-7cbaf74d4a25",

[
            {
                "account_id": "557058:0027676d030f5aaf",
                "account_type": "atlassian",
                "account_status": "inactive",
                "name": "Ishant",
                "picture": "",
                "email": "ishant@abc.com",
                "access_billable": false,
                "last_active": "2018-08-23T11:56:10.979248Z",
                "product_access": [
                    {
                        "name": "Trello",
                        "key": "trello",
                        "tier": "Free",
                        "realm": "global",
                        "regions": [],
                        "url": "trello.com",
                        "last_active": "2016-10-03T09:47:47.969Z"
                    },
                    {
                        "name": "Bitbucket",
                        "key": "bitbucket",
                        "realm": "global",
                        "regions": [],
                        "url": "bitbucket.org",
                        "last_active": "2018-08-23T11:56:10.979248Z"
                    }
                ],
                "links": {
                    "self": "https://api.atlassian.com/users/557058:f8-d66d030f5aaf/manage/profile"
                }
            },    "account_id": "557058:01205c5b-29-62d9dae4c453",
                "account_type": "atlassian",
                "account_status": "active",
                "name": "Nial",
                "picture": "",
                "email": "niall@abc.com",
                "access_billable": true,
                "last_active": "2024-07-10T15:00:14.394848115Z",
                "product_access": [
                    {
                        "name": "Opsgenie",
                        "key": "opsgenie",
                        "siteId": "919e3a55-c-573f51bc2e2a",
                        "realm": "global",
                        "regions": [],
                        "workspaceAri": "ari:cloud:opsgenie::site/919e3a55-c9c4-4df8-acfd-573f51bc2e2a",
                        "url": "spddev.atlassian.net",
                        "last_active": "2022-09-30T20:38:53.545922Z"
                    },
                    {
                        "name": "Jira Software",
                        "key": "jira-software",
                        "siteId": "95531a2e-c5af-4074-7cbaf74d4a25",
                        "realm": "global",
                        "regions": [],
                        "workspaceAri": "ari:cloud:jira-software::site/95531a2e-c5af-4074-bf24-7cbaf74d4a25",
                        "url": "spr.atlassian.net",
                        "last_active": "2024-07-10T15:00:14.394848115Z"
                    },
                    {
                        "name": "Jira Software",
                        "key": "jira-software",
                        "siteId": "cb8997e3-21d0-41-52e4466be317",
                        "realm": "global",
                        "regions": [],
                        "workspaceAri": "ari:cloud:jira-software::site/cb8997e3-21d0-43ca-8321-52e4466be317",
                        "url": "spr-sandbox.atlassian.net"
                    },
                    {
                        "name": "Bitbucket",
                        "key": "bitbucket",
                        "realm": "global",
                        "regions": [],
                        "url": "bitbucket.org",
                        "last_active": "2020-12-10T15:21:16.184282Z"
                    }
                ],
                "links": {
                    "self": "https://api.atlassian.com/users/557058:012051a9-62d9dae4c453/manage/profile"
                }
            }
]

[Sayan]

Hey @vikrantyadav11 

To extract and store specific data from a nested array in Power Automate Cloud flow, you need to use nested loops and conditions. Below is a step-by-step guide - 

Initialize a Variable for the Array:

• Add an "Initialize variable" action to store the array from the API response.

Parse JSON:

• Add a "Parse JSON" action to parse the array data. Use the array variable as the content and define the schema based on the sample data.

Apply to Each Loop for Users:

• Add an "Apply to each" action to loop through each user in the parsed JSON.

Get Account Details:

• Inside the "Apply to each" loop, use "Compose" actions to extract account_id, name, and email for each user.

Apply to Each Loop for Product Access:

• Inside the user loop, add another "Apply to each" action to loop through the product_access array for each user.

Condition to Filter Jira Software:

• Inside the nested loop, add a "Condition" action to check if key is "jira-software" and workspaceAri is "ari☁️jira-software::site/95531a2e-c5af-4074-bf24-7cbaf74d4a25".

Get Last Active Date:

• If the condition is true, use a "Compose" action to extract last_active from the product access array.

Store Data in an Excel File:

• After extracting the required data, use the "Add a row into a table" action to store account_id, name, email, and last_active into an Excel file.

------------------------------------------------------------------------------------------------------------------------------

If my suggestion helped you, please give it a Thumbs up 👍 and Mark it as a Solution ✔️.

This Motivates to solve more problems.
Sayan Patra
RPA Developer

[vikrantyadav11]

Hi @Sayan  
Thanks your response!
But I am using Select array to get data from API call and doing further filtration using Filter Array. 

Not able to find a way to get data product_access last_active date where workspaceari is ari☁️jira-software::site/95531a2e-c5af-4074-bf24-7cbaf74d4a25
I have tried to add if else condition on workspaceAri but it's giving false for each product_access

ari:cloud:jira-software::site/95531a2e-c5af-4074-bf24-7cbaf74d4a25

[Sayan]

Hey @vikrantyadav11 

Thanks for the sharing the Workflow. looking into it.

Thanks

Sayan Patra

[Sayan · Accepted solution]

Hey @vikrantyadav11 

Please follow the below workflow: 

Main Workflow:

Solution: 

Initialize a Variable for the Array:

Parse JSON:

Schema:

{
"type": "array",
"items": {
"type": "object",
"properties": {
"account_id": {
"type": "string"
},
"account_type": {
"type": "string"
},
"account_status": {
"type": "string"
},
"name": {
"type": "string"
},
"picture": {
"type": "string"
},
"email": {
"type": "string"
},
"access_billable": {
"type": "boolean"
},
"last_active": {
"type": "string"
},
"product_access": {
"type": "array",
"items": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"key": {
"type": "string"
},
"tier": {
"type": "string"
},
"realm": {
"type": "string"
},
"regions": {
"type": "array",
"items": {
"type": "string"
}
},
"url": {
"type": "string"
},
"last_active": {
"type": "string"
},
"siteId": {
"type": "string"
},
"workspaceAri": {
"type": "string"
}
}
}
},
"links": {
"type": "object",
"properties": {
"self": {
"type": "string"
}
}
}
}
}
}

Apply to Each Loop for Users:

Apply to Each Loop for Product Access:

Condition to Filter Jira Software:

Hope this solution will work.

------------------------------------------------------------------------------------------------------------------------------

If my suggestion helps you, please give it a Thumbs up 👍 and Mark it is a Solution ✔️. This motivates to solve more problems.

Thanks,

Sayan Patra

RPA Developer

[vikrantyadav11]

Hi @Sayan  Thanks for the solution. 
In condition have you string('jira-software') or simple put is equal to jira-software. 

[Sayan]

Hi @vikrantyadav11 

it is simple equal to.

Please let me know. If anything, need.

Thanks,

Sayan Patra