Mongodb 使用嵌套查找聚合复杂文档 [英] Mongodb Aggregate Complex Document with Nested Lookups

问题描述

我有这些收藏:

网站

{
  _id: ObjectId("5acdb8f65ea63a27c1facf86"),
  TemplateId: ObjectId("sdfsdfs34234234sdf"),
}

模板

{
  _id: ObjectId("sdfsdfs34234234sdf"),
  Type: "Site",
  Name: "Site 1",
  Sections:[{
     id: ObjectId("8asdf89asd8f9sdf"),
     Header: "Header1",
     FieldItems: [
           {
            FieldId: ObjectId("jsd32423423423"),
            x: 1,
            y: 0
           },
           {
            FieldId: ObjectId("2342sdffafdasdfdfs"),
            x: 1,
            y: 1
           }
          ]
        },
       (...more sections...)
     ]
}

字段

{
  _id: ObjectId("jsd32423423423"),
  Type: "Phone",
  Name: "Phone_Test"
},
{
  _id: ObjectId("2342sdffafdasdfdfs"),
  Type: "Numeric",
  Name: "Number_Test"
}
            

我是 MongoDB 的新手，但花了几天时间阅读问题和答案以及文档.我正在使用 MongoDB 4.2.6.我正在尝试返回格式如下的结果:

I'm new to MongoDB, but have taken a couple of days reading through questions and answers as well as documentation. I'm using MongoDB 4.2.6. I'm trying to return a result formatted like this:

{
  id: ObjectId("5acdb8f65ea63a27c1facf86"),
  TemplateId: ObjectId("sdfsdfs34234234sdf"),
  Template: {
   id: ObjectId("sdfsdfs34234234sdf"),
   Type: "Site",
   Sections:[{
     id: ObjectId("8asdf89asd8f9sdf"),
     Header: "Header1",
     FieldItems: [
        {
          FieldId: ObjectId("jsd32423423423"),
          x: 1,
          y: 0,
          Field: {
              _id: ObjectId("jsd32423423423"),
              Type: "Phone",
              Name: "Phone_Test"
           }
        }, (...)]
      }]
}
  

我编写了一个带有嵌套查找的聚合查询，主要是为了让我在那里完成，但要让它工作，我不得不放松 Sections 和 FieldItems.我还没有想出一种方法来让数组达到我想要的方式.我试过 group 但在子数组上有问题.我什至不确定这是否是获得所需结果的最佳方式:

I've written an aggregate query with nested lookups to get me mostly there, but to get it to work I've had to unwind Sections and FieldItems. I haven't figured out a way to get the arrays to the way I'd like them. I've tried group but having issues with the sub arrays. I'm not even sure if this is the best way to get the results I need:

db.getCollection("AppSites").aggregate(
[
    { 
        "$lookup" : { 
            "from" : "AppTemplates", 
            "let": {"template_id": "$TemplateId"},
            "pipeline": [
                { "$match": { "$expr": { "$eq" : ["$_id", "$$template_id"] } } },
                { "$unwind": "$Sections"},
                { "$unwind": "$Sections.FieldItems"},
                {
                    "$lookup": {
                        "from": "AppFields",
                        "let": {"field_id": "$Sections.FieldItems.FieldId"},
                        "pipeline": [
                            { "$match": { "$expr": { "$eq": ["$_id", "$$field_id"] } } }
                        ],
                        "as": "Sections.FieldItems.Field"
                    }
                }
            ],
            "as" : "Templates"
        }
    }
]
);

推荐答案

你已经做了两次$unwind，所以你需要你使用两个$group.

You have done $unwind two times, So you need you use two $group.

{
  $group: {
    _id: {
      secId: "$_id",
      fId: "$Sections.id"
    },
    Type: {
      $first: "$Type"
    },
    Name: {
      $first: "$Name"
    },
    Header: {
      $first: "$Sections.Header"
    },
    fieldItems: {
      $push: "$Sections.FieldItems"
    }
  }
},
{
  $group: {
    _id: "$_id.secId",
    Type: {
      $first: "$Type"
    },
    Name: {
      $first: "$Name"
    },
    Sections: {
      $push: {
        id: "$_id.fId",
        Header: "$Header",
        fieldItems: "$fieldItems"
      }
    }
  }
}

1. 第一组 - 将子对象分组.但是Type、Name和Header需要分别设置为父数组和子数组.
2. 第二组 - 将父对象分组.我们在对孩子进行分组时获得每个独立的字段.在这里，我们只需要在正确的订单中进行设置.

1. First group - to group child objects. But Type,Name and Header need to be set to parent and child array respectively.
2. Second group - to group parent objects. We get every standalone fields while grouping child. Here just we need to set it up in correct oreders.

工作 Mongo 游乐场

注意:当你使用 $lookup 时，它会提供一个数组.但是有些地方你只是把它当作对象.我不知道你是否加入了一对一的关系.如果是这样，您可以在投影中使用 位置运算符 或 arrayElemAt

Note : When you use $lookup, it will provide an array. But there are some places you just make it as object. I don't know whether your joins in one-to-one relationship or not. If so you can use positional operator in projection or arrayElemAt

这篇关于Mongodb 使用嵌套查找聚合复杂文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！