$lookup inside nested arrays results in an unwind document

Working with Data
,
1

hi all,

I have the following school document from the Schools collection:

{
    "name": "school1",
    "sections": [
        {
            "name": "section1",
            "cabinets": [
                {
                    "name": "cabinet1",
                    "columns": [
                        {
                            "_id": "1"
                            "index": 1,
                        }
                    ]
                }
            ]
        }
    ]
}

and the following locker document from the Lockers collection.
each locker belongs to one specific column in a cabinet

         
  {
    "_id": "...",
    "columnId": "1"
  },
  {
    "_id": "...",
    "columnId": "1"
  },
  {
    "_id": "...",
    "columnId": "1"
  }

i want to query all schools and join each column with its corresponding lockers like so:

  {
    "name": "school1",
    "sections": [
      {
        "name": "section1",
        "cabinets": [
          {
            "name": "cabinet1",
            "columns": [
              {
                "_id": "1",
                "index": 1,
                "lockers": [
                  {
                    "_id": "...",
                    "columnId": "1"
                  },
                  {
                    "_id": "...",
                    "columnId": "1"
                  },
                  {
                    "_id": "...",
                    "columnId": "1"
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  }

i tried to achieve it using aggregate and $lookup, the thing is the results are unwind, meaning for each section, cabinet and column i get a separate school document.

how do i aggregate properly and retain the structure of school with all its arrays and still get for every column its corresponding lockers array

here is a sample of my invalid aggregation:
https://mongoplayground.net/p/CE-2YxHZWP8

thanks!

3

Hi @Roy_Yair and welcome to MongoDB community forums!!

From the aggregation query mentioned in the playground, the output returned is similar to a few fields extra fields displayed which could be skipped using the $project fields.
For example:

{
        '$project': {
            '_id': 0, 
            'sections.name': 0, 
            'sections._id': 0
 }

As you might already know, the query performance in MongoDB is highly dependent on the efficient data modelling.
In saying so, I would recommend changing the schema design to a more efficient design which would eliminate the use of multiple $unwind aggregation pipeline and produce the results.
If this aggregation would be used in a huge dataset which involves more traversal into the lower level may or may not impact the query performance.
Please visit the documentation on Data Modelling in MongoDB for further understanding.

Let us know if you have further questions.

Regards
Aasawari

1 Like
4

Hi Aasawari!

thanks for your reply,
I guess my modeling isn’t ideal, what would you suggest,
please consider I’m expecting a lot of writes to the locker collection. and i dont want write locks to affect other users so I have separated it from Schools collection.

do you think I should’ve put everything on the same document? or maybe take the opposite direction and separate each layer into its own collection?

anyhow, I still confused on how to properly join in mongo as you can see in the playground - it seem to flatten the array and turn arrays into objects

5

Hi @Roy_Yair

The locks in MongoDB are not specific to collection. The write conflict would occur when multiple process try to write onto the same document, so if a document contains reference for multiple entities, the lock is certainly inevitable.
You can read more about granularity using locks in MongoDB for further informations.

I think those are two extreme ends of possible designs. With MongoDB you have the flexibility of choosing a design that is in-between those two extremes.
To start with, I would suggest you reading the blog post on Extended Reference Pattern and Building with Patterns would be a good starting point to design the schema.

Let us know if you have further questions

Regards
Aasawari