how to group in mongoDB and return all fields in result

Question

I am using aggregate method in mongoDB to group but when I use $group it returns the only field which I used to group. I have tried $project but it is not working either. I also tried $first and it worked but the result data is now in different format.

The response format I need looks like:

{
    "_id" : ObjectId("5b814b2852d47e00514d6a09"),
    "tags" : [],
    "name" : "name here",
    "rating" : "123456789"
}

and after adding $group in my query.response is like this, the value of _id changes. (and the $group is taking only _id, if i try any other keyword it throws an error of accumulator something. please explain this also.)

{
    "_id" :"name here" //the value of _id changed to the name field which i used in $group condition
}

I have to remove the duplicates in name field, without changing any structure and fields. also I am using nodeJS with mongoose, so please provide the solution that works with it.

i simply want to remove the duplicates. assume i receive 50 documents, 20 of them has same name field, which i want only once. ( eg {{"name" : "name1","rating":"123456"},{"name" : "name2","rating":"123456.004"},{"name" : "name1","rating":"12345614.23"}}). i dont want 3rd document as it has same name as 1st and it is duplicate. what should i do in this case? — Anukool, Sep 29 '18 at 09:56
Use [`$first`](https://docs.mongodb.com/manual/reference/operator/aggregation/first/) aggregation to get other fields. — Ashh, Sep 29 '18 at 10:00
problem is when i use group, the value of id changes,i dont want that. — Anukool, Sep 29 '18 at 10:24
eg. {{"_id":ObjectId("123asdf),"name" : "name1","rating":"123456"},{"_id":ObjectId("1234asd),"name" : "name2","rating":"123456.004"},{"_id":ObjectId("12345as"),"name" : "name1","rating":"12345614.23"}} using group returns this response-> {{"_id" : "name1"},{"_id" : "name2"}}. i need the response same as previous, but no dupliacates. — Anukool, Sep 29 '18 at 10:30
this are documents of my collection. { "_id" : ObjectId("5b814c9752d45"), "rating" : "0.1", "tags" : "", "name" : "name1" }, { "_id" : ObjectId("5b814c9752d44"), "rating" : "0.2", "tags" : null, "name" : "name2" }, { "_id" : ObjectId("5b814c9752d43"), "rating" : "0.3", "tags" : "", "name" : "name3" }, { "_id" : ObjectId("5b814c9752d42"), "rating" : "0.4", "tags" : "", "name" : "name2" }, { "_id" : ObjectId("5b814c9752d41"), "rating" : "0.5", "tags" : "", "name" : "name1" } please find output in next comment. characters limited.... — Anukool, Sep 29 '18 at 10:48
output ... { "_id" : ObjectId("5b814c9752d45"), "rating" : "0.1", "tags" : "", "name" : "name1" }, { "_id" : ObjectId("5b814c9752d44"), "rating" : "0.2", "tags" : "", "name" : "name2" }, { "_id" : ObjectId("5b814c9752d43"), "rating" : "0.3", "tags" : "", "name" : "name3" } — Anukool, Sep 29 '18 at 10:49

score 89 · Accepted Answer · answered Sep 30 '18 at 13:48

89

You can use below aggregation query.

$$ROOT to keep the whole document per each name followed by $replaceRoot to promote the document to the top.

db.col.aggregate([
  {"$group":{"_id":"$name","doc":{"$first":"$$ROOT"}}},
  {"$replaceRoot":{"newRoot":"$doc"}}
])

answered Sep 30 '18 at 13:48

s7vr

73,656
11
106
127

Thank you so much, this question really helps me a lot. – Habib Sep 22 '19 at 13:08
This is the short and best answer ! – dd619 Nov 12 '19 at 14:15
pls help if you can for related questions in mongoDB - https://stackoverflow.com/questions/61067856/calculate-dwell-time-between-2-statuses-of-a-field – newdeveloper Apr 07 '20 at 01:49
its working but converting my array to objects – Ashish Saini May 12 '22 at 12:11

Sathish Kumar · Answer 2 · 2020-04-23T06:20:09.320

user2683814's solution worked for me but in my case, I have a counter accumulator when we replace the newRoot object, the count field is missing in the final stage so I've used $mergeObjects operator to get my count field back.

db.collection.aggregate([
 {
  $group: {
    _id: '$product',
    detail: { $first: '$$ROOT' },
    count: {
      $sum: 1,
    },
  },
},
{
  $replaceRoot: {
    newRoot: { $mergeObjects: [{ count: '$count' }, '$detail'] },
  },
}])

Adnan Ahmed Ansari · Answer 3 · 2018-09-29T10:45:34.273

4

When you group data on any database, it means you want to perform accumulated operation on the required field and the other field which will not be include in accumulated operation will be used in group like

 db.collection.aggregate([{
 $group: {
   _id: { field1: "", field1: "" },
   acc: { $sum: 1 }
 }}]

here in _id object will contains all other fields which you want to hold.

for your data you can try this

db.collection.aggregate([{
    $group: {
        _id: "$name",
        rating: { $first: "$rating" },
        tags: { $first: "$tag" },
        docid: { $first: "$_id" }
    }
},
{
    $project: {
        _id: "$docid",
        name: "$_id",
        rating: 1,
        tags: 1
    }
}])

edited Sep 29 '18 at 10:45

answered Sep 29 '18 at 09:43

Adnan Ahmed Ansari

304
4
15

i simply want to remove the duplicates. assume i receive 50 documents, 20 of them has same name field, which i want only once. ( eg {{"name" : "name1","rating":"123456"},{"name" : "name2","rating":"123456.004"},{"name" : "name1","rating":"12345614.23"}}). i dont want 3rd document as it has same name as 1st and it is duplicate. what should i do in this case? – Anukool Sep 29 '18 at 09:49
You have different ratings against same name so which one you want to get with group or do you want to all ratings against one name? – Adnan Ahmed Ansari Sep 29 '18 at 10:11
which ever comes first, the rating is not that important, important thing is that documents must have distinct values of name field. – Anukool Sep 29 '18 at 10:19
now i am getting "The field 'name' must be an accumulator object" error. if i replace name with _id, it works but it will change the _id value. {"_id" : "name1","rating" : "0.001","tags" : null,"id" : ObjectId("5b814c9752d47e00514e9fcf")}.. the value of _id is changing which i dont want to. the actual value of _id is receiving in "id" field. – Anukool Sep 29 '18 at 10:39
oh sorry my mistake you are right you will use _id for name then use projection. I'm updating my query – Adnan Ahmed Ansari Sep 29 '18 at 10:42
by the way is there any way to get all fields without passing all fields. ? i have 30 fields in document. – Anukool Sep 29 '18 at 11:50
Yes Senthur Deva query is usefull for all fields you can see him answer – Adnan Ahmed Ansari Sep 29 '18 at 15:38

score 2 · Answer 4 · answered Sep 29 '18 at 10:47

You can use this query

db.col.aggregate([
                        {"$group" : {"_id" : "$name","data" : {"$first" : "$$ROOT"}}},
                        {"$project" : {
                            "tags" : "$data.tags",
                            "name" : "$data.name",
                            "rating" : "$data.rating",
                            "_id" : "$data._id"
                            }
                        }])

score 2 · Answer 5 · edited Aug 06 '20 at 07:53

2

I wanted to group my collection by groupById field and store it as key value pairs having key as groupById and value as all the items of that group.

db.col.aggregate([{$group :{_id :"$groupById",newfieldname:{$push:"$"}}}]).pretty()

This is working fine for me..

edited Aug 06 '20 at 07:53

Roberto Caboni

7,252
10
25
39

answered Aug 06 '20 at 07:22

Krishna Mistry

41
5

how to group in mongoDB and return all fields in result

5 Answers5

Linked