Return multiple documents which match the latest date with a single MongoDB query in Mongoose

Question

The following is MongoDB query. What is the best possible way to write this query in Mongoose?

db.logs.find({date: db.logs.find({}, {date:1,"_id":0}).sort({date:-1}).limit(1).toArray()[0].date})

There could be multiple documents with the same date and we need to retrieve all the documents that match the latest date.

@chridam, using your query it will return only 1 record. But if there are multiple documents having the latest date it will pick only 1 and ignore others. This should not happen — Temp O'rary, Aug 31 '18 at 08:51
what is the trouble in these queries ? `const date = await db.logs.find({ }, { date: 1, _id: false }).sort({ date: -1 }).limit(1)` and then `const data = await db.logs.find({ date: date[0].date })` — Ashh, Aug 31 '18 at 18:13

score 2 · Answer 1 · answered Aug 31 '18 at 10:27

The aggregation framework makes it possible to write this in a single query. You would require a pipeline that has an initial $lookup operator to do a self-join and within the $lookup execute a pipeline on the joined collection which allows for uncorrelated sub-queries to return the latest date:

db.logs.aggregate([
    {  "$lookup": {
        "from": "logs",
        "pipeline": [
            { "$sort": { "date": -1 } },
            { "$limit": 1 },
            { "$project": { "_id": 0, "date": 1 } }
        ],
        "as": "latest"
    } }
])

A further step is required to reshape the new field latest produced above so that the array is flattened. Use $addFields to reshape and $arrayElemAt to flatten the array or use "$unwind":

db.logs.aggregate([
    {  "$lookup": {
        "from": "logs",
        "pipeline": [
            { "$sort": { "date": -1 } },
            { "$limit": 1 },
            { "$project": { "_id": 0, "date": 1 } }
        ],
        "as": "latest"
    } },
    { "$addFields": { "latest": { "$arrayElemAt": ["$latest", 0] } } }
])

The final step would be to filter the documents in the resulting pipeline using $expr in a $match stage since you will be comparing fields from the same document:

db.logs.aggregate([
    {  "$lookup": {
        "from": "logs",
        "pipeline": [
            { "$sort": { "date": -1 } },
            { "$limit": 1 },
            { "$project": { "_id": 0, "date": 1 } }
        ],
        "as": "latest"
    } },
    { "$addFields": { "latest": { "$arrayElemAt": ["$latest", 0] } } },
    { "$match": {
        "$expr": {
            "$eq": [ "$date", "$latest.date" ]
        } 
    } }
])

Getting the query into Mongoose becomes a trivial exercise.

Return multiple documents which match the latest date with a single MongoDB query in Mongoose

1 Answers1