Query C# Mongo object where array property matches value and return object without projection

Question

I have an object like so:

public class DriverDocument : Document
{
    public string?          Email        { get; set; }
    public List<SubscriptionInfo> Subscriptions { get; set; } = new();
}

public class SubscriptionInfo
{
    public string           Id                  { get; set; } = null!;
    public string           PartnerId           { get; set; } = null!;
}

and I would to return all DriverDocument where the Subscriptions.PartnerId = '123'.

If I use .Find(Builders<DriverDocument>.Filter.ElemMatch(x => x.Subscriptions, s => s.PartnerId == partnerId)) this returns all DriverDocuments where ANY of the Subscriptions contain '123'.

I want to return the DriverDocument AND Subscriptions that contain only the relevant records.

If I do something like the below I have to use a projection which means manually mapping out the properties. If I add a new property to DriverDocument I would have to remember to include it in the projection.

 .Find(Filter.ElemMatch(x => x.Subscriptions, y => y.PartnerId == partnerId))
 .Project(Projection.Expression(
            d => new DriverDocument
            {
                Id = d.Id,
                Status = d.Status,
                FirstName   = d.FirstName,
                LastName    = d.LastName,
                Email       = d.Email,
                
                Subscriptions = d.Subscriptions
                    .Where(sub => sub.PartnerId == partnerId)
                    .ToList()
            }));

Is there any other way to return the DriverDocument AND Subscriptions records with just the data I want without having to map properties and without BsonDocument?

Answer 1

Instead of using a plain find with projection, you can run an aggregation pipeline with at least two stages:

• $match to filter for documents that have a subscription with a matching partner id
• $set to assign the Subscriptions property so that the list contains only items with the matching partner id

For simplicity, you can use LINQ to set up the aggregation pipeline. Please note that at the time of this writing, the sample works for MongoDB C# Driver 2.28 and the property type List<SubscriptionInfo>. For an alternative solution, see below.

var pipeline = new EmptyPipelineDefinition<DriverDocument>()
    .Match(x => x.Subscriptions.Any(y => y.PartnerId == "123"))
    .Set(x => new DriverDocument()
    {
        Subscriptions = x.Subscriptions.Where(y => y.PartnerId == "123").ToList()
    });
var result = await (await coll.AggregateAsync(pipeline)).ToListAsync();

This way, you do not have to keep in mind that you have to add new properties to the statement.

---

Appendix: The sample generates to following MongoDB aggregation pipeline

[
  {
    $match: {
      Subscriptions: {
        $elemMatch: { PartnerId: "123" }
      }
    }
  },
  {
    $set: {
      Subscriptions: {
        $filter: {
          input: "$Subscriptions",
          as: "y",
          cond: { $eq: ["$$y.PartnerId", "123"] }
        }
      }
    }
  }
]

---

Alternative solution without Linq

If you cannot upgrade to version 2.28 or your property type differs from List<SubscriptionInfo>, you can also set up the $set stage as a BsonDocument, e.g.:

var partnerId = "123";
var setStage = BsonDocument.Parse("""
{
  $set: {
    Subscriptions: {
      $filter: {
        input: "$Subscriptions",
        as: "y",
        cond: { $eq: ["$$y.PartnerId", "%%PARTNER_ID%%"] }
      }
    }
  }
}
""".Replace("%%PARTNER_ID%%", partnerId));
var pipeline = new EmptyPipelineDefinition<DriverDocument>()
    .Match(x => x.Subscriptions.Any(y => y.PartnerId == "123"))
    .AppendStage<DriverDocument, DriverDocument, DriverDocument>(setStage);
var result = await (await coll.AggregateAsync(pipeline)).ToListAsync();