C# Lookup with additional Join condition

[JAReyes (José Antonio Reyes)]

Hi,

We need to perform a $lookup operation between two collections but also filter with another field from the foreign collection.

Let’s say we have a products collection like this:

{
    "_id": ObjectId("6231f79ad66270b351d58619"),
    "type": 1,
    "title": "Product 1",
    "date": "2022-03-16T14:41:47.689Z",
    "providers": [{
        "_id": UUID('be0d1637-6fac-4b2d-8a55-fb619c54e482'),
        "email": "provider1@mail.com"
    }]
},
{
    "_id": ObjectId("62389478a2f52569b8a0a688"),
    "type": 1,
    "title": "Product 2",
    "date": "2022-03-15T16:15:27.568Z",
    "providers": [{
        "_id": UUID('c9ffa5f0-7868-4395-83ac-3435905474ea'),
        "email": "provider1@mail.com"
		},
		{
        "_id": UUID('be0d1637-6fac-4b2d-8a55-fb619c54e482'),
        "email": "provider2@mail.com"
    }]
}

And another orders collection like this:

{
    "_id": ObjectId("6234c9b3e7311dd0c7936189"),
    "productId": ObjectId("6231f79ad66270b351d58619"),
    "userId": UUID('be0d1637-6fac-4b2d-8a55-fb619c54e482'),
    "orderDate": "2022-03-18T18:04:35.160Z"
},
{
    "_id": ObjectId("62388c81a2f52569b8a0a687"),
    "productId": ObjectId("6231f79ad66270b351d58619"),
    "userId": UUID('c9ffa5f0-7868-4395-83ac-3435905474ea'),
    "orderDate": "2022-03-21T12:24:32.461Z"
}

We can execute this from the Mongo Shell, and this works:

db.products.aggregate( [ 
 {
      $lookup:
         {
           from: "orders",
           let: { product_id: "$_id" },
           pipeline: [
              { $match:
                 { $expr:
                    { $and:
                       [
                         { $eq: [ "$productId",  "$$product_id" ] },
                         { $eq: [ "$userId", UUID('be0d1637-6fac-4b2d-8a55-fb619c54e482') ] }
                       ]
                    }
                 }
              },
              { $project: { orderDate: 0, _id: 0 } }
           ],
           as: "ordersdata"
         }
    }
] )

But if we try to create the aggregation pipeline from our backend side, it doesn’t work:

private IEnumerable<ProductsModel> GetPagedByUser(MongoPager pager, string userId)
{
	if (pager == null)
		throw new ArgumentNullException(nameof(pager));

	var uId = new Guid(userId);

	var lookupPipeline = new EmptyPipelineDefinition<Orders>()
		.Match(new BsonDocument("$expr",
			new BsonDocument("$and", new BsonArray
			{
				new BsonDocument("$eq", new BsonArray { "$productId", "$$product_id" }),
				new BsonDocument("$eq", new BsonArray { "$userId", $"UUID('{userId}')" })
			})))
		.Project<Orders, Orders, Orders>(
			Builders<Orders>.Projection
				.Exclude(order => order.OrderDate)
				.Exclude(order => order.Id));

	var userProducts = _mongoProductsCollection.Aggregate()
		.Match(product => product.Providers.Any(provider => provider.Id == uId))
		.Sort(pager.SortFilter)
		.Skip(pager.Skip)
		.Limit(pager.PageSize)
		.Lookup(
			_mongoOrdersCollection,
			new BsonDocument { { "product_id", "$_id" } },
			lookupPipeline,
			new ExpressionFieldDefinition<ProductsModel, IEnumerable<Orders>>(product => product.Orders))
		.ToList();

	return userProducts;
}

The thing is, we think, because the lookupPipeline expression creates the userId with double quotes:

{ "userId" : "UUID('be0d1637-6fac-4b2d-8a55-fb619c54e482')" }

Instead of without them, such as running from the Mongo Shell:

{ "userId" : UUID('be0d1637-6fac-4b2d-8a55-fb619c54e482') }

Any ideas on how to solve this problem, or other possible solutions?

Thanks.

[JAReyes (José Antonio Reyes)]

For those interested in, I was able to solve it in this way:

• First, in the lookup pipeline definition:

var lookupPipeline = new EmptyPipelineDefinition<Orders>()
	.Match(new BsonDocument("$expr",
		new BsonDocument("$and", new BsonArray
		{
			new BsonDocument("$eq", new BsonArray { "$productId", "$$product_id" }),
			new BsonDocument("$eq", new BsonArray { "$userId", "$$ws_user_id" })
		})))
	.Project<Orders, Orders, Orders>(
		Builders<Orders>.Projection
			.Exclude(order => order.OrderDate)
			.Exclude(order => order.Id));

• And then, use the new BsonBinaryData in the let parameter of the Lookup function:

var userProducts = _mongoProductsCollection.Aggregate()
	.Match(product => product.Providers.Any(provider => provider.Id == uId))
	.Sort(pager.SortFilter)
	.Skip(pager.Skip)
	.Limit(pager.PageSize)
	.Lookup(
		_mongoOrdersCollection,
		new BsonDocument { { "product_id", "$_id" }, { "ws_user_id", new BsonBinaryData(uId, GuidRepresentation.Standard) } },
		lookupPipeline,
		new ExpressionFieldDefinition<ProductsModel, IEnumerable<Orders>>(product => product.Orders))
	.ToList();

Hope this helps.

Regards.