JavaScript SUM and GROUP BY of JSON data

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

December 13, 2014

KyleMit

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>

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