C# Driver aggregate lookup for embedded array with another collection

Question

I have two MongoDB collections, customers and instances:

customers:

[
  {
    id: 1,
    name: 'Customer Name',
    projects: [
      {
        name: 'Project 1',
        description: 'Project description',
        instances: [10],
      },
      {
        name: 'Project 2',
        description: 'Project description',
        instances: [10, 20],
      },
    ],
  },
]

instances

[
  {
    id: 10,
    operatingSystem: 'Microsoft Windows 2012R2',
    version: '3.1.5',
    product: {
      name: 'Product 1',
      vendor: 'Vendor A',
    },
  },
  {
    id: 20,
    operatingSystem: 'Microsoft Windows 2016',
    version: '4.1.0',
    product: {
      name: 'Product 2',
      vendor: 'Vendor B',
    },
  },
]

I'm trying to $lookup the collections with C# LINQ aggregation - with no luck

The expected results are a single customer (matched by id, i.e. '1') with the instances (second collection) details when each instance document match the instance id provided by the first collection (customers.Projects.Instances):

{
  id: 1,
  name: 'Customer Name',
  projects: [
    {
      name: 'Project 1',
      description: 'Project description',
      instances: [
        {
          id: 10,
          operatingSystem: 'Microsoft Windows 2012R2',
          version: '3.1.5',
          product: {
            name: 'Product 1',
            vendor: 'Vendor A',
          },
        },
      ],
    },
    {
      name: 'Project 2',
      description: 'Project description',
      instances: [
        {
          id: 10,
          operatingSystem: 'Microsoft Windows 2012R2',
          version: '3.1.5',
          product: {
            name: 'Product 1',
            vendor: 'Vendor A',
          },
        },
        {
          id: 20,
          operatingSystem: 'Microsoft Windows 2016',
          version: '4.1.0',
          product: {
            name: 'Product 2',
            vendor: 'Vendor B',
          },
        },
      ],
    },
  ],
}

Will appreciate your help finding the appropriate LINQ query to match/join the two collections to match the expected results in the most effective way (minimum roundtrips to the db)

EDIT

Code I have tried:

public async ValueTask<dynamic> GetAsync(string customerId)
{
    var customer = await _customers.Aggregate()
        .Match(c => c.Id == customerId)
        .Lookup("instances", "Projects.Instances", "_id", "Results")
        .FirstOrDefaultAsync();
        
    return customer;
}

Another thing to mention:

• Both Instances.Id and Customers.Projects.Instances[] are ObjectId type
• The reason I'm trying to use a non-strong type in my query is because I couldn't find a way to match 'Projects.Instances' using C# objects.

Having the same syntax on Mongo shell returns the results (no exactly as expected, by at least not throwing error)

The query above returns an exception:

Unable to cast object of type 'MongoDB.Bson.BsonObjectId' to type 'MongoDB.Bson.BsonBoolean'.

And again, I prefer using the LINQ syntax but unfortunately with no success

UPDATE

I managed to run the following code that returns the matching instances. Now I just need to find a way to attach it to the customer object since that's the entity I really want to return:

var results = _customers.AsQueryable()
                .Where(c => c.Id == customerId)
                .SelectMany(i => i.Projects)
                .FirstOrDefault()
                .Instances
                .Join(_instances.AsQueryable(), a => a, b => b.Id, (a, b) => new { Instance = b })
                .ToList();

and the results:

[
  {
    instance: {
      id: 10,
      operatingSystem: 'Microsoft Windows 2012R2',
      version: '3.1.5',
      product: {
        name: 'Product 1',
        vendor: 'Vendor A',
      },
    },
  },
  {
    instance: {
      id: 20,
      operatingSystem: 'Microsoft Windows 2016',
      version: '4.1.0',
      product: {
        name: 'Product 2',
        vendor: 'Vendor B',
      },
    },
  },
]

Thanks!

Answer 1

After spending some hours, the following code (using the amazing MongoDB aggregation framework), resolved my problem:

public async ValueTask<dynamic> GetAsync(string customerId)
{
    var unwind = new BsonDocument("$unwind",
        new BsonDocument("path", "$projects"));

    var lookup = new BsonDocument("$lookup",
        new BsonDocument("from", "instances")
        .Add("localField", "projects.instances")
        .Add("foreignField", "_id")
        .Add("as", "projects.instances"));

    var group = new BsonDocument("$group",
        new BsonDocument("_id", "$_id")
        .Add("name", new BsonDocument("$first", "$name"))
        .Add("projects", new BsonDocument("$push", "$$ROOT.projects"))
        );

    return await _customers.Aggregate()
        .Match(c => c.Id == customerId)
        .AppendStage<dynamic>(unwind)
        .AppendStage<dynamic>(lookup)
        .AppendStage<CustomerDetailsModel>(group)
        .FirstOrDefaultAsync();
}

Code explained:

I'm first matching the provided customerId with the 'customers' collection

Then I $unwind (flatten) the customers. projects array so I can easily $lookup every matching instance

Now I'm using $lookup to left outer join the matching instances from the instances collection. The result of this stage is x (number of matching instances) documents and I need to merge them to a single document:

$group helps me to achieve this.

The way I figure it out is by doing the M121 course in the official MongoDB University (https://university.mongodb.com/) which I highly recommend.

If you have thought of how to improve the pipeline - I will be happy to know