Complex join and filter on large tables

19 views

Skip to first unread message

Rajesh Soni

unread,

Mar 5, 2018, 6:53:35 AM3/5/18

to mongodb-dev

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

unread,

Mar 27, 2018, 2:24:45 AM3/27/18

to mongodb-dev

Hi Rajesh,

Questions related to the use of MongoDB (i.e. your question) are better sent to mongodb-user group. 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. It contains key considerations for designing schemas, whether to embed or to use references. See also 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. I would also recommend to enrol in a free online course at MongoDB University 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.