Group by and aggregation on JSON array using Underscore JS

Question

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 . Below are JSON array Object

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 group by and aggregation on matrix columns .

Expected Output

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

thefourtheye · Accepted Answer · 2014-04-10 06:45:22Z

27

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

edited Apr 10, 2014 at 6:45

answered Apr 9, 2014 at 6:30

thefourtheye

241k53 gold badges466 silver badges505 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shailesh Over a year ago

. I am trying to do groupby on Two Column . Ex .groupBy("FlexCategoryName").groupBy("VENUE_ID") But it always showing group by based on FlexCategoryName. I also tried .groupBy("FlexCategoryName VENUE_ID") and .groupBy("FlexCategoryName ,"VENUE_ID") But its not working

Shailesh Over a year ago

Edited question . I want perform same operation as we do in sql server for grouby on more than 1 column

thefourtheye Over a year ago

@Shailesh Nope, this changes the original meaning of the question and renders my answer useless. I am reverting your changes. Please edit properly again

Shailesh Over a year ago

Can i make grouping based on n level columns. because i can have 1,2 ,3 ..n number of columns for grouping .I am digging your code .

thefourtheye Over a year ago

@Shailesh I think it deserves a separate question.

Collectives™ on Stack Overflow

Group by and aggregation on JSON array using Underscore JS

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related