Multiple lookup in aggregate

[david_jk]

I have 4 collections, I want data from this collection for my Cart Page

1. User
2. Items
3. ZipCode
4. Provider

Here is the model of my collections
UserCollection : { _id :1, cart:[{ itemsId:10 }], zipcode:{ userZipCodeId: 0000 } }

ItemsCollection: [{ _id: 10, title: itemstitle }]

ZipCodeCollection:[{ _id: 0000, placeName: NY,serviceProviders:[100,101,102] }]

Providers :[ { _id: 100, title: Happy Store},{ _id: 101, title: ABC Store}]

On my cart page, I want user cart Items data, check user Zipcode and search from ZipCodeCollection which are the service providers in the zip code, then get the provider info from the Provider collection

here is my code

 			 User.aggregate([
 			 	{ $match: { _id: req.user._id } },

 			 	{
 			 		$lookup:
 			 		{
		 			 	from: 'ItemsCollection',
		 			 	localField: 'cart',
		 			 	foreignField: '_id',
		 			 	as : "cartList"
	 				},
	 			},


				 {

 			 		$lookup:
 			 		{
		 			 	from: 'zipcode',
		 			 	localField: 'zipcode.userZipCodeId',
		 			 	foreignField: '_id',
		 			 	as : "zipcodeList"

	 				},	 	
	 			},

	 			{
 			 		$lookup:
 			 		{
		 			 	from: 'providers',
		 			 	localField: 'zipcodeList.providers',
		 			 	foreignField: '_id',
		 			 	as : "providersList"

	 				},		 				
	 			},
	
				{"$match":{"providersList.status": true }},
				{
					$project:
					{
						_id:1,
						pincode:1,
						email:1,
						name:1,
						cartList:1,
						zipcodeList:1,
						cartList:1

					}
				}	

here it’s working, but take more time ( more than seconds )to execute I am afraid about the performance and bad design, how to increase this code performance and design well

[MaBeuLux88 (Maxime Beugnet)]

Hi @david_jk and welcome in the MongoDB Community !

Congratulation for finding and trying to eradicate─before it actually happened─the MongoDB #1 trap !

You actually answered your problem yourself: it’s a terrible data model and this will result in terrible performance.

The only way to fix this is to fix the data model.

Data Modeling Introduction — MongoDB Manual

I highly recommend that you take the time to read the above doc and especially try to check the 2 talks at the bottom + the white paper.

Your current data model looks like an SQL design. In MongoDB, data that is read together, should be stored together so $lookup is actually a very last resort.

In your case here, Users and Items are most probably the only 2 real collections. The 2 others should be embedded and the entries eventually be duplicated across the different documents.

Also, there are a few design pattern that could be helpful.

For this, I recommend you have a look to:

• Building with Patterns: A Summary | MongoDB Blog
• Building with Patterns: A Summary | MongoDB Blog

And in your case, I think you could benefit from the Extended Reference Pattern:

In your user cart, you would store the list of items IDs and quantities, but probably also a few other fields like the price, label and description for example.
So for example, when you open the cart in your app, you don’t need to $loopup the items because all the information you need is already in your user cart items. If the user clicks on an item to get more details about it (length, height, weight, technical specs…), then you can fetch directly (findOne) the details of that item. In the end, you might not even need $lookup at all.

A comment though… Usually the carts are stored in a separated collection so they can benefit from a TTL index (cart expiration 30 min).

Nothing of what I have said here is the absolute and unique truth. It’s all about your use cases and queries. Find these first. They will help design your documents.

A quick example. Let’s say I have a collection of books and authors.
If I’m a bookstore, then authors should be embedded in books because my queries are all manipulating books before anything else.
If I’m creating Wikipedia for famous authors. Then it’s the opposite. Books should be embedded in my authors documents because they are just a detail in this system.
And depending on the cardinality of both, it could also be a valid choice to keep them separated and maintain mono or bi-directional references.

Schema design is an art . The reward is premium performances.

Cheers,
Maxime.

[JulioW (Júlio César Wandekoken)]

Thanks for the reply and the resources links. I am facing the same issue and will benefit from your answer.

[MaBeuLux88 (Maxime Beugnet)]

Hi @JulioW and welcome in the MongoDB Community !

I’m glad that I was able to help you!
It’s the entire point of having a forum like this. We are building a giant knowledge base and hopefully by saving one, we can help many.

Cheers,
Maxime.