MongoDB: aggregate lookup in deeply nested array of objects

Question

I have two collections:

Audios

[
       {
        "_id": "5f6b1a2e69eef14818ca03a7",
        "audioURL": "https://boyd.org",
        "state": "draft",
       }
       {
        "_id": "5f6b1e1c6297f34bc6f1fee3",
        "name": "navigate",
        "audioURL": "http://mariane.org",
        "state": "draft",
       },
       {
        "_id": "5f5b5423ba39f738d593b504",
        "audioURL": "https://storage/test123mp31599820832928.mp3",
        "state": "draft",
       }
]

Lessons:

 "lessons": [
  {
   "_id": "5f770e275cef7a611f3cf931",
   "minigameDescriptions": [
    {
     "assessmentSections": [
      {
       "mark": "step_1",
       "start": 1,
       "end": 2,
       "stepAudio": {
        "backingTrackAudioId": "5f6b1a2e69eef14818ca03a7",
        "previewTrackAudioId": "5f6b1a2e69eef14818ca03a7"
       }
      },
      {
       "mark": "step_2",
       "start": 3,
       "end": 6,
       "stepAudio": {
        "backingTrackAudioId": "5f6b1e1c6297f34bc6f1fee3",
        "previewTrackAudioId": "5f6b1e1c6297f34bc6f1fee3"
       }
      },
      {
       "mark": "step_3",
       "start": 7,
       "end": 10,
       "stepAudio": {
        "backingTrackAudioId": "5f6b1a2e69eef14818ca03a7",
        "previewTrackAudioId": "5f5b5423ba39f738d593b504"
       }
      }
     ],
     "drumStyleName": "DrumStyleHalftimeShuffle",
     "onboarding": false,
     "showHints": false,
     "minigameType": "StrummingTrainer",
    }
   ],
   "_updated_at": "2020-10-02T12:00:23.848Z",
  }
 ]
}

Now I have to aggregate that because in every mingameDescriptions.assessmentSections there is property stepAudio that has previewTrackAudioId and backingTrackAudioId. These references to collection "audios" and I need to join them there as objects (audio primary key is _id). I've tried following pipeline (right now I only try to lookup for previewTrackAudio ):

{
        $unwind: {
          path: "$minigameDescriptions.assessmentSections",
          preserveNullAndEmptyArrays: true
        },
      },
      {
        $lookup: {
          from: "audios",
          let: {
            audioId: "$minigameDescriptions.assessmentSections.stepAudio.previewTrackAudioId"
          },
          pipeline: [
            {
              $match: {
                $expr: {
                  $eq: ["$_id", "$$audioId"]
                }
              }
            }
          ],
          as: "minigameDescriptions.assessmentSections.stepAudio.previewTrackAudio"
        }
      },
      {
        $unwind: {
          preserveNullAndEmptyArrays: true,
          path: "$minigameDescriptions.assessmentSections.stepAudio.previewTrackAudio"
        }
      }

And it "kinda" works, I get following result:

{
 "lessons": [
  {
   "_id": "5f770e275cef7a611f3cf931",
   "minigameDescriptions": [
    {
     "assessmentSections": {
      "mark": "step_2",
      "start": 3,
      "end": 6,
      "stepAudio": {
       "backingTrackAudioId": "5f6b1e1c6297f34bc6f1fee3",
       "previewTrackAudioId": "5f6b1e1c6297f34bc6f1fee3",
       "previewTrackAudio": {
        "_id": "5f6b1e1c6297f34bc6f1fee3",
        "name": "navigate",
        "audioURL": "http://mariane.org",
        "state": "draft",
       }
      }
     },
     "drumStyleName": "DrumStyleHalftimeShuffle",
     "onboarding": false,
     "showHints": false,
     "minigameType": "StrummingTrainer",
    },
    {
     "assessmentSections": {
      "mark": "step_3",
      "start": 7,
      "end": 10,
      "stepAudio": {
       "backingTrackAudioId": "5f6b1a2e69eef14818ca03a7",
       "previewTrackAudioId": "5f5b5423ba39f738d593b504",
       "previewTrackAudio": {
        "_id": "5f5b5423ba39f738d593b504",
        "audioURL": "https://storage/test123mp31599820832928.mp3",
        "state": "draft",
       }
      }
     },
     "drumStyleName": "DrumStyleHalftimeShuffle",
     "onboarding": false,
     "showHints": false,
     "minigameType": "StrummingTrainer",
    },
    {
     "assessmentSections": {
      "mark": "step_1",
      "start": 1,
      "end": 2,
      "stepAudio": {
       "backingTrackAudioId": "5f6b1a2e69eef14818ca03a7",
       "previewTrackAudioId": "5f6b1a2e69eef14818ca03a7",
       "previewTrackAudio": {
        "_id": "5f6b1a2e69eef14818ca03a7",
        "audioURL": "https://boyd.org",
        "state": "draft",
       }
      }
     },
     "drumStyleName": "DrumStyleHalftimeShuffle",
     "onboarding": false,
     "showHints": false,
     "minigameType": "StrummingTrainer",
    }
   ],
   "_updated_at": "2020-10-02T12:00:23.848Z",
  }
 ]
}

But now instead of having an array with 3 mingameDescriptions.assessmentSections I have an array with three objects that have assesmentSections along with duplicate properties from minigameDescriptions which is wrong :/. How can I fix this?

What I would like to get is:

 "lessons": [
  {
   "_id": "5f770e275cef7a611f3cf931",
   "minigameDescriptions": [
    {
     "assessmentSections": [
      {
       "mark": "step_1",
       "start": 1,
       "end": 2,
       "stepAudio": {
        "backingTrackAudioId": "5f6b1a2e69eef14818ca03a7",
        "previewTrackAudioId": "5f6b1a2e69eef14818ca03a7",
        "previewTrackAudio": {
         "_id": "5f6b1a2e69eef14818ca03a7",
         "audioURL": "https://boyd.org",
         "state": "draft",
        }
       }
      },
      {
       "mark": "step_2",
       "start": 3,
       "end": 6,
       "stepAudio": {
        "backingTrackAudioId": "5f6b1e1c6297f34bc6f1fee3",
        "previewTrackAudioId": "5f6b1e1c6297f34bc6f1fee3",
        "previewTrackAudio": {
          "_id": "5f5b5423ba39f738d593b504",
          "audioURL": "https://storage/test123mp31599820832928.mp3",
          "state": "draft",
         }
       }
      },
      {
       "mark": "step_3",
       "start": 7,
       "end": 10,
       "stepAudio": {
        "backingTrackAudioId": "5f6b1a2e69eef14818ca03a7",
        "previewTrackAudioId": "5f5b5423ba39f738d593b504",
        "previewTrackAudio": {
         "_id": "5f5b5423ba39f738d593b504",
         "audioURL": "https://storage/test123mp31599820832928.mp3",
         "state": "draft",
        }
       }
      }
     ],
     "drumStyleName": "DrumStyleHalftimeShuffle",
     "onboarding": false,
     "showHints": false,
     "minigameType": "StrummingTrainer",
    }
   ],
   "_updated_at": "2020-10-02T12:00:23.848Z",
  }
 ]
}

You need to post both collections. And don't post unnecessary fields which make uncomfortable to others while looking into it — varman
– varman, Commented Oct 3, 2020 at 3:50
Right, I've posted both collections and removed the unnecessary fields. — TheUnderScorer
– TheUnderScorer, Commented Oct 3, 2020 at 6:01

varman · Accepted Answer · 2020-10-03 11:05:40Z

What you have tried was correct.

[
  {
    $unwind: "$minigameDescriptions"
  },
  {
    $unwind: "$minigameDescriptions.assessmentSections"
  },
  {
    $lookup: {
      from: "Audios",
      let: {
        audioId: "$minigameDescriptions.assessmentSections.stepAudio.previewTrackAudioId"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$_id",
                "$$audioId"
              ]
            }
          }
        }
      ],
      as: "minigameDescriptions.assessmentSections.stepAudio.previewTrackAudio"
    }
  },
      {
    $group: {
      _id: {
        _id: "$_id",
        /**   assId: "$minigameDescriptions.assessmentSections.mark"*/
        
      },
      assessmentSections: {
        $addToSet: "$minigameDescriptions.assessmentSections"
      },
      drumStyleName: {
        $first: "$minigameDescriptions.drumStyleName"
      },
      minigameType: {
        $first: "$minigameDescriptions.minigameType"
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      minigameDescriptions: {
        $addToSet: {
          assessmentSections: "$assessmentSections",
          drumStyleName: "$drumStyleName",
          minigameType: "$minigameType"
        }
      }
    }
  }
]

Working Mongo playground

Note: Here we have done two unwinds, so better to perform to group operation. Carefule, I have commented assessmentId in first group which is not present in your document. But better if have anything to identify each element in assessmentSections which helps not to be complicated

It's almost what I need, thanks! I have edited the post to add what response I need, since in your solution I only get the assessmentSections, and the remaining properties from minigameDescriptions are missing. Can you help me with that?
I've updated my answer I've done for few fields, you can follow same way

Collectives™ on Stack Overflow

MongoDB: aggregate lookup in deeply nested array of objects

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related