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,120
5
20
39

answered Jul 24 '13 at 06:42

Rei Mavronicolas

1,379
8
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,120
5
20
39

answered Mar 31 '15 at 02:14

NYTom

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

linqjs group by with a sum

4 Answers4

Linked