MongoDB nested lookup with 3 child levels

Question

I need to retrieve the entire single object hierarchy from the database as a JSON. I'm trying aggregate for hours and can't solve how to do it with my data. So I have three collections:

form

{ "_id" : "1", "name" : "My first form" }
{ "_id" : "2", "name" : "Second one" }
{ "_id" : "3", "name" : "Another" } 

question

{ "_id" : "q1", "form" : "1", "title": "What's your country?"}
{ "_id" : "q2", "form" : "1", "title": "What your favorite color?"}
{ "_id" : "q3", "form" : "1", "title": "Where do you live?"}
{ "_id" : "q4", "form" : "2", "title": "Where to go?"}

option

{ "_id" : "o1", "question" : "q1", "text" : "Brazil" }
{ "_id" : "o2", "question" : "q1", "text" : "EUA" }
{ "_id" : "o3", "question" : "q1", "text" : "China" }
{ "_id" : "o4", "question" : "q2", "text" : "Red" }
{ "_id" : "o5", "question" : "q2", "text" : "Blue" }
{ "_id" : "o6", "question" : "q2", "text" : "Green" }

I need to retrieve each form with all corresponding questions, and in each question the options for it. Like this:

[
   {
      _id:"q1",
      name: "My first form",
      questions: [
          { "_id" : "q1",
            "form" : "1", 
            "title": "What's your country?",
            "options": [
                  { "_id" : "o1", "question" : "q1", "text" : "Brazil" }
                  { "_id" : "o2", "question" : "q1", "text" : "EUA" },
                  { "_id" : "o3", "question" : "q1", "text" : "China" }
            ]
          },
          { "_id" : "q2",
            "form" : "1", 
            "title": "What your favorite color",
            "options": [
                  { "_id" : "o4", "question" : "q2", "text" : "Red" }
                  { "_id" : "o5", "question" : "q2", "text" : "Blue" },
                  { "_id" : "o6", "question" : "q2", "text" : "Green" }
            ]
          },
          { "_id" : "q3", 
            "form" : "1", 
            "title": "Where do you live?",
            "options": []
          }
      ]
   },
   ...
]

I've tried a lot of $lookup, $unwind, another $lookup and $project, but nothing give-me that result (forms with questions inside, questions with options inside).

Please, help me! :)

Answer 1

I think the around is querying the question collection, looking up their questions and grouping by form, and finally lookup form and project, in that order.

This should do it. Keep in mind that _id in the output of this aggregate is the form _id.

db.question.aggregate([
    {$match: {}},
    {$lookup: {
        from: 'option',
        localField: '_id',
        foreignField: 'question',
        as: 'options'
    }},
    {$group: {
        _id: "$form",
        questions: {$push: {
            title: "$title",
            options: "$options",
            form: "$form"
        }}
    }},
    {$lookup: {
        from: 'form',
        localField: "_id",
        foreignField: "_id",
        as: 'form'
    }},
    {$project: {
        name: {$arrayElemAt: ["$form.name", 0]},
        questions: true
    }}
]);

Actually.. this seems like a better alternative. It will return forms with no questions too.

db.form.aggregate([
    {$match: {}},
    {$lookup: {
        from: 'question',
        localField: '_id',
        foreignField: 'form',
        as: 'questions'
    }},
    {$unwind: {
        path: "$questions",
        preserveNullAndEmptyArrays: true
    }},
    {$lookup: {
        from: 'option',
        localField: 'questions._id',
        foreignField: 'question',
        as: 'options'
    }},
    {$group: {
        _id: "$_id",
        name: {$first: "$name"},
        question: {$push: {
            title: "$questions.title",
            form: "$questions.form",
            options: "$options"
        }}
    }}
])