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.

Solution 1

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"}}
])

Solution 2

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'] },
  },
}])

Solution 3

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
    }
}])

Solution 4

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"
                            }
                        }])

Solution 5

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..

View more solutions

Comments

[Anukool]

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?

[Adnan Ahmed Ansari]

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?

[Anukool]

which ever comes first, the rating is not that important, important thing is that documents must have distinct values of name field.

[Anukool]

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.

[Adnan Ahmed Ansari]

oh sorry my mistake you are right you will use _id for name then use projection. I'm updating my query

[Anukool]

by the way is there any way to get all fields without passing all fields. ? i have 30 fields in document.

[Adnan Ahmed Ansari]

Yes Senthur Deva query is usefull for all fields you can see him answer

[Habib]

Thank you so much, this question really helps me a lot.

[dd619]

This is the short and best answer !

[newdeveloper]

pls help if you can for related questions in mongoDB - stackoverflow.com/questions/61067856/…

[Ashish Saini]

its working but converting my array to objects