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

martskins · Accepted Answer · 2017-07-11T18:28:37.763

9

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"
        }}
    }}
])

edited Jul 11 '17 at 18:28

answered Jul 11 '17 at 17:49

martskins

2,920
4
26
52

How is it not working for you? I made a few tweaks to it just mins ago. – martskins Jul 11 '17 at 18:04
I got your last edit. I saw it. The structure of you result, are right. But it don't got data. Take a look at the results here: https://pastebin.com/qddteCTq – Tiago Gouvêa Jul 11 '17 at 18:08
I can see an issue with it, and that it is that if the form has no questions it won't be in the results, but I created the collections you gave as example, and I got this result: https://pastebin.com/0DqFq8LS – martskins Jul 11 '17 at 18:11
Sorry! It work! I just miss that you are querying "questions" collection. It work fine. Thanks! You save my day. – Tiago Gouvêa Jul 11 '17 at 18:16
Remember that it won't return forms with no questions. I don't know if that's an issue though. – martskins Jul 11 '17 at 18:20
1

@TiagoGouvêa check the updated answer, that second aggregate seems like a much better alternative – martskins Jul 11 '17 at 18:31
it work well to! Thanks! Now, I must study more to understand how it really works. Great job! :) – Tiago Gouvêa Jul 11 '17 at 18:38

MongoDB nested lookup with 3 child levels

1 Answers1