I am working a asp .net 6.0 with mongoDB.

I have a query to filter hotel results.

public async Task<List<HotelResultDocument>> GetHotelResults(List<string> rateKeys)
    {
        var filter = Builders<HotelResultDocument>.Filter.In(x => x.RateKey, rateKeys);

        filter &= Builders<HotelResultDocument>.Filter.Eq(x => x.RateType, "BOOKABLE");

        filter &= Builders<HotelResultDocument>.Filter.Gt(x => x.ExpiredAt, DateTime.Now);

        return await _hotelResultsCollection.Find(filter).ToListAsync();
    }

Here My issue is There are more than one responses for same rateKEy with "BOOKABLE" type. I want only lastly added collection with a rateKEy with "BOOKABLE" type.

How to filter to get above response?

public class HotelResultDocument
    {
        public string Id { get; set; }

        public string Reference { get; set; }

        public string Token { get; set; }

        public int HotelCode { get; set; }

        public string RateKey { get; set; }

        public string RateClass { get; set; }

        public string RateType { get; set; }

        public double Net { get; set; }

        public DateTime CreatedAt { get; set; }

        public DateTime ExpiredAt { get; set; }
    }

Note : If I sent four rateKeys I have to find the hotels for each rateKeys (if the hotel satisfied above conditions.) But using above query I got more then one hotel for a rateKey. I have to find the lastly added hotel for a particular rateKey.

Edit : Current Response

"hotel" : [
{
   "hotelCode" : 2,
   "CreatedAt" :{26/07/2022 03:50:26},
   "RateKey" : 1,
},

{
   "hotelCode" : 2,
   "CreatedAt" :{26/07/2022 03:51:26},
   "RateKey" : 1,
},

{
   "hotelCode" : 2,
   "CreatedAt" :{26/07/2022 03:52:26},
   "RateKey" : 1,
},

{
   "hotelCode" : 2,
   "CreatedAt" :{26/07/2022 03:54:26},
   "RateKey" : 1,
},

{
   "hotelCode" : 4,
   "CreatedAt" :{26/07/2022 03:52:26},
   "RateKey" : 5,
},

{
   "hotelCode" : 4,
   "CreatedAt" :{26/07/2022 03:55:26},
   "RateKey" : 5,
}
]

Expected Response :

"hotel" : [

{
   "hotelCode" : 2,
   "CreatedAt" :{26/07/2022 03:54:26}, 
   "RateKey" : 1,
},

{
   "hotelCode" : 4,
   "CreatedAt" :{26/07/2022 03:55:26},
   "RateKey" : 5,
}
]

Anyone help me. Thank you.

CodePudding user response：

I believe that you can achieve it with Aggregation Pipeline.

Updated based on Post Owner's requirements

1. $match - Filter documents.

2. $sort - Order by CreatedAt descending.

3. $group - Group by RateKey and take the first document as hotel.

4. $replaceWith - Replace the input document with hotel.

MongoDB query

db.collection.aggregate([
  {
    $match: {}
  },
  {
    $sort: {
      CreatedAt: -1
    }
  },
  {
    $group: {
      _id: "$RateKey",
      hotel: {
        $first: "$$ROOT"
      }
    }
  },
  {
    "$replaceWith": "$hotel"
  }
])