MongoDB nested lookup with 3 child levels

Viewed 5501

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! :)

1 Answers
Related