Querying within Child Collections

TIP

Marten V4 greatly improved Marten's abilities to query within child collections of documents

Quantifier Operations within Child Collections

Marten supports the Any() and Contains() quantifier operations within child collections.

The following code sample demonstrates the supported Linq patterns for collection searching:

public class ClassWithChildCollections
{
    public Guid Id;

    public IList<User> Users = new List<User>();
    public Company[] Companies = [];

    public string[] Names;
    public IList<string> NameList;
    public List<string> NameList2;
}

public void searching(IDocumentStore store)
{
    using (var session = store.QuerySession())
    {
        var searchNames = new string[] { "Ben", "Luke" };

        session.Query<ClassWithChildCollections>()
            // Where collections of deep objects
            .Where(x => x.Companies.Any(_ => _.Name == "Jeremy"))

            // Where for Contains() on array of simple types
            .Where(x => x.Names.Contains("Corey"))

            // Where for Contains() on List<T> of simple types
            .Where(x => x.NameList.Contains("Phillip"))

            // Where for Contains() on IList<T> of simple types
            .Where(x => x.NameList2.Contains("Jens"))

            // Where for Any(element == value) on simple types
            .Where(x => x.Names.Any(_ => _ == "Phillip"))

            // The Contains() operator on subqueries within Any() searches
            // only supports constant array of String or Guid expressions.
            // Both the property being searched (Names) and the values
            // being compared (searchNames) need to be arrays.
            .Where(x => x.Names.Any(_ => searchNames.Contains(_)));
    }
}

^{snippet source | anchor}

You can search on equality of multiple fields or properties within the child collection using the && operator:

var results =(await  theSession
    .Query<Target>()
    .Where(x => x.Children.Any(_ => _.Number == 6 && _.Double == -1))
    .ToListAsync());

^{snippet source | anchor}

Finally, you can query for child collections that do not contain a value:

(await theSession.Query<DocWithArrays>().CountAsync(x => !x.Strings.Contains("c")))
    .ShouldBe(2);

^{snippet source | anchor}

(await theSession.Query<DocWithArrays>().CountAsync(x => !x.Strings.Contains("c")))
    .ShouldBe(2);

^{snippet source | anchor}

How Marten Translates Collection Predicates 9.15

Marten picks the most index-friendly SQL strategy it can for a Where() clause that filters against a child collection, in this order:

