Get flattened array of documents embedded in array with filter on document property in MongoDB

Question

I have a MongoDb collection of documents each containing an array of embedded documents. I'd like to retrieve a flattened list of those embedded documents of which the date property is before a given date.

Let's assume we have the following classes:

public class RootItem
{
    public string Id { get; set; }
    public string Name{ get; set; }
    public string PictureUrl { get; set; }
    public List<Reservation> Reservations { get; set; }
}

public class Reservation
{
    public string Name { get; set; }
    public DateTime Date{ get; set; }
    public int NrOfSeats { get; set; }
}

So the collection would look something like this:

{
  "_id": "5be2bb2fdfd6174938518af2",
  "name": "John Doe",
  "pictureUrl": "http://example.com/abc.jpg",
  "reservations": [
    {
      "table": "abc",
      "date": "1/1/2019",
      "nrOfSeats": 5
    },
    {
      "name": "xyz",
      "date": "7/1/2019",
      "nrOfSeats": 5
    }
  ]
}

I've read the docs, I've read alot here at SO but the closest I got this far was this:

var reservations = Collection
            .Aggregate()
            .Unwind<RootItem, Reservation>(x => x.Reservations)
            .ToList()
            .FindAll(r => r.Date > thresholdDate);

Running this code I got this error:

System.FormatException: 'Element '_id' does not match any field or property of class 'Reservation'

So I add a projection. If I only exclude the _id field I get:

'Element 'Created' does not match any field or property of class 'Reservation'

So I manually include the other fields (should be unnecessary):

var reservations = Collection
            .Aggregate()
            .Unwind<RootItem, Reservation>(x => x.Reservations)
            .Project<Reservation>(Builders<Reservation>.Projection
                 .Exclude("_id")
                 .Include(r => r.Name)
                 .Include(r => r.Date)
                 .Include(r => r.NrOfSeats))
            .ToList()
            .FindAll(r => r.Date > thresholdDate);

But now I get a List with NrOfSeats and Name set to null and Date to 1/1/0001.

How can I retrieve a flattened list of all reservations in my collection of which the date property is before/smaller than a given date?

JavierFromMadrid · Accepted Answer · 2019-02-02 17:49:07Z

1

I think if you use

collection.AsQueryable().SelectMany(s => s.Reservations).Where(r => r.Date > thresholdDate).ToList();

It should return what you expect.

answered Feb 2, 2019 at 17:49

JavierFromMadrid

6219 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bertenvdb Over a year ago

Awesome, exactly what I wanted! Thanks ;-)

Collectives™ on Stack Overflow

Get flattened array of documents embedded in array with filter on document property in MongoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related