Sort Subcollection and Return First Subcollection Document

Question

I have a collection with documents that look like this:

{
    "_id": ObjectId("507f191e810c19729de860ea"),
    "SubCollection": [{
        "_id": ObjectId("55849c0002cee826cc67550a"),
        "Timestamp": NumberLong(635717988841453845),
        "IsValid": true
    },
    {
        "_id": ObjectId("507f1f77bcf86cd799439011"),
        "Timestamp": NumberLong(635717988830033174),
        "IsValid": true
    }]  
}

    public class RootEntity
    {
        public ObjectId Id { get; set; }
        public List<SubEntity> SubCollection { get; set; }
    }

    public class SubEntity
    {
        public ObjectId Id { get; set; }
        public long Timestamp { get; set; }
        public bool IsValid { get; set; }
    }

I need to create a mongo query that returns the document including only the first sub collection item. However, the sub collection needs to first be filtered by the IsValid bool then sorted by the Timestamp.

So far I have a query that returns the document with the first sub collection item but I cannot get it to sort the sub collection first, can this be achieved?

This is the query that I have so far, please note that I am using the C# .NET MongoDB Driver v2.0:

        var filter = Builders<TestFocusEntity>.Filter.And(
            Builders<RootEntity>.Filter.Eq(x => x.Id, id),
            Builders<TestFocusEntity>.Filter.ElemMatch(x => x.SubCollection, sub => test.IsValid == true));

        var result = await collection.Find(filter)
            .Sort(Builders<RootEntity>.Sort.Ascending("SubCollection.$.Timestamp"))
            .Project<RootEntity>(Builders<RootEntity>.Projection.Slice(x => x.SubCollection, 0, 1))
            .ToListAsync();

        var rootDocument = result.SingleOrDefault();
        var subDocument = rootDocument == null
            ? null
            : rootDocument.SubCollection.SingleOrDefault();

Answer 1

The sort on the Timestamp field is going to sort the matched documents (which will have no effect as there is only at most 1 matched by id). It won't sort the array within the matched document. It's not possible to do such a sort within arrays on the DB at all in MongoDB, it's not a limitation of the C# driver.

See Mongo documentation on array sort:

With arrays ... an ascending sort compares the smallest element of arrays

i.e. it's sorting the documents by the smallest value of the array in each document, not sorting within the array.

If the SubEntity array is reasonably small, you can perform do that part of your operation in memory after pulling back the whole array.

If the SubEntity array is very large, you may want to rethink the document schema to avoid pulling back the whole document and subcollection into memory each time. For example, make each SubEntity a document in it's own right.