summation of two columns in Aggregate Method

Question

I am using mongodb Aggregate query. My db is like this:

{
  "_id" : ObjectId("5a81636f017e441d609283cc"),
  "userid": "123",
  page : 'A',
  newpage: 'A',
},
{
  "_id" : ObjectId("5a81636f017e441d609283cd"),
  "userid": "123",
  page : 'B',
  newpage: 'A',
},
{
  "_id" : ObjectId("5a81636f017e441d609283ce"),
  "userid": "123",
  page : 'D',
  newpage: 'D',
}

I want to get the Sum of all page and new page value. I am able to get one column value which can give the very precise result.

But I am stuck with the two columns. What I did for getting the sum/repetition of values for one column is:

db.Collection.aggregate([
                    {$match:{ "userid":"123"}},
                    {$unwind:"$newpage"},
                    {$group:{"_id":"$newpage", "count":{"$sum":1}}}, 
                    {$project: {_id:0, pagename :"$_id", count:{ $multiply: [ "$count", 1 ] }}},
                    {$sort: {count: -1}},
                    //{$limit: 10}              
               ], function(error, data){
                   if (error) {
                       console.log(error);
                   } else {
                    console.log(data);
        }
    });

Desired Result will be like:

    {
        "pagename": "A",
        "count": 3
    },
    {
        "pagename": "D",
        "count": 2
    },
    {
        "pagename": "B",
        "count": 1
    }

Is anyone has any approach to getting these things for Two Column? Any Help is appreciated

What's your MongoDB Server version? If it is 3.4 or newer then $facet allows you to process multiple aggregation pipelines within a single stage on the same set of input documents. — chridam
– chridam, Commented Feb 15, 2018 at 10:01

chridam · Accepted Answer · 2018-02-15 10:38:02Z

1

Use $facet pipeline stage to process multiple aggregation pipelines within a single stage on the same set of input documents. In your case you need to aggregate the counts separately then join the two results and calculate the final aggregates.

This can be demonstrated by running the following pipeline:

db.collection.aggregate([
    { "$match": { "userid": "123" } },
    {
        "$facet": {
            "groupByPage": [
                { "$unwind": "$page" },
                { 
                    "$group": {
                        "_id": "$page",
                        "count": { "$sum": 1 }
                    }
                }
            ],   
            "groupByNewPage": [
                { "$unwind": "$newpage" },
                { 
                    "$group": {
                        "_id": "$newpage",
                        "count": { "$sum": 1 }
                    }
                }
            ]
        }
    },
    { 
        "$project": {
            "pages": {
                "$concatArrays": ["$groupByPage", "$groupByNewPage"]
            }
        }
    },
    { "$unwind": "$pages" },
    { 
        "$group": {
            "_id": "$pages._id",
            "count": { "$sum": "$pages.count" }
        }
    },
    { "$sort": { "count": -1 } }
], function(error, data){
    if (error) {
        console.log(error);
    } else {
        console.log(data);
    }
)

edited Feb 15, 2018 at 10:38

answered Feb 15, 2018 at 10:16

chridam

104k26 gold badges246 silver badges243 bronze badges

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

2 Comments

dnickless Over a year ago

Two issues here: a) The first $unwind stages are not needed. b) The $setUnion will remove duplicates which you don't want.

chridam Over a year ago

If you look closely at the OP's current working pipeline, there is an $unwind stage which shows the original document has newpage or page as arrays otherwise the pipeline would have thrown an error. You are correct about the $setUnion though, edited.

dnickless · Accepted Answer · 2018-02-15 10:28:59Z

0

There you go:

db.Collection.aggregate([
    {$match:{ "userid":"123"}}, // filter out what's not of interest
    {$facet: { // process two stages in parallel --> this will give us a single result document with the following two fields
        "newpage": [ // "newpage" holding the ids and sums per "newpage" field
            {$group:{"_id":"$newpage", "count":{"$sum":1}}}
        ],
        "page": [ // and "page" holding the ids and sums per "page" field
            {$group:{"_id":"$page", "count":{"$sum":1}}}
        ]
    }},
    {$project: {x:{$concatArrays:["$newpage", "$page"]}}}, // merge the two arrays into one
    {$unwind: "$x"}, // flatten the single result document into multiple ones so we do not need to $reduce but can nicely $group
    {$group: {_id: "$x._id", "count": {$sum: "$x.count"}}} // perform the final grouping/counting,
    {$sort: {count: -1}} // well, the sort according to your question
]);

edited Feb 15, 2018 at 10:28

answered Feb 15, 2018 at 10:23

dnickless

10.9k1 gold badge22 silver badges35 bronze badges

1 Comment

Ankit Over a year ago

Thanks for sharing your approach. Really appreciate

Collectives™ on Stack Overflow

summation of two columns in Aggregate Method

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related