MongoDB lookup on an array of objects which contains (foreign) ObjectId as a key

If you have worked with MongoDB it's highly probable that you have used aggregation pipelines. And its a very common use-case that you need to connect documents from one collection to another, aka 'The Join Query' from our SQL days.

Let's elaborate the problem statement first. We have 2 collections, orders and products.
Every order has some user related information and a list of products which the user has ordered along with its quantity.

sample order object

sample product object

The result we want from aggregation

Here you can see that we have inflated the product details inside the products array.
At the time of writing this article, I couldn't find any sweet and easy way of achieving this, so I decided to dive in a little deeper.

A point to note here, if the product array would have been something like products: [ObjectId(...), ObjectId(...)] then it would be a breeze. But since we also need to accommodate quantity alongside the productId, it's not quite feasible (at least with the current schema).

So, let's get started with the solution.

Step 1: Split the array

Use the $unwind operator to split the products array so that each document formed will have only one product instead of the entire array.

Now each of our unwound orders would looks something like this

This looks simpler, but wait, where are the rest of the ordered products?
It turns out, using $unwind would split one document into multiple documents where all the data is identical except the one list on which $unwind is called.
Here, it will split our sample document into 2 documents like the following

So, I hope it's clear that, if you have n orders with m products in each, it will generate a total m*n documents.

Step 2: Inflate product details

This is the important step, fetching product details from another collection

this step inflates the data and makes it look something like the following

If you notice one thing, the product is inflated, but its an array instead of an object. The reason being, mongodb expects that multiple documents in the products table may match with this criteria, and hence by default puts all the docs in an array. In our case we know its going to be unique, so we can just unwind it out.

Step 2a: Unwind the product inflated

Cool! we got the data we needed. Now we can merge those docs up to make it into original.

Step 3: Merging the docs

the key for this step is $group. we will push all the products into an array.

But, here is a small catch!
For grouping them together, you need to preserve all the other fields as well. like userId, createdAt and _id.
One way is, put everything in _id of your group, something like

BUT...
there are 3 shortcomings

1. Difficult to execute if docs have different keys.
2. High chances of forgetting/mistyping any field
3. If the schema is altered even slightly, you need to go through the pipeline and rewrite this part again.

As a typical engineer who believes in writing 20 smart lines to avoid hardcoding 5 lines, I came up with a way to make this bit future proof.

The key idea is, merge on the basis of _id alone, and then lookup itself to get a fresh copy of the doc.

Step 3a: Join on basis of _id

Here, I am pushing elements to products after picking them from products, it might sound confusing, but what it does is that, it takes all of the products from existing docs, put them in an array and then call it products in the end.

Step 3b: Fetch a fresh copy of orders for regeneration

This is pretty straightforward, shouldn't need much explaining. At this point the doc looks something like this

I know it looks weird, don't worry. We have some beautification left.

Step 3c: Beautify and reshape the entire doc

All we need to do is put all the stuff in their proper places.
We shall do it in 2 steps.

1. Put the products: {} from root to orderDetails.products.
2. Make the orderDetails as the root doc

Something like this

This one takes the contents inside orderDetails and make it as the root document.

And here we have our required order object with inflated product details!

The complete aggregation looks something like this