Sum values for JSON objects only if they have a certain attribute

Rudy Holtkamp
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 18, 2024

Hi all,

I've got a pretty complex question. So here is a little bit of context:

In an automation rule I do a web request to Tempo and get a JSON back with list of worklogs. In some of these worklogs I do have a work attribute TFT. I want to sum all timeSpentSeconds values for all worklogs with the TFT attribute.

I've tried a regex (which does not work for me), a branch (I do get the values ,but I can't sum them, at least when I leave the branch, my sum variable is 0).

I know there are some automation wizards out there, so my hopes are on you!

Here is (a part of ) the JSON:

{ 
"self": "https://api.tempo.io/4/worklogs/user/$accountid?from=2024-01-01&to=2024-12-31&offset=0&limit=5000",
"metadata": {
"count": 498,
"offset": 0,
"limit": 5000
},
"results": [
{
"self": "https://api.tempo.io/4/worklogs/27386",
"tempoWorklogId": 27386,
"issue": {
"self": "https://$site.atlassian.net/rest/api/2/issue/90340",
"id": 90340
},
"timeSpentSeconds": 7200,
"billableSeconds": 0,
"startDate": "2024-02-04",
"startTime": "09:00:00",
"description": "Week 5",
"createdAt": "2024-01-31T16:08:45Z",
"updatedAt": "2024-01-31T16:08:57Z",
"author": {
"self": "https://$site.atlassian.net/rest/api/2/user?accountId=$accountid",
"accountId": "$accountid"
},
"attributes": {
"self": "https://api.tempo.io/4/worklogs/27386/work-attribute-values",
"values": [
{
"key": "_Account_",
"value": "$Account"
}, {
"key": "_TFT_",
"value": "true"
}
]}
}, {
"self": "https://api.tempo.io/4/worklogs/27303",
"tempoWorklogId": 27303,
"issue": {
"self": "https://$site.atlassian.net/rest/api/2/issue/90194",
"id": 90194
},
"timeSpentSeconds": 28800,
"billableSeconds": 28800,
"startDate": "2024-02-02",
"startTime": "09:00:00",
"description": "Part time day off",
"createdAt": "2024-01-30T15:03:12Z",
"updatedAt": "2024-04-22T15:18:03Z",
"author": {
"self": "https://$site.atlassian.net/rest/api/2/user?accountId=$accountid",
"accountId": "$accountid"
},
"attributes": {
"self": "https://api.tempo.io/4/worklogs/27303/work-attribute-values",
"values": [ {
"key": "_Account_",
"value": "$Account"
} ]
} }
]}

1 answer

1 accepted

1 vote
Answer accepted
Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 18, 2024

Hi @Rudy Holtkamp 

I would suggest trying smart value, list filtering or a regular expression with the match() function.

The challenge for a scenario like this is finding the correct point to split the result message, if it is not interpreted already as a list.

Have you checked the Tempo documentation or experimented to learn how to access the items as a list?

Kind regards,
Bill

Rudy Holtkamp
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 18, 2024

Hi @Bill Sheboy 

I can't come up with a Pröpper regex to match. 

With the filtering, is it possible to get data from the first list during iteration of the 2nd list?

{{#webresponse.body.results}}

{{#attributes.values}}

{{#if(key.equals("TFY"}}

{{timeSpentSeconds}}

{{/}}

{{/}}

{{/}}

 

So in this case I want to retrieve the timeSpentSeconds value which is part of the first list.

Any ideas?

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 18, 2024

No, that method would not work because once inside the nested iterator, the other attributes are not accessible.

How about still iterating at the higher scope, and filtering on the lower one using match()?

{{#webresponse.body.results}}

{{#if(attributes.values.key.match("(_TFT_)").size.gt(0)}}

{{timeSpentSeconds}}

{{/}}

{{/}}

 

Rudy Holtkamp
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 21, 2024

Thanks @Bill Sheboy ,

This brought me on the right track. I made it too complex in my head 😖.

For references this is what I ultimately used:

{{#=}}
(
{{#webresponse.body.results}}
{{#if(attributes.values.key.match("(_TFT_)").size.gt(0))}}
{{#if(attributes.values.value.match("(true)").size.gt(0))}}
{{timeSpentSeconds}}+
{{/}}
{{/}}
{{/}}0
)/3600
{{/}}

In words:

Iterate over the JSON response, search for a match of _TFT_ in the values keys, then search for true in the value.

When found get the timeSpentSeconds value and a plus sign

Finally add all these seconds and divide them by 3600 to get the hours.

Like Bill Sheboy likes this
Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 21, 2024

Well done!

I recommend checking the performance of the expression, and if necessary, perhaps comparing that to use and() rather than nested conditionals to see if there is a difference.

{{#=}}
(
{{#webresponse.body.results}}
{{#if(and(attributes.values.key.match("(_TFT_)").size.gt(0),attributes.values.value.match("(true)").size.gt(0)))}}
{{timeSpentSeconds}}+
{{/}}
{{/}}0
)/3600
{{/}}

 

Like Rudy Holtkamp likes this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
PERMISSIONS LEVEL
Product Admin
TAGS
AUG Leaders

Atlassian Community Events