Group By And Aggregation On JSON Array Using Underscore JS

I have a json array object . I want to group by data based on column 'FlexCategoryName' and perform aggregation on matrix columns such as avg of 'cost' ,sum of 'clicks' etc . Belo

Solution 1:

var _ = require("underscore");
function sum(numbers) {
    return _.reduce(numbers, function(result, current) {
        return result + parseFloat(current);
    }, 0);
}
var result = _.chain(data)
    .groupBy("FlexCategoryName")
    .map(function(value, key) {
        return {
            FlexCategoryName: key,
            Cost: sum(_.pluck(value, "Cost")),
            Impressions: sum(_.pluck(value, "Impressions"))
        }
    })
    .value();

console.log(result);

Output

[ { FlexCategoryName: 'Cat1', Cost: 4094.2, Impressions: 65943 },
  { FlexCategoryName: 'Cat2', Cost: 818, Impressions: 65951 },
  { FlexCategoryName: 'Cat3', Cost: 409, Impressions: 10 },
  { FlexCategoryName: 'Cat5', Cost: 10, Impressions: 10 } ]

If you want to find average values instead of sum, then just change the function.

Edit: If you want to group based on more than one fields, you can nest the grouping like this

var result = _.chain(data)
    .groupBy("FlexCategoryName")
    .map(function(value, key) {
        return _.chain(value)
            .groupBy("VENUE_ID")
            .map(function(value1, venue_id) {
                return {
                    FlexCategoryName: key,
                    Cost: sum(_.pluck(value1, "Cost")),
                    Impressions: sum(_.pluck(value1, "Impressions")),
                    VENUE_ID: venue_id
                }
            })
            .value();
    })
    .value();