MongoDB C# driver sorting by property in related collection?

Question

I am trying to sort data in a query of one collection based on a property in another collection referenced by an id. In SQL terms basically a sort by a value in a foreign key. The main collection contains an entity called CaseData that looks similar to this (some unrelated properties removed:

{
    caseId: “some unique Id value”,
    type: {
        typeId: “someUniqueTypeId”
    }
    users: [
        {
            userId: “unique user id”,
            userType: “some unique user type id”
            …other properties
        }
    ],
    …other properties
}

And the second collection is a collection of user objects that looks like the json below that is referenced in the users collection above by it's userId:

{
    userId: “some unique id”,
    data: {
        property: {
            someSortableProperty: true
        }
    }
}

NOTE: the users array in caseData is not a "user" as it contains other properties that are specific to the assignment to the case that arent in user. The actual value I need to sort on is in the full user document. e.g. someSortableProperty.

I’ve got a mongo query that appears to be working if run in a mongo ide that looks like this:

db.cases.aggregate([
    { $match: { ‘type.typeId’: ‘someUniqueTypeId’, ‘users’:{$exists:true, $size:1} } },
    ^ this is not the final query qas just used to get data for the sample
    { $lookup: { ‘from’: ‘users’, ‘localField’: ‘users.userId’, ‘foreignField’: ‘userId’, ‘as’: ‘caseUser’ } },
    { $sort: {‘caseUser.data.property.someSortableProperty’:-1}}
])

The idea is that I need to sort case data by the caseUser.data.property.somevalueproperty on the related values in “caseUser” assuming only one case user in case data is used for the sort. Perhaps I am way off base with this query, but but it seems to work, I am just struggling with how to express this in the mongodb C# driver. I am trying to use a combination of aggregate and lookup, but nothing I have tried this far seems to make any sense. I ran across this post (mongodb - Aggregate $lookup with C# - Stack Overflow) on stack overflow which seems to be doing what I need to do, partially at least, but not of those examples compile and I am not understanding the relationships that person is trying to convey.

Is this even possible to do in the C# driver?

Answer 1

C# drivers allows specifying a raw MQL query in string form (as BsonDocument), so your above code will look similar to:

        var client = new MongoClient();
        var db = client.GetDatabase("d");

        var cases = db.GetCollection<CaseData>("cases");

        db.DropCollection("cases");
        db.DropCollection("users");

        var users = db.GetCollection<UserOutput>("users");
        var someUniqueTypeId = Guid.NewGuid();
        var someUniqueUserId = Guid.NewGuid();

        cases.InsertOne(new CaseData() { Type = new Type { TypeId = someUniqueTypeId }, Users = new[] { new User() { UserId = someUniqueUserId } } });
        users.InsertOne(new UserOutput() { UserId = someUniqueUserId, Data = new Data { Property = new Property { SomeSortableProperty = true } } });

        var res = cases
            .Aggregate()
            .Match(Builders<CaseData>.Filter.And(
                Builders<CaseData>.Filter.Eq(t => t.Type.TypeId, someUniqueTypeId),
                BsonDocument.Parse("{ users : { $exists : true, $size : 1} }")))
            // typed $lookup method doesn't support providing array as `localField`
            .AppendStage<UserOutput>(BsonDocument.Parse("{ $lookup : { 'from' : 'users', 'localField': 'users.userId', 'foreignField': 'userId', 'as': 'caseUser' } }"))
            .Sort(Builders<UserOutput>.Sort.Descending(c => c.Data.Property.SomeSortableProperty))
            .Single();

    // types
    public class Type
    {
        [BsonElement("typeId")]
        public Guid TypeId { get; set; }
    }

    public class User
    {
        [BsonElement("userId")]
        public Guid UserId { get; set; }
        public Guid UserType { get; set; }
    }

    public class CaseData
    {
        public ObjectId Id { get; set; }
        public Guid CaseId { get; set; }
        [BsonElement("type")]
        public Type Type { get; set; }
        [BsonElement("users")]
        public User[] Users { get; set; }
    }


    // foreign coll
    // this attribute is used since actual $lookup response document will be fields combination between `CaseData` and `UserOutput`. So to get rid of `CaseData` fields during deserialization use `BsonIgnoreExtraElementsAttribute`
    [BsonIgnoreExtraElements]
    public class UserOutput
    {
        public ObjectId Id { get; set; }
        public Guid UserId { get; set; }
        public Data Data { get; set; }
    }

    public class Data
    {
        public Property Property { get; set; }
    }
    public class Property
    {
        public bool SomeSortableProperty { get; set; }
    }

Generated MQL will be:

aggregate([
{ "$match" : { "type.typeId" : CSUUID("85e99185-5c1b-4beb-a108-1929d89c1b42"), "users" : { "$exists" : true, "$size" : 1 } } }, 
{ "$lookup" : { "from" : "users", "localField" : "users.userId", "foreignField" : "userId", "as" : "caseUser" } }, 
{ "$sort" : { "Data.Property.SomeSortableProperty" : -1 } }
])