MongoDB C# Driver - how to enforce projection on the joined collection in .NET?

Question

Here's the code:

ProjectionDefinition<Accountant> projDefAccountant = Builders<Accountant>.Projection
                    .Include(x => x.Id)
                    .Include(x => x.Name);

ProjectionDefinition<Client> projDefClient = Builders<Client>.Projection
                    .Include(c => c.Name)
                    .Include(c => c.Address)
                    .Include(c => c.Occupation);

IMongoCollection<Accountant> collection = mongoDatabase.GetCollection<Accountant>("accountants");
IMongoCollection<Client> foreignCollection = mongoDatabase.GetCollection<Client>("clients");

                var results = collection.Aggregate()
                    .Project<Accountant>(projDefAccountant)
                    .Lookup<Accountant, Client, Accountant>(
                        foreignCollection: foreignCollection,
                        localField: ac => ac.BestClientsIds,
                        foreignField: c => c.Id,
                        @as: ac => ac.MyClients
                    ).ToList().AsQueryable();

I'm able to use the first projection "projDefAccountant" to limit what fields I want out of the "accountants" collection. Is there a way to enforce the "projDefClient" projection on the joined "clients" collection so that the join doesn't return all the fields but only those specified in the "projDefClient"? Thx.

Answer 1

You can use $lookup with custom pipeline and your aggregation could look like this:

db.accountants.aggregate([
    { "$project" : { "_id" : 1, "Name" : 1, BestClientsIds: 1 } }, 
    { 
        "$lookup" : { 
            "from" : "clients", 
            "let" : { "best_client_ids" : "$BestClientsIds" }, 
            "pipeline" : [
                { "$match" : { "$expr" : { "$in" : [ "$_id", "$$best_client_ids"] } } }, 
                { "$project": { Name: 1, Address: 1, Occupation: 1} }
            ], 
            as: "MyClients"}
    }    
]);

Mongo Playground

In C# there's one overloaded version of .Lookup which allows you to run that method in an almost strongly-typed way. Here's the signature:

IAggregateFluent<TNewResult> Lookup<TForeignDocument, TAsElement, TAs, TNewResult>(
        IMongoCollection<TForeignDocument> foreignCollection,
        BsonDocument let,
        PipelineDefinition<TForeignDocument, TAsElement> lookupPipeline,
        FieldDefinition<TNewResult, TAs> @as,
        AggregateLookupOptions<TForeignDocument, TNewResult> options = null)
        where TAs : IEnumerable<TAsElement>;

You can modify projDefAccountant so that it includes BestClientsIds field:

ProjectionDefinition<Accountant> projDefAccountant = Builders<Accountant>.Projection
            .Include(x => x.Id)
            .Include(x => x.Name)
            .Include(x => x.BestClientsIds);

Then it's easier to specify let and $match phases as BsonDocument however the rest stays strongly-typed:

var filter = new BsonDocumentFilterDefinition<Client>(BsonDocument.Parse("{ $expr: { $in: [ '$_id', '$$ids' ] } }"));

PipelineDefinition< Client, Client> pipeline = new PipelineStagePipelineDefinition<Client, Client>(
    new IPipelineStageDefinition[]
    {
        PipelineStageDefinitionBuilder.Match(filter),
        PipelineStageDefinitionBuilder.Project<Client, Client>(projDefClient),
    });

ExpressionFieldDefinition<Accountant, Client[]> fieldDef 
    = new ExpressionFieldDefinition<Accountant, Client[]>(f => f.MyClients);

var letDef = BsonDocument.Parse("{ ids: '$BestClientsIds' }");


var results = collection.Aggregate()
    .Project<Accountant>(projDefAccountant)
    .Lookup<Client, Client, Client[], Accountant>(
        foreignCollection: foreignCollection,
        let: letDef,
        lookupPipeline: pipeline,
        @as: fieldDef
    ).ToList().AsQueryable();