Question 210474 in Mongodb Question posted in
The official documentation can be found here.

MongoDB query for multiple and distinct values based on latest date

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 ?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    Why your documents have same _ids, example:{ _id : 10} ?

    You can achieve your result by aggregation query.
    I think below code may help.

    db.yourCollection.aggregate(
[
{$match:{"LID":"45",'KID':{$in:[28,44]}}},
{$group:{_id:"$KID"}},{$sort:{pointDate:-1}}
]
)

    Hope it helps.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search