MongoDB三级嵌套查询

Question

MongoDB三级嵌套查询

8

我需要将整个单一对象层级以JSON格式从数据库中检索出来。我已经尝试了几个小时的聚合操作，但是无法解决如何处理我的数据。因此，我有三个集合：

表单

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

问题

{ "_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?"}

选项

{ "_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" }

我需要检索每个表单及其所有相关问题，以及每个问题的选项。就像这样：

[
   {
      _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": []
          }
      ]
   },
   ...
]

我尝试了很多$lookup、$unwind、另一个$lookup和$project，但没有什么可以给我那个结果（包含问题的表格，问题中包含选项）。

请帮帮我！ :)

- Tiago Gouvêa

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- martskins · Accepted Answer

我认为这段代码正在查询question集合，查找它们的question并按form分组，最后按照顺序查找表格和项目。

应该没问题。请注意，这个聚合输出中的_id是表格的_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
    }}
]);

实际上，这似乎是更好的选择。它将返回没有问题（question）的表单（form）。

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