Grouping and aggregation on a JSON array using Underscore JS

Question

I have a json array object. I want to group data based on the "FlexCategoryName" column and aggregate on matrix columns such as the average cost, the sum of the clicks, etc. Below is an array of JSON objects

Array[100] 0: Object 1: Object 2: Object 3: Object 

0: Object

 Clicks: 1067 Cost: 4094.2 Date: "2/8/2014" FlexCategoryName: "Cat1" Impressions: 65943 Leads: 20 

VENUE_ID: 1

1: Object

 Clicks: 106 Cost: 409 Date: "2/8/2014" FlexCategoryName: "Cat2" Impressions: 65941 Leads: 21 VENUE_ID:1 

2: Object

 Clicks: 106 Cost: 409 Date: "2/8/2014" FlexCategoryName: "Cat2" Impressions: 10 Leads: 21 VENUE_ID:2 

3: Object

 Clicks: 106 Cost: 409 Date: "2/8/2014" FlexCategoryName: "Cat3" Impressions: 10 Leads: 21 VENUE_ID:2 

4: Object

 Clicks: 20 Cost: 10 Date: "2/8/2014" FlexCategoryName: "Cat5" Impressions: 10 Leads: 21 VENUE_ID:3 

How to use Underscore JS to perform grouping and aggregation on matrix columns.

Expected Result

 0: Object Cost: sum of all cost based on FlexCategoryName Cat1 FlexCategoryName: "Cat1" Impressions: sum of all Impressions based on FlexCategoryName Cat1 1: Object Cost: sum of all cost based on FlexCategoryName Cat2 FlexCategoryName: "Cat2" Impressions: sum of all Impressions based on FlexCategoryName Cat2 

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

Exit

 [ { 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 averages instead of sums, just change the function.

Edit: If you want to group by several fields, you can nest this grouping as follows:

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