How to query documents by a condition on the subdocument with the latest date

Question

I'm trying to figure out the best way to query documents based on a criteria on the latest subdocument.

So my data might look like this:

[{
  _id: '59bb31efae69726bd5fc9391',
  name: 'Something',
  terms: [
    {
      _id: '58e54f5aad59a6000cdcd590',
      begDate: '2017-06-13T07:00:00.000Z',
      endDate: '2018-01-01T07:59:59.999Z'
    },
    {
      _id: '59bb32765e651d28909ed706',
      begDate: '2018-01-01T08:00:00.000Z',
      endDate: '2019-01-01T07:59:59.999Z'
    }
  ]
}, {
  _id: '59f20ddeef426f6bca3abbf1',
  name: 'Something',
  terms: [
    {
      _id: '59f20e35c8257b5b0f22d2a6',
      begDate: '2018-06-13T07:00:00.000Z',
      endDate: '2019-01-01T07:59:59.999Z'
    },
    {
      _id: '59f20e9394c8108d9db33bf9',
      begDate: '2019-01-01T08:00:00.000Z',
      endDate: '2020-01-01T07:59:59.999Z'
    }
  ]
}]

What I want is to get all documents whose last term's endDate is 2019-01-01T07:59:59.999Z This could be done by either getting the last term in an array, or more reliably sorting terms, and then grabbing the last one.

I can see how I could do this with $where but I know if I can find another way it would be more performant.

I also want to add, whatever I do here would accompany other query parameters. For example:

{
  _id: {
    '$in': [
      ObjectId("591e5e37abddad14afe1b272"),
      ObjectId("591e5e37abddad14afe1b123")
    ]
  }
}

UPDATE:

As noted, this question has a duplicate (which was hard for me to find as the question referenced is difficult to understand). That being said, I'm not only looking for the last in an array but also the most recent (I agree that's not clear in the body of the question). I'm not arguing against the duplicate question reference, but for the sake of making this easier for future readers, you'll find in the accepted answer a clean solution for mongo 3.6+ as well as a reference to another question in the comments which should help if you want to query by date in subdocuments.

Xavier Guihot · Accepted Answer · 2018-05-27T17:43:59.783

Using $expr to perform a 'complex' match and $let to have an intermediate variable storing the last element of arrays found with "$arrayElemAt": [ "$terms", -1 ] in order to compare it to the date in question:

db.collection.find({
  $expr: {
    $let: {
      vars: { "last": { $arrayElemAt: [ "$terms", -1 ] } },
      in: { $eq: [ "$$last.endDate", "2019-01-01T07:59:59.999Z" ] }
    }
  } 
})

which returns with the input you provided the first record.

And, as per your requirements, in order not to exclude the possibility to add additional filters, you can add them using $and:

db.collection.find({
  $and: [
    { $expr: { $let: {
      vars: { "last": { $arrayElemAt: [ "$terms", -1 ] } },
      in: { $eq: [ "$$last.endDate", "2019-01-01T07:59:59.999Z" ] }
    }}},
    { "_id": { $ne: "sss" } } // actually whatever additional filter
  ]
})

Exact same thing can be achieved with an aggregate pipeline, if you wish to perform additional stages with your matching documents:

db.collection.aggregate([
  { $match: {
    $and: [
      { $expr: { $let: {
        vars: { "last": { $arrayElemAt: [ "$terms", -1 ] } },
        in: { $eq: [ "$$last.endDate", "2019-01-01T07:59:59.999Z" ] } 
      }}},
      { "_id": { $ne: "sss" } }
    ]
  }},
  { ... }
])

Excellent answer! Is it possible to get the most recent term (under another schema, I need a similar query, but it must be based on the most recent (and the ordering of the array is not chronological). — Joao, May 28 '18 at 01:39
If the last element of term arrays is not always the one with the latest date, then this would be more appropriate: https://stackoverflow.com/a/17735611/9297144. Note that it uses a groupBy, which could impact performances depending on the size of your collection. Best would be, if possible, to ensure the order of terms when inserting/updating records. And you can apply the same type of query for your symmetrical query on most recent terms. — Xavier Guihot, May 28 '18 at 04:58
I thought I should add a comment here. I found a solution on this question to the latest date, which shows how to do it without `aggregate` using `$expr`: https://stackoverflow.com/questions/48657294/criteria-aggregation-search-for-all-complete-documents-whose-array-field-has-el?noredirect=1&lq=1 — Joao, May 30 '18 at 04:04

How to query documents by a condition on the subdocument with the latest date

1 Answers1