Unanswered

Build a JSON string which have the Status and the total number of items under each status

(0) Share

Report

Posted on by johnjohnPter

1,803 Super User 2024 Season 1

I have a manual power automate flow which get called from Power Apps, where i am passing FromDate & ToDate, and i am getting the items which where created between those 2 dates values, as follow:-

Now i want to build a JSON which show the Status and the TotalNumber of items under each status, such as, and send it back to Power app:-

{
"Statuses": [
    {
      "Status": "In Progress",
      "Count": 12
    },
    {
      "Status": "Completed",
      "Count": 8
    },
    {
      "Status": "Pending",
      "Count": 5
    },
    {
      "Status": "Cancelled",
      "Count": 2
    }
]
}

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (0)

Chriddle 8,416 Super User 2025 Season 2 on at

Like (2)

Report
Build a JSON string which have the Status and the total number of items under each status

Use xpath() in a Select:

From:

union( xpath( xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))), '//Item/Status/text()' ), json('[]') )

Map Statuses:

item()

Map Count:

xpath( xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))), concat('count(//Item[Status="', item(), '"])') )

Output:

Was this reply helpful? Yes No
johnjohnPter 1,803 Super User 2024 Season 1 on at

Like (0)

Report

Build a JSON string which have the Status and the total number of items under each status

@Chriddle

Ok i tried this where i used the same formuals are yours, but since i do not have Compose, so i used the output of the GetItems action, but i got empty result, as follow:-

Here what i defined inside the Select:-

From:-

union(
    xpath(
        xml(json(concat('{"Root":{"Item":', outputs('Get_items-Cotracts')?['body/value'], '}}'))),
        '//Item/Status/text()'
    ),
    json('[]')
)

Map:-

xpath(
    xml(json(concat('{"Root":{"Item":', outputs('Get_items-Cotracts')?['body/value'], '}}'))),
    concat('count(//Item[Status="', item(), '"])')
)

Any advice? Thanks for your help

Here is sample of the items i am getting from the GetItems action:-

Ok i think i found the issue i need to use Status/Value to get the value of the status, since it is a option and not free text.. thanks a lot for your help..

Was this reply helpful? Yes No
Chriddle 8,416 Super User 2025 Season 2 on at

Like (1)

Report

Build a JSON string which have the Status and the total number of items under each status

Sorry, the community software butchered my post. There were some images attached ;)

I had a simpler structure like this:

[

{"Status": In Progress},

...

]

Because of the deeper nesting in your JSON, the xpath expressions must be something like these:

'//Item/Status/Value/text()'

and

concat('count(//Item[Status/Value="', item(), '"])')

Was this reply helpful? Yes No
Chriddle 8,416 Super User 2025 Season 2 on at

Like (1)

Report

Build a JSON string which have the Status and the total number of items under each status

Or, assuming there is no other property name "Value" in this JSON you could also use

'//Value/text()'

and

concat('count(//*[Value="', item(), '"])')

Was this reply helpful? Yes No
johnjohnPter 1,803 Super User 2024 Season 1 on at

Like (0)

Report

Build a JSON string which have the Status and the total number of items under each status

@Chriddle

Ok worked, thanks a lot

Was this reply helpful? Yes No