Filter and sum JSON

[Anonymous]

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”
  }
}

[ekarim2020 · Accepted solution]

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.

 

"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.

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

Convert the string above back to a JSON object.

Filter for billable entries only:

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

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 :

 

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

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

This is the runtime output I get:

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]

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