Find upcoming documents based on subdocument date

user_2071807 0
Viewed 85

Suppose I have some MongoDB Event documents, each of which has a number of sessions which take place on different dates. We might represent this as:

db.events.insert([
  {
    _id: '5be9860fcb16d525543cafe1',
    name: 'Past',
    host: '5be9860fcb16d525543daff1',
    sessions: [
      { date: new Date(Date.now() - 1e8 ) },
      { date: new Date(Date.now() + 1e8 ) }
    ]
  }, {
    _id: '5be9860fcb16d525543cafe2',
    name: 'Future',
    host: '5be9860fcb16d525543daff2',
    sessions: [
      { date: new Date(Date.now() + 2e8) },
      { date: new Date(Date.now() + 3e8) }
    ]
  }
]);

I'd like to find all Events which have not yet had their first session. So I'd like to find 'Future' but not 'Past'.

At the moment I'm using Mongoose and Express to do:

  Event.aggregate([
    { $unwind: '$sessions' }, {
      $group: {
        _id: '$_id',
        startDate: { $min: '$sessions.date' }
      }
    },
    { $sort:{ startDate: 1 } }, {
      $match: { startDate: { $gte: new Date() } }
    }
  ])
    .then(result => Event.find({ _id: result.map(result => result._id) }))
    .then(event => Event.populate(events, 'host'))
    .then(events => res.json(events))

But I feel like I'm making heavy weather of this. Two hits on the database (three if you include the populate statement) and a big, complicated aggregate statement.

Is there a simpler way to do this? Ideally one which only involves one trip to the database.

mongoose aggregation-framework express node.js mongodb
3 Answers

You could use $reduce to fold the array and find if any of of the elements have a past session.

To illustrate this, consider running the following aggregate pipeline:

db.events.aggregate([
    { "$match": { "sessions.date": { "$gte": new Date() } } },
    { "$addFields": {
        "hasPastSession": { 
            "$reduce": {
                "input": "$sessions.date",
                "initialValue": false,
                "in": { 
                    "$or" : [
                        "$$value", 
                        { "$lt": ["$$this", new Date()] }
                     ] 
                 }
            }
       }
    } },
    //{ "$match": { "hasPastSession": false } }
])

Based on the above sample, this will yield the following documents with the extra field 

/* 1 */
{
    "_id" : "5be9860fcb16d525543cafe1",
    "name" : "Past",
    "host" : "5be9860fcb16d525543daff1",
    "sessions" : [ 
        {
            "date" : ISODate("2019-01-03T12:04:36.174Z")
        }, 
        {
            "date" : ISODate("2019-01-05T19:37:56.174Z")
        }
    ],
    "hasPastSession" : true
}

/* 2 */
{
    "_id" : "5be9860fcb16d525543cafe2",
    "name" : "Future",
    "host" : "5be9860fcb16d525543daff2",
    "sessions" : [ 
        {
            "date" : ISODate("2019-01-06T23:24:36.174Z")
        }, 
        {
            "date" : ISODate("2019-01-08T03:11:16.174Z")
        }
    ],
    "hasPastSession" : false
}

Armed with this aggregate pipeline, you can then leverage $expr and use the pipeline expression as your query in the find() method (or using the aggregate operation above but with the $match pipeline step at the end enabled) as

db.events.find(
    { "$expr": {
        "$eq": [
            false,
            { "$reduce": {
                "input": "$sessions.date",
                "initialValue": false,
                "in": { 
                    "$or" : [
                        "$$value", 
                        { "$lt": ["$$this", new Date()] }
                     ] 
                 }
           } }
        ]
    } }
)

which returns the document

{
    "_id" : "5be9860fcb16d525543cafe2",
    "name" : "Future",
    "host" : "5be9860fcb16d525543daff2",
    "sessions" : [ 
        {
            "date" : ISODate("2019-01-06T23:24:36.174Z")
        }, 
        {
            "date" : ISODate("2019-01-08T03:11:16.174Z")
        }
    ]
}
user_1220050

You don't need to use $unwind and $group to find the $min date from the array. You can directly use $min to extract the min date from the session array and then use $lookup to populate the host key

db.events.aggregate([
  { "$match": { "sessions.date": { "$gte": new Date() }}},
  { "$addFields": { "startDate": { "$min": "$sessions.date" }}},
  { "$match": { "startDate": { "$gte": new Date() }}},
  { "$lookup": {
    "from": "host",
    "localField": "host",
    "foreignField": "_id",
    "as": "host"
  }},
  { "$unwind": "$host" }
])
user_75106570

Is it possible you can just reach into the sessions of each event, and pull back each event where all session dates are only in the future? Something like this? Might need tweaking.. 

db.getCollection("events").aggregate(
    [

        {$match:{'$and':
               [
                   {'sessions.date':{'$gt': new Date()}}, 
                   {'sessions.date':{ '$not': {'$lt': new Date()}}} 
               ]
             }}
    ]
);
user_108295700
Related