Retrieve documents which contain distinct element in array

Question

I have documents like

[{"_id" : "12","name" : "test1", "nesArray" : [1,2], "createdAt" : ISODate("2016-10-15T06:17:53.946Z") },
{"_id" : "12","name" : "test1", "nesArray" : [1,3], "createdAt" :ISODate("2016-10-15T06:17:54.946Z") },
{"_id" : "12","name" : "test1", "nesArray" : [1,4], "createdAt" :ISODate("2016-10-15T06:17:53.946Z") },
{"_id" : "12","name" : "test1", "nesArray" : [1,2], "createdAt" :ISODate("2016-10-15T06:18:53.946Z") },
{"_id" : "12","name" : "test1", "nesArray" : [1,3], "createdAt" :ISODate("2016-10-15T06:18:53.946Z") },
{"_id" : "12","name" : "test1", "nesArray" : [1,2],"createdAt" :ISODate("2016-10-15T06:19:53.946Z") } ]

and I want to retrieve only those document which have

Distinct nesArray
Only latest document

Result should be like

   [{"_id" : "12","name" : "test1", "nesArray" : [1,4], "createdAt" :ISODate("2016-10-15T06:17:53.946Z") },
    {"_id" : "12","name" : "test1", "nesArray" : [1,3], "createdAt" :ISODate("2016-10-15T06:18:53.946Z") },
    {"_id" : "12","name" : "test1", "nesArray" : [1,2],"createdAt" :ISODate("2016-10-15T06:19:53.946Z") }]

I tried it with aggregation but I don't want to use aggregation(Mongoose populate is not work with aggregation).

@sidgate I tried this with aggregation 1. sort element by createdAt 2. group document by "nesArray" 3. retrieve latest document of every group by $last. — Rishabh Garg, Oct 19 '16 at 18:12

Marcelo Risse · Answer 1 · 2016-10-19T18:35:21.263

0

It's basically what you tried, but getting all fields using $last and projecting after to have same structure as in DB:

db.coll.aggregate(
  { 
    $sort: { createdAt: 1 }
  }, 
  {
    $group: {
      _id: "$nesArray", 
      id: { $last: "$_id" }, 
      name: {$last: "$name"}, 
      createdAt: { $last: "$createdAt"}
    }
  },
  {
    $project: {
      _id: "$id",
      nesArray: "$_id",
      name: 1,
      createdAt: 1
    }
  }
)

edited Oct 19 '16 at 18:35

answered Oct 19 '16 at 18:25

Marcelo Risse

514
3
6

I already tried this I need help to get it with aggregation so that I can use populate(Mongoose). – Rishabh Garg Oct 19 '16 at 18:33
It's using aggregate. Use populate on the aggregation results, `Model.populate(results, ...)` – Marcelo Risse Oct 19 '16 at 18:40
Can you give an example? – Rishabh Garg Oct 19 '16 at 18:43

Retrieve documents which contain distinct element in array

1 Answers1