JavaScript SUM and GROUP BY of JSON data

Question

This is my first attempt at doing JavaScript with some JSON data objects and need some advice on the proper way to attain my goal.

Some server-side code actually generates a JSON formatted string that I have to work with and assign it to a string:

var dataString='$DATASTRING$';

But the end-result I have to work with after the server substitutes its data (without the \r\n, of course):

var dataString='[ 
 { "category" : "Search Engines", "hits" : 5, "bytes" : 50189 },
 { "category" : "Content Server", "hits" : 1, "bytes" : 17308 },
 { "category" : "Content Server", "hits" : 1, "bytes" : 47412 },
 { "category" : "Search Engines", "hits" : 1, "bytes" : 7601 },
 { "category" : "Business", "hits" : 1, "bytes" : 2847 },
 { "category" : "Content Server", "hits" : 1, "bytes" : 24210 },
 { "category" : "Internet Services", "hits" : 1, "bytes" : 3690 },
 { "category" : "Search Engines", "hits" : 6, "bytes" : 613036 },
 { "category" : "Search Engines", "hits" : 1, "bytes" : 2858 } 
]';

And then I can change it to an object to work with.

var dataObject=eval("("+dataString+")");

This allows me to access the data individual rows of data, but I need to sum, group by, and order the values.

I need to the equivalent of an SQL statement like this:

SELECT category, sum(hits), sum(bytes) 
FROM dataObject
GROUP BY category
ORDER BY sum(bytes) DESC

My desired output would be an object like this that I can further process:

var aggregatedObject='[ 
 { "category" : "Search Engines", "hits" : 13, "bytes" : 673684 },
 { "category" : "Content Server", "hits" : 3, "bytes" : 88930 },
 { "category" : "Internet Services", "hits" : 1, "bytes" : 3690 },
 { "category" : "Business", "hits" : 1, "bytes" : 2847 } 
]';

...but i don't know where to start.

I could loop through all the category values and find the unique categories first, then loop again and sum the hits and bytes, then again to sort, but it seems there has got to be an easier way.

prototype.js (1.7) is already included on the client page, but I could add Underscore, jQuery, or some other small library if I had to.

I just don't know what would be best, easiest, smallest with the least amount of code to process the query.

Any suggestions?

Answer 1

You can use the native functions .reduce() to aggregrate the data, and then .sort() to sort by bytes.

var result = dataObject.reduce(function(res, obj) {
    if (!(obj.category in res))
        res.__array.push(res[obj.category] = obj);
    else {
        res[obj.category].hits += obj.hits;
        res[obj.category].bytes += obj.bytes;
    }
    return res;
}, {__array:[]}).__array
                .sort(function(a,b) { return b.bytes - a.bytes; });

If you're supporting older implementations, you'll need to use a shim for .reduce().

Answer 2

If you go the LINQ.js route, you can do it like this:

var aggregatedObject = Enumerable.From(dataArray)
    .GroupBy("$.category", null, function (key, g) {
        return {
          category: key,
          hits: g.Sum("$.hits"),
          bytes: g.Sum("$.bytes")
        }
    })
    .ToArray();

Working demo with Stack Snippets:

var dataArray = [ 
  { category: "Search Engines", hits: 5, bytes: 50189 },
  { category: "Content Server", hits: 1, bytes: 17308 },
  { category: "Content Server", hits: 1, bytes: 47412 },
  { category: "Search Engines", hits: 1, bytes: 7601  },
  { category: "Business",       hits: 1, bytes: 2847  },
  { category: "Content Server", hits: 1, bytes: 24210 },
  { category: "Internet ",      hits: 1, bytes: 3690  },
  { category: "Search Engines", hits: 6, bytes: 613036 },
  { category: "Search Engines", hits: 1, bytes: 2858  } 
];

var aggregatedObject = Enumerable.From(dataArray)
    .GroupBy("$.category", null, function (key, g) {
        return {
          category: key,
          hits: g.Sum("$.hits"),
          bytes: g.Sum("$.bytes")
        }
    })
    .ToArray();

console.log(aggregatedObject);

