Mongodb Query on a field of a last document in a nested Array

Question

I come from SQL world, and still learning to query MongoDB, and I faced this problem...

Setup:

I have a Student collection which has an array of Meetings

student: {
  meetings: [
    {time: x},
    {time: y},
    {time: z}
  ]
}

meetings array has a dynamic size, and its elements are sorted by time in asc, so the first meeting in the array would have the earliest time.

I am able to query to get all students whose first meeting begins after certain time by:

db.students.find( { "meetings.0.time": {$gt: ISODate()} } )

Question:

Now I also need to query to get all students whose LAST meeting begins before certain time. However, the following didn't work:

db.students.find( { "meetings.-1.time": {$lt: ISODate()} } )

How would you guys solve this problem?

Can't answer properly from phone. Buy you will be able to do this using the aggregation framework. Have a look on mongodb docs. — sambomartin
– sambomartin, Commented Mar 14, 2013 at 23:25
If the last time is less than the date then all the times in the array would be less than that date. maybe meets.forEach.time would work? — Four_lo
– Four_lo, Commented Mar 14, 2013 at 23:37
@sambomartin Great! Good to hear that this is possible. I'll check out the framework. — haejeong87
– haejeong87, Commented Mar 15, 2013 at 5:12
You could use aggregate command. $unwind the array and then $group to get the meeting for the max time per student. You could either $limit before Or after the group depending on what you want to return — sambomartin
– sambomartin, Commented Mar 16, 2013 at 12:16

sambomartin · Accepted Answer · 2013-03-19 23:16:56Z

1

Does this work for you?

db.student.aggregate(
  { $unwind : "$meetings" },
  { $group : { _id: "$_id", lastMeeting : { $max : "$meetings.time" }}},
  { $match: { lastMeeting : { $lt : IsoDate(...) } } 
);

answered Mar 19, 2013 at 23:16

sambomartin

6,8237 gold badges43 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

haejeong87 Over a year ago

How would you change this to actually select the last element in the array, instead of using $max?

sambomartin Over a year ago

that's not possible (yet) using the aggregation framework. There are a few jira tickets relating to this jira.mongodb.org/browse/SERVER-4589 and i think this would be useful jira.mongodb.org/browse/SERVER-6074. Alternative methods I've seen would be to hold a sequence / order as an attribute in each element (meeting) - there are examples of creating a sequence on mongodb examples. If you're happy to do some processing in app you could use $slice docs.mongodb.org/manual/reference/projection/slice. Another technique would be to hold a "lastMeeting" copy next to the array

sambomartin Over a year ago

You could also use an ObjectId for each meeting too - this is unique and sequential**

Collectives™ on Stack Overflow

Mongodb Query on a field of a last document in a nested Array

Setup:

Question:

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Setup:

Question:

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related