SUM and GROUP BY on multiple fields of JSON data using Linq.js

Question

I have a JSON data and I need to do group by and sum the data. Let's say I have a json object which looks like this:

var dataString='[{"Company":"ABC","Focus":"Operate","Completed":"50","Remaining":"25"},{"Company":"ABC","Focus":"Operate","Completed":"20","Remaining":"50"},{"Company":"DEF","Focus":"Optimize","Completed":"100","Remaining":"75"},{"Company":"XYZ","Focus":"Innovate","Completed":"100","Remaining":"75"},{"Company":"ABC","Focus":"Improve","Completed":"50","Remaining":"50"},{"Company":"ABC","Focus":"Optimize","Completed":"50","Remaining":"25"},{"Company":"DEF","Focus":"Operate","Completed":"50","Remaining":"25"},{"Company":"ABC","Focus":"Improve","Completed":"50","Remaining":"25"},{"Company":"XYZ","Focus":"Operate","Completed":"50","Remaining":"25"},{"Company":"XYZ","Focus":"Operate","Completed":"50","Remaining":"25"},{"Company":"XYZ","Focus":"Optimize","Completed":"20","Remaining":"50"}]';

output

output :: Array inside an array, first array should contain company and inside one more array which will contain my focus area sum of completed and remaining data.

Company: 
   1. ABC --Focus -- 1.Operate  -- Completed :70 ,Remaining:75
                     2.Improve    -- Completed:100 ,Remaining:75
                     3.Optimize -- Completed:50 ,Remaining:25

   2. DEF--Focus-- 1.Operate  -- Completed:50 ,Remaining:25
                   2.Optimize -- Completed:100 ,Remaining:75  

   3. XYZ--Focus-- 1.Innovate  -- Completed:100 ,Remaining:75
                   2.Operate    -- Completed:100 ,Remaining:50
                   3.Optimize -- Completed:20 ,Remaining:50

I have tried below query to achieve this :

  var resultxyz = Enumerable.From(dataString).GroupBy("$.Company", null,
    function (key, g) {
    var resultxyz = {
        z: key,
        totalCompleted: g.Sum("$.Completed"),
        totalRemaining: g.Sum("$.Remaining")

    }
    return resultxyz ;
}).ToArray();

But this is grouping only on Company I have to do on focus also. Any idea or suggestion would be great help.

score 1 · Accepted Answer · answered Dec 14 '15 at 21:51

Basically you're looking for a two-tiered grouping. First an overall grouping of all companies, and within each group, a grouping of foci. There's many different ways this could be modeled, but the most straight forward way:

var query = Enumerable.From(data)
    .GroupBy("$.Company", null,
        function (key, g) {
            return {
                Company: key,
                Result: Enumerable.From(g)
                    .GroupBy("$.Focus", null,
                        "{ Focus: $, Completed: $$.Sum('Number($.Completed)'), Remaining: $$.Sum('Number($.Remaining)') }"
                    )
                    .ToArray()
            };
        }
    )
    .ToArray();

Note the use of the Number() function. Since the Completed and Remaining properties are strings, taking the sum will not quite work as expected. We need to convert them to numbers beforehand.

Thank you so much Jeff this is exactly what i wanted :) :) – Najitha Sidhik Dec 15 '15 at 06:21 — Najitha Sidhik, Dec 15 '15 at 06:21

SUM and GROUP BY on multiple fields of JSON data using Linq.js

1 Answers1