Mongodb find multiple and retrive one record for matching id

user_11005474 0
Viewed 35

I'm trying to find many records in the same query, the records are of this type

{
    "key": 2,
    time: 3
  },
  {
    "key": 2,
    time: 5
  },
  {
    "key": 3,
    time: 4
  }

I'd like to retrive for example the records with key inside [2,3] and with the relative maximum amount of time, so in this case recrods with { "key": 2, time: 5 } and { "key": 3, time: 4 } should be returned.

So far i got only how to retrive the records with key inside in a proveded array but i'm not understanding how to get for each key in the array the record with the latest time, i'd like to not use the where operator.

Here is a playground with the provided scenario https://mongoplayground.net/p/5sWnn8_HnqK

mongoose mongodb
2 Answers

You can use aggregate function to find max element for each key. Example: https://mongoplayground.net/p/P9JSuvTHdZa

    db.collection.aggregate([
  {
    $match: {
      key: {
        $in: [
          2,
          3
        ]
      }
    }
  },
  {
    $group: {
      _id: "$key",
      time: {
        $max: "$time"
      }
    }
  }
])

Edited: as in comment suggested

user_104570880

The easiest way to achieve this is by matching the documents, sorting them descendingly and then choose the "first" document for each key, like so:

db.collection.aggregate([
  {
    $match: {
      key: {
        $in: [
          1,
          2,
          3
        ]
      }
    }
  },
  {
    $sort: {
      time: -1
    }
  },
  {
    $group: {
      _id: "$key",
      first: {
        $first: "$$ROOT"
      }
    }
  }
])
user_107703700
Related