JSONata (or JS) - group and sum JSON array / objects

Question

I want to 'groupsum' an array of objects with JSONata, from the following array:

payload = [
 {"id":"Irr-1","name":"Irrigation","timedif_s":7.743},
 {"id":"Irr-2","name":"Irrigation","timedif_s":2.749},
 {"id":"Lig-2","name":"Lights","timedif_s":1.475},
 {"id":"Irr-1","name":"Irrigation""timedif_s":1.07},]

The results must look like:

[{"name":"Irrigation", "sumtimedif": 11.562},
 {"name":"Lig-1", "sumtimedif": 1.475}]

I have tried:

payload.name{"name":payload.name,"sumtimedif":$sum(timedif_s)}

But I only get back an empty string.

{ empty }

Any advise?

Andrew Coleman · Accepted Answer · 2018-12-24 08:58:19Z

2

The following JSONata expression will do this:

payload{
  name: $sum(timedif_s)  /* group by name */
} ~> $each(function($v, $k) {
 {"name": $k, "sumtimedif": $v}  /* transform each key/value pair */
})

The first part of the expression does the grouping and aggregation, and the second part transforms each group (key/value pair) into the format you want. http://try.jsonata.org/ByxzyQ0x4

answered Dec 24, 2018 at 8:58

Andrew Coleman

1,62711 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jéan Over a year ago

What if I want to add a 2nd group-by keypair, and get totals with that, I tried to modify your statement in several ways, I cannot get it to work? Let's say a new keypair in above --> "date" : "2018-12-24" ? @Andrew-Coleman

Jéan · Accepted Answer · 2018-12-22 20:45:31Z

0

I found another good post that helped me: Most efficient method to groupby on a array of objects.

Not sure if this can be done in JSONata, so I reverted to JS, as follows:

var arr = msg.payload ;
arr1 = groupBy(arr, 'name', 'timedif_s' ) ;
msg.payload = arr1 ;
return msg;
//---------------------------------------------
function groupBy(array, col, value) {
    var r = [], o = {};
    array.forEach(function (a) {
        if (!o[a[col]]) {
            o[a[col]] = {};
            o[a[col]][col] = a[col];
            o[a[col]][value] = 0;
            r.push(o[a[col]]);
        }
        o[a[col]][value] += +a[value];
    });
    return r;
}

answered Dec 22, 2018 at 20:45

Jéan

7274 gold badges14 silver badges29 bronze badges

Collectives™ on Stack Overflow

JSONata (or JS) - group and sum JSON array / objects

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related