web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Build a JSON string wh...
Power Automate
Unanswered

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

(0) ShareShare
ReportReport
Posted on by 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:
I have the same question (0)
  • Chriddle Profile Picture
    8,416 Super User 2025 Season 2 on at
    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:

  • johnjohnPter Profile Picture
    1,803 Super User 2024 Season 1 on at
    Build a JSON string which have the Status and the total number of items under each status
     
    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..
  • Chriddle Profile Picture
    8,416 Super User 2025 Season 2 on at
    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(), '"])')
  • Chriddle Profile Picture
    8,416 Super User 2025 Season 2 on at
    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(), '"])')
  • johnjohnPter Profile Picture
    1,803 Super User 2024 Season 1 on at
    Build a JSON string which have the Status and the total number of items under each status
     
    Ok worked, thanks a lot

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Leaderboard > Power Automate

#1
Tomac Profile Picture

Tomac 497 Moderator

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 477 Super User 2025 Season 2

#3
chiaraalina Profile Picture

chiaraalina 242

Last 30 days Overall leaderboard