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?

LINQ to JS might not have everything you need, but it might be helpful for you — MilkyWayJoe, Jun 26 '12 at 1:33
Not bad. Good place to start looking, but still needs jQuery for the aggrgate functions. A little more than I wanted. could come in handy for another project I have in mind. Thanks — LordChariot, Jun 26 '12 at 2:59

squint · Accepted Answer

up vote 12 down vote accepted

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().

answered Jun 26 '12 at 2:05

community wiki

squint

Beautiful. Does exactly what I want and only needs a small shim for reduce. (even works in IE6 :) I think that was the key I was missing because I saw .reduce referred to but could never get it to work. seems to have the lowest overhead and does what I need. Awesome. – LordChariot Jun 26 '12 at 3:09

hmmm. Now I wonder if there's an easy way to graph this? I was just going to do a text table, but maybe a chart for the bytes or the hits. Any good graphing routines out there? – LordChariot Jun 26 '12 at 3:15

@LordChariot: Google will likely show you quite a few nice libraries for graphing. Give a few a try first, and then ask a question if you get stuck on something. :) – squint Jun 26 '12 at 14:51

This works inside CouchDB :) – Pete Jun 8 at 15:30

add a comment

KyleMit · Answer 2 · 2014-12-13 05:55:05Z

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:

Also, you can find more info on linqjs group by with a sum

jackwanders · Answer 3 · 2012-06-26 02:00:25Z

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();

Yes. Does exactly what I wanted it to do. I put in the actual full dataset I receive (1000+ array elements) into jsfiddle and it came out with the correct values. Downside is it needs both Underscore and jQuery. I was hoping to reduce the amount of baggage I had to include. (Can't hot link to external because it's likely on a closed network) But overall i love the flexibility for some other stuff i might be doing soon. — LordChariot, Jun 26 '12 at 3:05

score 0 · Answer 4 · 2014-06-09 22:33:44Z

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}]"

JavaScript SUM and GROUP BY of JSON data

4 Answers

Working demo with Stack Snippets: