Lookup in two collections with nested documents

Working with Data
, ,
1

Hi!

I have this scenario:

Doc01:

    {
      _id: "Unit123",
      name: "Test Unit",
      "sections": [
        {
        "_id": "63925553eeb147dc9bd894e1",
          "name": "TestSection 1",
          "contents": [
            {
              _id: "1",
              "type": "TEXT_PAGE",
              "pageTitle": "Lorem ipsum dolor sit amet, consectetur adipiscing elit..",
              "pageContent": "Lorem ipsum dolor sit amet, consectetur adipiscing elit."
            },
            {
              _id: "2",
              "type": "TEXT_PAGE",
              "pageTitle": "Lorem ipsum dolor sit amet, consectetur adipiscing elit..",
              "pageContent": "Lorem ipsum dolor sit amet, consectetur adipiscing elit."
            }
          ],
        },
      ],
    },

Doc02:

    {
     "_id": "63925553eeb147dc9bd894e3",
      unit: "Unit123",
      user: 3,
      lastContent: "2",
      progressions: [
        {
          _id: "1",
          isCompleted: true,
        },
        {
          _id: "2",
          isCompleted: true,
        },
      ]
    },

I want to achieve this structure:

    {
      _id: "Unit123",
      name: "Test Unit",
      "sections": [
        {
        "_id": "63925553eeb147dc9bd894e1",
          "name": "TestSection 1",
          "contents": [
            {
              _id: "1",
              "type": "TEXT_PAGE",
              "pageTitle": "Lorem ipsum dolor sit amet, consectetur adipiscing elit..",
              "pageContent": "Lorem ipsum dolor sit amet, consectetur adipiscing elit.",
              "contentProgression":  {
                  _id: "1",
                  isCompleted: true,
               },
            },
            {
              _id: "2",
              "type": "TEXT_PAGE",
              "pageTitle": "Lorem ipsum dolor sit amet, consectetur adipiscing elit..",
              "pageContent": "Lorem ipsum dolor sit amet, consectetur adipiscing elit.",
              "contentProgression":  {
                  _id: "2",
                  isCompleted: true,
               },
            }
          ],
        },
      ],
    },

I’m stucked in turning the sections and contents a group after the lookup.
This is my query:

db.units.aggregate([
  {
    "$unwind": "$sections"
  },
  {
    "$unwind": "$sections.contents"
  },
  {
    "$lookup": {
      "from": "unitProgressions",
      let: {
        contentsId: "$sections.contents._id"
      },
      pipeline: [
        {
          "$unwind": "$progressions"
        },
        {
          $match: {
            $expr: {
              $eq: [
                "$$contentsId",
                "$progressions._id"
              ]
            }
          }
        },
        
      ],
      "as": "sections.contents.unitProgression"
    }
  },
  {
    "$unwind": {
      path: "$sections.contents.unitProgression",
      "preserveNullAndEmptyArrays": true
    }
  },
  
])

I tried to use group but it only groups the parent fields.
Is it possible to build this structure? Is there a better way to organize this schemas? It feels that I’ll have problems with this query speed.

MongoDB Playground

Thanks!

2

I don’t really understand your $lookup. It looks wrong.

I do not see anywhere how you know which top level document from unitProgressions to use based on the top level document from units. From the sample documents it would appear that the field unit of unitProgressions, value Unit123 in the sample, is related to the _id of the document in the units collection. This is nowhere to be seen in your pipeline. You do match the _id of the sub-documents, but we do not know which top level document.

You also have a collection named users and from the fields of unitProgressions, it looks that a unitProgressions is specific to a given user. I do not see that in your lookup.

So what is the use-case exactly? I suspect that you want the progression of a specific user for a specific unit in a single document.

3

Inside the unit I have the sections and contents of it.
I want to connect the sections content with the progressions. Inside the progressions is only the contents progression.
You’re right the use case is: Get the Users progression of a Unit based on its contents.

4

It is still not clear. In Doc01, the field sections is an array. In your sample you only have 1 section but being an array I suspect that you might have more than one. Otherwise having an array is useless. It looks like in Doc02, you are missing a field that indicates which sections from Doc01.