Query for last document with different restriction per document

user_11986167 0
Viewed 19

I have a list of cars in the following form:

  {
    "_id": ObjectId("60a2c0621e5f043b735e36ef"),
    "car_id": 78,
    "terminal": "JFK",
    "timestamp": ISODate("2020-01-01T17:00:00.000Z"),
    
  },

I wish to create a query where there are 2 restrictions per car (car_id and timestamp). Both parameters (car_id and timestamp) are different per each car.

Example:

  • for car_id '78" get the last document where the timestamp is "2020-02-15T05:00:11.000Z"
  • for car_id "79" get the last document where the timestamp is "2020-02-23T11:07:27.000Z"

I managed to get it per one car but not for each car.

My expected result would be:

car_id   timestamp                terminal
78       2020-02-08T17:00:00Z     LAX
79       2020-02-11T17:00:00Z     MIA

mongoplayground

aggregation-framework mongodb
1 Answers

just need to sort before $group stage,

  • $sort by timestamp in ascending order
db.collection.aggregate([
  {
    "$match": {
      "$or": [
        {
          "car_id": 78,
          "timestamp": {
            "$lte": ISODate("2020-02-15T05:00:11.000Z")
          }
        },
        {
          "car_id": 79,
          "timestamp": {
            "$lte": ISODate("2020-02-23T11:07:27.000Z")
          }
        }
      ]
    }
  },
  { "$sort": { "timestamp": 1 } },
  {
    "$group": {
      "_id": "$car_id",
      "last": { "$last": "$$ROOT" }
    }
  }
])

Playground

Simplified the $match stage condition, it will work same as your syntax.

user_89871280
Related