c# MongoDB Driver, retrieve object from a nested Array

Question

In C# using the .NET MongoDB driver, I am trying to retrieve objects that are in a nested array of a document and that match some criteria.

Models:

  public class MainElement
  {
    [BsonId]
    public ObjectId DocumentId { get; set; }
    public string A { get; set; }
    public string B { get; set; }
    public List<NestedElement> NestedObjects { get; set; }
  }

  public class NestedElement
  {
    public DateTime Date { get; set; }

    public string W { get; set; }
    public long Y { get; set; }
  }

What I want to retrieve is the property A from MainElement and properties Date, W, Y from NestedElement for all documents thats matches my filters.

So far I tried this but it returns the full MainElement document:

  var filters = Builders<MainElement>.Filter.Eq("A", "Something") &
                Builders<MainElement>.Filter.Eq("B", "AnotherThing") &
                Builders<MainElement>.Filter.Gte("NestedObjects.Date", day) &
                Builders<MainElement>.Filter.Eq("NestedObjects.W", "ABC") &
                Builders<MainElement>.Filter.Gte("NestedObjects.Y", 1500);

  var projection = Builders<MainElement>.Projection.Include("A")
                                                   .Include("NestedObjects.Date")
                                                   .Include("NestedObjects.W")
                                                   .Include("NestedObjects.Y");

  var elements = Collection.Find(filters).Project(projection).ToList();

Any ideas?

Re your filter: can you confirm that you want documents where any NestedObject (NO)'s date is greater than day, any NO's W property (not necessarily the same NO as the previous requirement) is equal to ABC, and any NO's Y property is equal to 1500, as opposed to only documents where all of those are true for a single nested object? — ProgrammingLlama
– ProgrammingLlama, Commented Sep 8, 2021 at 1:15
I want to retrieve only the NestedObject that match my criteria and not the full document, where the Document A = Something B = AnotherThing and retrieve all its NestedObjects with Date >= day and W = ABC and Y >= 1500. — SlashJ
– SlashJ, Commented Sep 8, 2021 at 12:52

ProgrammingLlama · Accepted Answer · 2021-09-09 01:33:02Z

3

You should be able to write your query like this:

// We need the NestedElement filter twice: once to filter documents and once to filter the array in the projection
var nestedFilter = Builders<NestedElement>.Filter.Gte(n => n.Date, day)
                    & Builders<NestedElement>.Filter.Eq(n => n.W, "ABC")
                    & Builders<NestedElement>.Filter.Eq(n => n.Y, 1500);



var elements = Collection
    .Find(
        Builders<MainElement>.Filter.Eq(e => e.A, "Something")
        & Builders<MainElement>.Filter.Eq(e => e.B, "AnotherThing")
        // match only documents that have matching nested array elements
        & Builders<MainElement>.Filter.ElemMatch(e => e.NestedObjects, nestedFilter)
    )
    .Project(
        Builders<MainElement>.Projection
            .Include(e => e.A)
            // include only matching elements in the resulting object
            .ElemMatch(e => e.NestedObjects, nestedFilter)
    )
    .ToList();

I've used ElemMatch in the document filter because, from your comments, that seems to be what you actually want. For example: one of the many NestedObjects should have Date=day,W=ABC,Y>=1500, rather than one NestedObject matching Date=day, and another on the same document matching W=ABC and Y>=1500.

edited Sep 9, 2021 at 1:33

answered Sep 8, 2021 at 13:47

ProgrammingLlama

39.3k7 gold badges78 silver badges104 bronze badges

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

3 Comments

SlashJ Over a year ago

Thank you Llama, I learned a lot from this snippet.

SlashJ Over a year ago

For some reason, in the returned document, there is always only 1 element in the NestedObjects array even tho I can see may potential matches in the database. Any idea on how to get all NestedObject elements that are matches?

ProgrammingLlama Over a year ago

@SlashJ Could it possibly be an issue with .Gte(n => n.Date, day)? Are you factoring in the time (and if you're not always using UTC) the timezone here?

YuTing · Accepted Answer · 2021-09-08 01:03:51Z

0

You have to user $project and $filter the array.

This is what I try：mongoplayground

answered Sep 8, 2021 at 1:03

YuTing

6,6692 gold badges8 silver badges19 bronze badges

1 Comment

SlashJ Over a year ago

Thank you. Do you have any idea on how to translate that to .NET?

Collectives™ on Stack Overflow

c# MongoDB Driver, retrieve object from a nested Array

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related