Multiple lookup stages vs multiple separated find queries

Working with Data Node.js Frameworks
, ,
1

I have these collections:
Recordings: { _id, call }
Calls: { _id, department }
Departments: { _id, settings }
Settings: { _id, …settingsData }

I need to retrieve many recordings and attach the corresponding “setting” to each one. Considering that the collections “Recordings” and “Calls” are extensive, and the matching recordings (starting point) will also be a very large amount of documents, what’s the best approach?

  • Nest several lookups (at least 3 to get from recordings to settings) but having everything in one query
  • Request multiple queries to DB, one to populate the department, and another to get the specific setting per department (considering this will require merging the information in execution time afterward)
2

Hello @Nohelia_Rincon, Welcome back!

I suggest reading about the difference between embedding and referencing. Here are a few resources:

In my opinion, embedding the Department and Setting objects in the Recordings and Calls documents can help avoid joins in your main query.

However, you must handle cases where the Department or Setting updates you must execute queries to update the embedded objects in the Recordings and Calls collections.

1 Like
3

Please can you tell us

Am I forret thatyou want - for each (or at least many) Recordings to:

Fetch the ‘Call’ foe that recordont
Fetch the Department for that Call
Fetch the Settings for that departments

What is the cardinality between these? What are the typical document sizes? Are Raltionships 1 to Many or Many to Many?

1 Like
4

Also how frequently do you need to do this, and how quickly?

1 Like
5

Thanks! The thing is, the model is already created and I have no possibility to change it. So I have to work with what I currently have

1 Like
6

Okay,

If the total number of documents in the Department and Settings is less or the size is less then you can fetch them all together through a separate query and merge it with the respective Recordings and Calls. This is better than the first option (nested lookup) if you are fetching large amounts of documents from Recordings and Calls.

3 Likes
7

I actually only need to do this once or maybe twice, I think I’ll take recommendation from @turivishal since Calls is a very large collection but Departments and Settings are not, and most of them are repeated across Calls. Thank you both!

3 Likes