javascript linq.js groupby

Question

I have the following JSON data in a Javascript function (it's simplified here):

var data = [
  { Expiry: "2013-01-02T00:00:00", EndDate: "2013-01-16T00:00:00", Category: 10, Amount: 14.53 },
  { Expiry: "2013-01-02T00:00:00", EndDate: "2013-01-16T00:00:00", Category: 25, Amount: 14.86 },
  { Expiry: "2013-02-06T00:00:00", EndDate: "2013-02-20T00:00:00", Category: 10, Amount: 14.43 },
  { Expiry: "2013-02-06T00:00:00", EndDate: "2013-02-20T00:00:00", Category: 25, Amount: 14.76 }
];

Note: An EndDate value will always be the same for any specific Expiry value.

Now ideally I'd like to have the following JSON at the end (grouped by Expiry date and note the use of the Category value in the property name):

[
   {
     Expiry: 2013/1/2,     (note: not fussed about the date format)
     EndDate: 2013/1/16,
     Category10: 14.53,
     Category25: 14.86
   },
   {
     Expiry: 2013/2/6,
     EndDate: 2013/2/20,     
     Category10: 14.43,
     Category25: 14.76
   },
]

But not sure I'll get that all in one go so can settle for an array of Categories and Amounts under each date pair that I can then deal with to get the above somehow:

[
   {
     Expiry: 2013/1/2,    
     EndDate: 2013/1/16,
     Categories: [ { 10, 25 } ],
     Amounts: [ { 14.53, 14.86 } ]
   },
   {
     Expiry: 2013/2/6,
     EndDate: 2013/2/20,
     Categories: [ { 10, 25 } ],
     Amounts: [ { 14.43, 14.76 } ]
   },
]

I'm trying to use the groupby function in the linq.js library to get the necessary JSON but can't get to what I need.

With the following:

var result = linq.groupBy("$.Expiry", "", "k,e => { Expiry: k, Categories: e.select('$.Category').toArray(), Amounts: e.select('$.Amount').toArray()}").toArray();

I get:

[
   {
     Expiry: 2013/1/2,    
     Categories: [ { 10, 25 } ],
     Amounts: [ { 14.53, 14.86 } ]
   },
   {
     Expiry: 2013/2/6,
     Categories: [ { 10, 25 } ],
     Amounts: [ { 14.43, 14.76 } ]
   },
]

But I can't workout how to get the EndDate in...

Can anyone assist?

Ta,

Jeff Mercado · Accepted Answer · 2013-11-15T08:07:50.797

Your key should be a composite key with both the Expiry and EndDate. Then you can include it in your result.

I wouldn't recommend trying to get your ideal result, creating results like that aren't in the spirit of what LINQ was made to do. Rather, you should collect items you are querying for.

var query = Enumerable.From(data)
    .GroupBy(
        "{ Expiry: $.Expiry, EndDate: $.EndDate }",
        null,
        "{ Expiry: $.Expiry, EndDate: $.EndDate, Categories: $$.Select('$.Category').ToArray(), Amounts: $$.Select('$.Amount').ToArray() }",
        "$.Expiry + '-' + $.EndDate" // compare selector needed
    )
    .ToArray();

However it is still possible to get your ideal result, but again, I would recommend not using this approach.

var query = Enumerable.From(data)
    .GroupBy(
        "{ Expiry: $.Expiry, EndDate: $.EndDate }",
        null,
        function (key, g) {
            return g.Aggregate({
                Expiry: key.Expiry,
                EndDate: key.EndDate
            }, function (result, item) {
                result['Category' + item.Category] = item.Amount;
                return result;
            });
        },
        "$.Expiry + '-' + $.EndDate"
    )
    .ToArray();

javascript linq.js groupby

1 Answers1

Linked