Use $group with find in mongodb node.js

Question

I want to group by user_id from order collection where there are lots of entries but I want to fetch the top 5 users that have made more orders and sum the overall price of particular user.

For example, if I made 10 orders I want to fetch my id and sum of my all orders. I have used below code but its not working:

Order.find({$group : { user_id : "$user_id"}},function(error,fetchAllTopUsers){
    console.log('##################');
      console.log(fetchAllTopUsers);
    })

I have checked this example but they are not using find with $group. I am new in Node.js and MongoDB.

Query and sum all with mongoose

Sample documents

{ user_id : '57c7f4312b3c9771219bd21c', totalprice : 100 },
{ user_id : '57c7f4312b3c9771219bd21c', totalprice : 59 },
{ user_id : '57c7f4312b3c9771219bd21c', totalprice : 26 },
{ user_id : '57c7f4312b3c9771219bd21c', totalprice : 533 },
{ user_id : '57c7f4312b3c9771219bd21c', totalprice : 544 },    
{ user_id : '57efb93fdc78c816a3a15c4a', totalprice : 250 },
{ user_id : '57efb93fdc78c816a3a15c4a', totalprice : 157 },
{ user_id : '57efb93fdc78c816a3a15c4a', totalprice : 149 },
{ user_id : '57efb93fdc78c816a3a15c4a', totalprice : 104 }

I'm expecting an output like below:

{
    user_id : '57c7f4312b3c9771219bd21c',
    finalTotal : 1262
},
{   
    user_id : '57efb93fdc78c816a3a15c4a'
    finalTotal : 660
}

ok, let me add table data. – Pritesh Mahajan Oct 03 '16 at 08:14 — Pritesh Mahajan, Oct 03 '16 at 08:14

chridam · Accepted Answer · 2016-10-03T08:31:58.243

4

The $group operator is only available with the aggregation framework hence you need to run an aggregation pipeline that groups the documents by the user_id field and then sorts the documents by the aggregated finalTotal field using $sort and then get the top 5 documents using $limit.

Follow this pipeline to get the needed result:

Order.aggregate([
    {
        "$group": {
            "_id": "$user_id",
            "finalTotal": { "$sum": "$totalprice" }
        }
    },
    { "$sort": { "finalTotal": -1 } },
    { "$limit": 5 }
]).exec(function(error, fetchAllTopUsers){
    console.log('##################');
    console.log(fetchAllTopUsers);
});

edited Oct 03 '16 at 08:31

answered Oct 03 '16 at 07:55

chridam

100,957
23
236
235

Thank you for your reply. i need to sum total price by user_id. like pritesh make 100 orders and overall price is 15000, Chridam make 3 orders and overall price is 5000 like that – Pritesh Mahajan Oct 03 '16 at 07:57
Above answer is working but below is not working. see my comment one more time. i have edited with example – Pritesh Mahajan Oct 03 '16 at 08:06
Added my data as well – Pritesh Mahajan Oct 03 '16 at 08:25
@PriteshMahajan Thanks, much more clearer. I've updated my answer, that should address your requirements. – chridam Oct 03 '16 at 08:32
this answer is working fine but now i am facing a issue that is for string. in db "totalprice" is string type. so when i get result "finalTotal" with showing always 0. not sum my values. – Pritesh Mahajan Oct 15 '16 at 14:14
You need to first update your collection by converting the fields with the string values to numeric values. Consider the answers in these question [MongoDB: How to change the type of a field?](http://stackoverflow.com/questions/4973095/mongodb-how-to-change-the-type-of-a-field), [MongoDB convert string type to float type](http://stackoverflow.com/questions/37942844/mongodb-convert-string-type-to-float-type/) and [how to convert string to numerical values in mongodb](http://stackoverflow.com/questions/29487351/how-to-convert-string-to-numerical-values-in-mongodb) – chridam Oct 15 '16 at 14:19

score 0 · Answer 2 · answered Jan 21 '21 at 12:35

0

We can use like this

db.getCollection('form_a').aggregate([
    {
        "$group": {
            "_id": "$Status",
            "counter": { "$sum": 1 }
            
        }
    }
])

answered Jan 21 '21 at 12:35

Amit Prajapati

119
1
6

form_a is the collection name – Amit Prajapati Jan 21 '21 at 12:38

Use $group with find in mongodb node.js

2 Answers2