Predicate shape	SQL strategy	GIN index eligible?
Equalities combined with &&, e.g. x.Lines.Any(l => l.Name == "a" && l.Number == 2)	JSONB containment: data -> 'Lines' @> '[{"Name":"a","Number":2}]'	Yes
Equalities combined with ||, e.g. x.Lines.Any(l => l.Name == "a" || l.Name == "b")	OR of containment filters (BitmapOr over the index)	Yes
Contains() of a whole element, e.g. x.Lines.Contains(line)	JSONB containment: d.data @> '{"Lines":[{...serialized element...}]}'. Note: matches elements containing every serialized member of the argument, so documents written with extra/evolved properties still match	Yes, including whole-document GinIndexJsonData() indexes
Other comparisons (>, <, >=, <=, !=) and mixes, e.g. x.Lines.Any(l => l.Name == "a" && l.Number > 5)	JSONPath: jsonb_path_exists(d.data, '$.Lines[*] ? (@.Name == $val1 && @.Number > $val2)', :params)	No, but a single scan with a cheap per-row predicate
Case-sensitive StartsWith()	JSONPath starts with $var — fully parameterized, works in compiled queries	No
Contains()/EndsWith() on strings and case-insensitive comparisons	JSONPath like_regex with the (escaped) search text embedded in the SQL. Not usable in compiled queries — the value cannot be re-bound, and Marten fails loudly if you try	No
Aggregates in predicates, e.g. x.Lines.Sum(l => l.Number) > 100 (also Min/Max/Average, and parameterless overloads on scalar collections)	Correlated scalar subquery: (SELECT COALESCE(SUM(...), 0) FROM ... ) > :arg. Sum() over an empty collection is 0 like LINQ-to-objects; Min/Max/Average over an empty collection simply fail the comparison (C# would throw)	No
All(predicate) for any jsonpath-capable predicate	NOT jsonb_path_exists(d.data, '$.Lines[*] ? (!(...))') — vacuously true on empty collections, and elements with null/missing members fail string predicates just like in LINQ-to-objects	No
Indexing into a collection, e.g. x.Lines[0].Name == "x", x.Lines.ElementAt(1).Number > 5	Plain JSON path navigation: data -> 'Lines' -> 0 ->> 'Name'. Out-of-range indexes yield SQL NULL and simply don't match	Via computed/duplicated indexes only
Anything else (member-to-member comparisons, DateTime values)	Correlated EXISTS (SELECT 1 FROM ...) over the exploded elements	No

A couple of practical notes:

• The containment strategies compare against data -> 'CollectionName', so a GIN index needs to be an expression index on that same expression to be used, e.g. CREATE INDEX ON mt_doc_order USING gin ((data -> 'Lines') jsonb_path_ops). A whole-document index from GinIndexJsonData() will not accelerate child collection containment.
• The JSONPath strategy passes all comparison values through the vars argument of jsonb_path_exists(), so the SQL stays fully parameterized and works with compiled queries.
• JSONPath comparisons follow C# semantics more closely than the older common-table-expression strategy did: comparing a null member with != matches, and range comparisons against null or missing members do not.

Querying within Value IEnumerables

As of now, Marten allows you to do "contains" searches within Arrays, Lists & ILists of primitive values like string or numbers:

public async Task query_against_string_array()
{
    var doc1 = new DocWithArrays { Strings = ["a", "b", "c"] };
    var doc2 = new DocWithArrays { Strings = ["c", "d", "e"] };
    var doc3 = new DocWithArrays { Strings = ["d", "e", "f"] };

    theSession.Store(doc1);
    theSession.Store(doc2);
    theSession.Store(doc3);

    await theSession.SaveChangesAsync();

    (await theSession.Query<DocWithArrays>().Where(x => x.Strings.Contains("c")).ToListAsync())
        .Select(x => x.Id).ShouldHaveTheSameElementsAs(doc1.Id, doc2.Id);
}

^{snippet source | anchor}

Marten also allows you to query over IEnumerables using the Any method for equality (similar to Contains):

[Fact]
public async Task query_against_number_list_with_any()
{
    var doc1 = new DocWithLists { Numbers = new List<int> { 1, 2, 3 } };
    var doc2 = new DocWithLists { Numbers = new List<int> { 3, 4, 5 } };
    var doc3 = new DocWithLists { Numbers = new List<int> { 5, 6, 7 } };
    var doc4 = new DocWithLists { Numbers = new List<int> { } };

    theSession.Store(doc1, doc2, doc3, doc4);

    await theSession.SaveChangesAsync();

    (await theSession.Query<DocWithLists>().Where(x => x.Numbers.Any(_ => _ == 3)).ToListAsync())
        .Select(x => x.Id).ShouldHaveTheSameElementsAs(doc1.Id, doc2.Id);

    // Or without any predicate
    (await theSession.Query<DocWithLists>()
        .CountAsync(x => x.Numbers.Any())).ShouldBe(3);
}

^{snippet source | anchor}

As of 1.2, you can also query against the Count() or Length of a child collection with the normal comparison operators (==, >, >=, etc.):

[Fact]
public async Task query_against_number_list_with_count_method()
{
    var doc1 = new DocWithLists { Numbers = new List<int> { 1, 2, 3 } };
    var doc2 = new DocWithLists { Numbers = new List<int> { 3, 4, 5 } };
    var doc3 = new DocWithLists { Numbers = new List<int> { 5, 6, 7, 8 } };

    theSession.Store(doc1);
    theSession.Store(doc2);
    theSession.Store(doc3);

    await theSession.SaveChangesAsync();

    (await theSession.Query<DocWithLists>()
        .SingleAsync(x => x.Numbers.Count() == 4)).Id.ShouldBe(doc3.Id);
}

^{snippet source | anchor}

IsOneOf

IsOneOf() extension can be used to query for documents having a field or property matching one of many supplied values:

// Finds all SuperUser's whose role is either
// Admin, Supervisor, or Director
var users = session.Query<SuperUser>()
    .Where(x => x.Role.IsOneOf("Admin", "Supervisor", "Director"));

^{snippet source | anchor}

To find one of for an array you can use this strategy:

// Finds all UserWithNicknames's whose nicknames matches either "Melinder" or "Norrland"

var nickNames = new[] {"Melinder", "Norrland"};

var users = session.Query<UserWithNicknames>()
    .Where(x => x.Nicknames.IsOneOf(nickNames));

^{snippet source | anchor}

To find one of for a list you can use this strategy:

// Finds all SuperUser's whose role is either
// Admin, Supervisor, or Director
var listOfRoles = new List<string> {"Admin", "Supervisor", "Director"};

var users = session.Query<SuperUser>()
    .Where(x => x.Role.IsOneOf(listOfRoles));

^{snippet source | anchor}

In

In() extension works exactly the same as IsOneOf(). It was introduced as syntactic sugar to ease RavenDB transition:

// Finds all SuperUser's whose role is either
// Admin, Supervisor, or Director
var users = session.Query<SuperUser>()
    .Where(x => x.Role.In("Admin", "Supervisor", "Director"));

^{snippet source | anchor}

To find one of for an array you can use this strategy:

// Finds all UserWithNicknames's whose nicknames matches either "Melinder" or "Norrland"

var nickNames = new[] {"Melinder", "Norrland"};

var users = session.Query<UserWithNicknames>()
    .Where(x => x.Nicknames.In(nickNames));

^{snippet source | anchor}

To find one of for a list you can use this strategy:

// Finds all SuperUser's whose role is either
// Admin, Supervisor, or Director
var listOfRoles = new List<string> {"Admin", "Supervisor", "Director"};

var users = session.Query<SuperUser>()
    .Where(x => x.Role.In(listOfRoles));

^{snippet source | anchor}

IsSupersetOf

// Finds all Posts whose Tags is superset of
// c#, json, or postgres
var posts = theSession.Query<Post>()
    .Where(x => x.Tags.IsSupersetOf("c#", "json", "postgres"));

^{snippet source | anchor}

IsSubsetOf

// Finds all Posts whose Tags is subset of
// c#, json, or postgres
var posts = theSession.Query<Post>()
    .Where(x => x.Tags.IsSubsetOf("c#", "json", "postgres"));

^{snippet source | anchor}