<script src="//cdnjs.cloudflare.com/ajax/libs/linq.js/2.2.0.2/linq.min.js"></script>

Further Reading: linqjs group by with a sum

Answer 3

Given the dataString above, the below code seems to work. It goes through each object; if the category exists in the groupedObjects array, its hits and bytes are added to the existing object. Otherwise, it is considered new and added to the groupedObjects array.

This solution makes use of underscore.js and jQuery

Here's a jsfiddle demo: http://jsfiddle.net/R3p4c/2/

var objects = $.parseJSON(dataString);
var categories = new Array();
var groupedObjects = new Array();
var i = 0;

_.each(objects,function(obj){
    var existingObj;
    if($.inArray(obj.category,categories) >= 0) {
        existingObj = _.find(objects,function(o){return o.category === obj.category; });
        existingObj.hits += obj.hits;
        existingObj.bytes += obj.bytes;
    } else {
        groupedObjects[i] = obj;
        categories[i] = obj.category;
        i++;
  }
});

groupedObjects = _.sortBy(groupedObjects,function(obj){ return obj.bytes; }).reverse();

Answer 4

var obj = [{Poz:'F1',Cap:10},{Poz:'F1',Cap:5},{Poz:'F1',Cap:5},{Poz:'F2',Cap:20},{Poz:'F1',Cap:5},{Poz:'F1',Cap:15},{Poz:'F2',Cap:5},{Poz:'F3',Cap:5},{Poz:'F4',Cap:5},{Poz:'F1',Cap:5}];
Array.prototype.sumUnic = function(name, sumName){
    var returnArr = [];
    var obj = this;
    for(var x = 0; x<obj.length; x++){
        if((function(source){
            if(returnArr.length == 0){
                return true;
            }else{
                for(var y = 0; y<returnArr.length; y++){
                    var isThere = [];
                    if(returnArr[y][name] == source[name]){
                        returnArr[y][sumName] = parseInt(returnArr[y][sumName]) + parseInt(source[sumName]);
                        return false;
                    }else{
                        isThere.push(source);
                    }
                }
                if(isThere.length>0)returnArr.push(source);
                return false;
            }
        })(obj[x])){
            returnArr.push(obj[x]);
        }
    }
    return returnArr;
}
obj.sumUnic('Poz','Cap');
// return "[{"Poz":"F1","Cap":45},{"Poz":"F2","Cap":25},{"Poz":"F3","Cap":5},{"Poz":"F4","Cap":5}]"

Answer 5

Hi here is one solution written by me Visit: aggregate_groupby_js on npm or in aggregate_groupby_js on github

The javascript library for using aggregate functions on array of objects. Basic functions like SUM, MIN, MAX, AVG, DISTINCT_COUNT for entire javascript objects

Example:

var arr = [{`"shape"`:`"square"`,`"color"`:`"red"`,`"used"`:1,`"instances"`:1},
    {`"shape"`:`"square"`,`"color"`:`"red"`,`"used"`:2,`"instances"`:1},
    {`"shape"`:`"circle"`,`"color"`:`"blue"`,`"used"`:0,`"instances"`:0},
    {`"shape"`:`"square"`,`"color"`:`"blue"`,`"used"`:4,`"instances"`:4},
    {`"shape"`:`"circle"`,`"color"`:`"red"`,"`used"`:1,`"instances"`:1},
    {`"shape"`:`"circle"`,`"color"`:`"red"`,`"used"`:1,`"instances"`:0},
    {`"shape"`:`"square"`,`"color"`:`"blue"`,`"used"`:4,`"instances"`:5}, 
    {`"shape"`:`"square"`,`"color"`:`"red"`,`"used"`:2,`"instances"`:1}];

// Specify columns
    var columns =[`"used"`, `"instances"`];

// Initialize object
    var gb = new GroupBy(arr,columns);
// or
    var gb = new GroupBy(arr,[`"used"`, `"instances"`]);

// Call the aggregate functions    
    gb.sum();
    gb.min();
    gb.max();
    gb.avg();
    gb.distinctCount();