{
    "_id" : ObjectId("61a765e6f664eb8f6b12c"),
    "details" : [ 
        {
            "_id" : ObjectId("60c84d9968c2d100154f3391"),
            "expiryDate" : ISODate("2021-06-12T05:30:00.000Z"),
            "updatedAt" : ISODate("2021-06-15T06:50:01.046Z"),
            "createdAt" : ISODate("2021-06-10T06:50:01.046Z")
        }, 
        {
            "_id" : ObjectId("60c84d99c2d100154f3391"),
            "expiryDate" : ISODate("2021-06-25T05:30:00.000Z"),
            "updatedAt" : ISODate("2021-06-15T06:50:01.046Z"),
            "createdAt" : ISODate("2021-06-16T06:50:01.046Z")
        }, 
        {
            "_id" : ObjectId("60c84d9968c20154f3391"),
            "expiryDate" : ISODate("2021-06-25T05:30:00.000Z"),
            "updatedAt" : ISODate("2021-06-15T06:50:01.046Z"),
            "createdAt" : ISODate("2021-06-15T06:50:01.046Z")
        }
    ]
}

How can i write mongo query to sort and get the latest date and then apply date range filter on that

CodePudding user response：

You can $addFields an auxilary field lastCreatedAt by using $max. Then $match on the field in an aggregation pipeline.

db.collection.aggregate([
  {
    "$addFields": {
      "lastCreatedAt": {
        $max: "$details.createdAt"
      }
    }
  },
  {
    "$match": {
      lastCreatedAt: {
        // input your date range here
        $gte: ISODate("2012-06-16T06:50:01.000Z"),
        $lt: ISODate("2021-12-30T06:50:01.100Z")
      }
    }
  },
  {
    $group: {
      _id: null,
      docs: {
        $push: "$$ROOT"
      },
      numOfLastCreatedAt: {
        $sum: 1
      }
    }
  }
])

Here is the Mongo playground for your reference.