linqjs group by with a sum

Question

This might seems like an easy question but I'm struggling with the linqjs syntax.

Given the following basic JSON:

{
    "DateEvent": "2013-04-23 14:00:00Z",
    "DateRecord": "2013-04-23 14:00:00Z",
    "Amount": -9500,
    "Type": {
        "Description": "Capital"
    },
    "Currency": {
        "ID": "USD"
    }
}

Using linqjs how can I return the total for each currency?

Enumerable.From(data)
    .GroupBy("{ currency: $.Currency.ID }", null, function (key, g) {
        var result = {
            currency: key.currency,
            total: g.Sum($.Amount)
        }
    });

The code above doesn't work.

score 7 · Accepted Answer · edited Sep 19 '21 at 21:47

7

You almost had it. Your key selector in your GroupBy and Sum is incorrect. Also the key selector needs to be a string. Try the following:

var result = Enumerable.from(data).groupBy("$.Currency.ID", null,
    function (key, g) {
        var result = {
            currency: key,
            total: g.sum("$.Amount")
        }
        return result;
    }).ToArray();

edited Sep 19 '21 at 21:47

A Farmanbar

4,381
5
24
42

answered Jul 24 '13 at 06:42

Rei Mavronicolas

1,387
9
12

score 3 · Answer 2 · edited Sep 19 '21 at 23:07

3

I am the author of the open-source project http://www.jinqJs.com

You could easily do it by executing:

new jinqJs().from(data).groupBy('Currency').sum('Amount').select();

edited Sep 19 '21 at 23:07

A Farmanbar

4,381
5
24
42

answered Mar 31 '15 at 02:14

NYTom

524
2
14

score 1 · Answer 3 · edited Sep 19 '21 at 21:47

Just to expand further on this question.

The following syntax will group the data by an additional field:

var result = Enumerable.from(data)
                       .groupBy("{ currency: $.Currency.ID, type: $.Type.Description }", null,
                            function (key, g) {
                                var result = {
                                     currency: key.currency,
                                     type: key.type,
                                     total: g.Sum("$.Amount")
                                }
                                return result;
                                }, function (x) { return x.currency + ':' + x.type }).ToArray();

score 1 · Answer 4 · edited Sep 19 '21 at 21:48

Assuming you have an array of objects that looks like that, you can write your query like this:

var query = Enumerable.from(data)
    .groupBy(
        "{ Currency: $.Currency.ID, Type: $.Type.Description }",
        "$.Amount",
        "{ Currency: $.Currency, Type: $.Type, Total: $$.Sum() }"
    )
    .ToArray();

Personally I find using the lambda string syntax more succinct and preferable. Mixing lambdas and functions leads to messier code IMHO.

score 1 · Answer 5 · answered Apr 18 '23 at 12:18

Update with Native JS

With widespread support for JS operations in nearly any browser and the proliferation of build tools to compile down when necessary, unless you're already importing linqjs, I'd recommend going with a native JS approach.

const grouped = data.reduce((acc, el) => {
  const key = el.Currency.ID
  const prev = acc[key] ?? 0
  acc[key] = prev + el.Amount;
  return acc;
}, {})

Demo in Stack Snippets

const data = [
    {
        "Amount": 5,
        "Currency": { "ID": "USD" }
    },
    {
        "Amount": 10,
        "Currency": { "ID": "USD" }
    },
    {
        "Amount": 20,
        "Currency": { "ID": "CAN" }
    }
]

const grouped = data.reduce((acc, el) => {
  const key = el.Currency.ID
  const prev = acc[key] ?? 0
  acc[key] = prev + el.Amount;
  return acc;
}, {})

console.log(grouped)

// {
//     "USD": 15,
//     "CAN": 20
// }

See Also: How to group by and sum an array of objects?

linqjs group by with a sum

5 Answers5

Update with Native JS

Demo in Stack Snippets

Linked