cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filter and sum JSON

Hello,

 

I am stuck and having a hard time wrapping my head around parsing JSON (I think that's what I am trying to do).  I have a fairly complicated Flow and I'm looking to generate a single value to then pass back to PowerApps.  I have tried a variety of methods and usually end up with it telling me I'm trying to use an object for an array or the other way around...I'm at a point where I'm just lost.

 

I got as far as Compose producing the following output snippet.  I am looking to generate a single value as a result of the following output being the sum of time_in_minutes where billable is true. 

 

What is the best way to generate this single value I am looking for as a result of filtering and summing?

 

For example, below I have three records:

123456789 (billable is true) = 130 minutes

123465788 (billable is true) = 60 minutes

123456787 (billable is false) = 45 minutes

 

I'm looking to take the following data and end up with a value of "190" (130+60 minutes above for billable true).

 

 

 

{
  “123456789”: {
    "location": null,
    "created_at": "2000-01-00T01:01:01-01:00",
    "updated_at": "2001-01-00T01:01:01-01:00"",
    "date_performed": "2002-01-01”,
    "time_in_minutes": 130,
    "billable": true,
    "notes": "",
    "rate_in_cents": 0,
    "cost_rate_in_cents": 0,
    "currency": "USD",
    "currency_symbol": "$",
    "currency_base_unit": 100,
    "approved": true,
    "taxable": null,
    "is_invoiced": false,
    "id": “77777”
  },
  “123456788: {
    "location": null,
     "created_at": "2000-01-00T01:01:01-01:00",
    "updated_at": "2001-01-00T01:01:01-01:00"",
    "date_performed": "2002-01-01”,
    "time_in_minutes": 60,
    "billable": true,
    "notes": "",
    "rate_in_cents": 0,
    "cost_rate_in_cents": 0,
    "currency": "USD",
    "currency_symbol": "$",
    "currency_base_unit": 100,
    "approved": true,
    "taxable": null,
    "is_invoiced": false,
    "id": “88888”
  },
  “123456787: {
    "location": null,
    "created_at": "2000-01-00T01:01:01-01:00",
    "updated_at": "2001-01-00T01:01:01-01:00"",
    "date_performed": "2002-01-01”,
    "time_in_minutes": 45,
    "billable": false,
    "notes": "",
    "rate_in_cents": 0,
    "cost_rate_in_cents": 0,
    "currency": "USD",
    "currency_symbol": "$",
    "currency_base_unit": 100,
    "approved": true,
    "taxable": null,
    "is_invoiced": false,
    "id": “99999”
  }
}

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ekarim2020
Super User
Super User

If your data is in an array format, then we could reformat the JSON data, select only the billable items and then sum the time.

Snag_4d71ad0.png

ekarim2020_1-1646920527136.png

 

Snag_4d8951c.png

"reformat" the JSON data by converting it into a string and replacing text to create a new JSON object. The replace() function only works on strings, not JSON Objects.

ekarim2020_0-1646919865015.png

 

replace(replace(replace(replace(string(variables('varData')),'[{"', '[{"order_id":"'),'"},"' , '"}},{"order_id":"'),'"}]', '"}}]'),':{"location', ',"details": {"location')

 

Convert the string above back to a JSON object.

Snag_4e235b6.png

Filter for billable entries only:

Snag_4e2eca2.png

From the billable items, select all the time_in_minutes property (this action will create an array containing the times):

Snag_4e35d7c.png

Then sum the above array. For the sum function I am using the following post: How to Sum an Array of Numbers in Power Automate :

Snag_4e4618e.png

 

{
  "root": {
    "Numbers": @{body('Select')}
  }
}

 

Snag_4e574f2.png

 

xpath(xml(outputs('ComposeJSON')), 'sum(/root/Numbers)')

 

This is the runtime output I get:

ekarim2020_2-1646920913410.png

 

Test thoroughly with different sets of data.

 

Hope this helps.

 


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

 

View solution in original post

2 REPLIES 2
ekarim2020
Super User
Super User

If your data is in an array format, then we could reformat the JSON data, select only the billable items and then sum the time.

Snag_4d71ad0.png

ekarim2020_1-1646920527136.png

 

Snag_4d8951c.png

"reformat" the JSON data by converting it into a string and replacing text to create a new JSON object. The replace() function only works on strings, not JSON Objects.

ekarim2020_0-1646919865015.png

 

replace(replace(replace(replace(string(variables('varData')),'[{"', '[{"order_id":"'),'"},"' , '"}},{"order_id":"'),'"}]', '"}}]'),':{"location', ',"details": {"location')

 

Convert the string above back to a JSON object.

Snag_4e235b6.png

Filter for billable entries only:

Snag_4e2eca2.png

From the billable items, select all the time_in_minutes property (this action will create an array containing the times):

Snag_4e35d7c.png

Then sum the above array. For the sum function I am using the following post: How to Sum an Array of Numbers in Power Automate :

Snag_4e4618e.png

 

{
  "root": {
    "Numbers": @{body('Select')}
  }
}

 

Snag_4e574f2.png

 

xpath(xml(outputs('ComposeJSON')), 'sum(/root/Numbers)')

 

This is the runtime output I get:

ekarim2020_2-1646920913410.png

 

Test thoroughly with different sets of data.

 

Hope this helps.

 


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

 

Anonymous
Not applicable

This worked great and also taught me quite a bit.  Thank you so much, Ellis!

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (4,685)