How to aggregate $lookup with MongoDB C# driver?

Question

I am using below lookup to get all required documents & then apply LINQ query to find specific documents & it is working fine as expected.

     new BsonDocument("$lookup", new BsonDocument()
                        .Add("from", "JournalInstructionReplication")
                        .Add("localField", "_id")
                        .Add("foreignField", "_eid")
                        .Add("as", "Replicated"))
 var cursor = await collection.AggregateAsync(pipeline, options);
            List<BsonDocument> list = cursor.ToList();

           var failedDocs = list.Where(d => d["Replicated"][0]["lastReplicationStatus"] != "success" ||
                             d["Replicated"][0]["eSeq"] != d["Replicated"][0]["lastReplicationSequence"])
                            .ToList();

I want to merge above LINQ query with existing lookup query.

Any idea how to achieve with lookup?

Answer 1

Thanks a lot. It worked as expected.Final working code as below:

 new BsonDocument("$lookup", new BsonDocument()
                        .Add("from", "JournalInstructionReplication")
                        .Add("localField", "_id")
                        .Add("foreignField", "_eid")
                        .Add("as", "Replicated")),
                 new BsonDocument("$unwind", new BsonDocument()
                        .Add("path", "$Replicated")),
                   new BsonDocument("$match", new BsonDocument()
                        .Add("$expr", new BsonDocument()
                                .Add("$or", new BsonArray()
                                        .Add(new BsonDocument()
                                                .Add("$ne", new BsonArray()
                                                        .Add("$Replicated.lastReplicationStatus")
                                                        .Add("success")
                                                )
                                        )
                                        .Add(new BsonDocument()
                                                .Add("$ne", new BsonArray()
                                                        .Add("$Replicated.eSeq")
                                                        .Add("$Replicated.lastReplicationSequence")
                                                )
                                        )
                                )
                        ))
            };

            var cursor = await collection.AggregateAsync(pipeline, options);
            List<BsonDocument> list = cursor.ToList();

Answer 2

To avoid running [0] you can run $unwind since there's clearly 1:1 relationship between joined collections (you join them by _id). The in-memory part has to be converted into below $match:

{
    $match: {
        $expr: {
            $or: [
                { $ne: [ "$Replicated.lastReplicationStatus", "success" ] },
                { $ne: [ "$Replicated.eSeq", "$Replicated.lastReplicationSequence" ] },
            ]
        }
    }
}

which would look like below in C#:

var q = _channels.Aggregate()
                    .Lookup(
                    foreignCollectionName: "JournalInstructionReplication", 
                    localField: "_id", 
                    foreignField:"_id", 
                    @as: "Replicated")
    .Unwind("Replicated")
    .Match(new BsonDocument()
    {
        { "$expr", new BsonDocument()
        {
            { "$or", new BsonArray()
            {
                new BsonDocument(){{ "$ne", new BsonArray(){ "$Replicated.lastReplicationStatus", "success" } }},
                new BsonDocument(){{ "$ne", new BsonArray(){ "$Replicated.eSeq", "$Replicated.lastReplicationSequence" } }
            } }
        } }
    }});

var result = q.ToList();