[mongodb-dev] Complex join and filter on large tables

[Rajesh Soni]

This is related to join multiple collection using $lookup and filter using
$match

We have order product collection with following attribute:


orderProduct(base collection)

------------

_id,

productId,

qty,

retailerId,

supplierId

unitPrice

lineTotal



Product(dependent collection)

------------

_id,

productName

subCategoryId


Retailer(dependent collection)

------------

_id

retailerName


Supplier(dependent collection)

-------------

_id

supplierName


Now we need to have following joins and select attribute from both
dependent and base collection


orderProduct joins Product

orderProduct joins Retailer

orderProduct joins Supplier


Also need filter from both dependent and base collection.


*The basic issue with above setup is that orderProduct is growing
rapidly(current size is 90K) and $lookup is taking too much time. *Sorting/Limit/Skip
also needed here


Is there any elegant way to handle this?


Thanks in advance

['Wan Bachtiar' via mongodb-dev]

Hi Rajesh,

Questions related to the use of MongoDB (i.e. your question) are better
sent to mongodb-user group
<https://groups.google.com/forum/#!forum/mongodb-user>. This group
(mongodb-dev) is for discussion for/by/about developers/development/code of
MongoDB.

Is there any elegant way to handle this?

Data in MongoDB has a flexible schema, and this means that your data model
can be designed to benefit how your application(s) will operate on the
data. To get started, please review Data Model Design
<https://docs.mongodb.org/manual/core/data-model-design/>. It contains key
considerations for designing schemas, whether to embed
<https://docs.mongodb.org/manual/core/data-model-design/#data-modeling-embedding>
or to use references
<https://docs.mongodb.org/manual/core/data-model-design/#data-modeling-referencing>.
See also Operational factors and data models
<https://docs.mongodb.org/manual/core/data-model-operations/#operational-factors-and-data-models> for
considerations when designing data models.

For example, based on how your application interact with the data in the
database, consider whether you can embed suppliers information with
products.

You may also be interested in Model Specific Application Contexts
<https://docs.mongodb.org/manual/applications/data-models-applications/>. I
would also recommend to enrol in a free online course at MongoDB University
<https://university.mongodb.com/> to learn more about MongoDB. The M101
courses cover JSON, schema design, querying, insertion, etc.

If you have further questions, please post a new discussion on mongodb-user
group <https://groups.google.com/forum/#!forum/mongodb-user>.

Regards,
Wan.
​