CODEWITHSUNDEEP
mongodbmongoose
codeofnode
codeofnode

Reputation: 18629

find all the documents that is the latest by a field and distinct by another field in mongodb collection

I want to extract the whole document to group.

As an example, I have a collection named "sub_comment".

Each "sub_comment" has a ref to one of the "comment" as "commentId".

sub_comment = new Schema({  // as per mongoose schema
   updatedAt : Date,
   text : String,
   by : String,
   info : String,
   commentId : { type : ObjectId, ref : 'commment' }
})

Now I want to extract all those subcomments (full document) that are latest (by means of "updatedAt") grouped by each comment.

I am currently doing this :

mongoose.model('sub_comment').aggregate([
            { $sort: { commentId : 1, updatedAt: -1 } },
            { $group: { _id: "$commentId", updatedAt: { $first: "$updatedAt" } } }
          ]

This, of course, return only two fields that is updatedAt and commentId.

Now what if I want the whole document. I will have to write each field separately, that I do not want to do.

What should be the preferred approach?

EDIT : the simple query

collection =

[
{commentId : 1, updatedAt : ISO("2015-10-13T11:38:29.000Z"), xyz  : 'xyz1' },
{commentId : 2, updatedAt : ISO("2015-10-10T11:38:29.000Z"), xyz  : 'xyz2'  },
{commentId : 2, updatedAt : ISO("2015-10-14T11:38:29.000Z"), xyz  : 'xyz3'  },
{commentId : 1, updatedAt : ISO("2015-10-11T11:38:29.000Z"), xyz  : 'xyz4'  }
]

i want output :

[
{commentId : 1, updatedAt : ISO("2015-10-13T11:38:29.000Z"), 'xyz' : 'xyz1' },
{commentId : 2, updatedAt : ISO("2015-10-14T11:38:29.000Z"), 'xyz' : 'xyz3'  }
]

Upvotes: 3

Views: 714

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 312095

You can use the $$ROOT system variable in an aggregation to refer to the complete top-level document at that stage in the pipeline:

mongoose.model('sub_comment').aggregate([
        { $sort: { commentId : 1, updatedAt: -1 } },
        { $group: { _id: "$commentId", doc: { $first: "$$ROOT" } } }
      ]

Upvotes: 4

Related Questions