MongoDB query for multiple and distinct values based on latest date

Question

Consider the scenario where I have to fetch records based on 2 Ids (LID and KID). I am allowed to have multiple LID matching records but if KID is duplicate in matching records, then pick the latest one according to the date field. Below is the sample data and required output.

[{
    "_id" : 6,
    "KID" : 28,
    "LID" : "45",
    "pointDate" : ISODate("2022-07-18T06:55:16.094+0000"),
    "weight" : 10.0
},
{
    "_id" : 3,
    "KID" : 44,
    "LID" : "45",
    "pointDate" : ISODate("2022-06-18T09:45:47.198+0000"),
    "weight" : 5.0
},
{
    "_id" : 22,
    "KID" : 28,
    "LID" : "45",
    "pointDate" : ISODate("2022-08-18T09:49:04.059+0000"),
    "weight" : 9.0
},
{
    "_id" : 10,
    "KID" : 28,
    "LID" : "45",
    "pointDate" : ISODate("2022-09-18T09:49:04.059+0000"),
    "weight" : 10.0
},
{
    "_id" : 10,
    "KID" : 32,
    "LID" : "50",
    "pointDate" : ISODate("2022-03-18T09:49:04.059+0000"),
    "weight" : 10.0
}]

As per the above sample data I am expecting the following output:

[{
    "_id" : 3,
    "KID" : 44,
    "LID" : "45",
    "pointDate" : ISODate("2022-06-18T09:45:47.198+0000"),
    "weight" : 5.0
},
{
    "_id" : 10,
    "KID" : 28,
    "LID" : "45",
    "pointDate" : ISODate("2022-09-18T09:49:04.059+0000"),
    "weight" : 10.0
}]

In above result output, I am fetching records with LID=45, where KID is either equal to 44 or 28, but KID=28 is having three records (duplicates) where I have to pick the record with latest date according to field: "pointDate". What will be mongodb query for this scenario ? Also is there any possible JPA method for this scenario ?

Answer 1

One option is:

db.collection.aggregate([
  {$match: {LID: "45", KID: {$in: [28, 44]}}},
  {$sort: {pointDate: -1}},
  {$group: {
      _id: {LID: "$LID", KID: "$KID"},
      res: {$first: "$$ROOT"}
  }},
  {$replaceRoot: {newRoot: "$res"}}
])

See how it works on the playground example