时间戳

首页 > 解决方案 > MongoDB查询从嵌套数组中过滤子文档

mongodb - MongoDB查询从嵌套数组中过滤子文档

问题描述

鉴于此嵌入式文档结构：

organisation
  district
    property
      installation 
        installation_type

我希望只检索安装类型 = 例如“燃气锅炉”的属性

我试过了 ：

db.organisation.find({ "district.property.installation.installation_type": { $all: ['Gas boiler'] }})

但这会为任何拥有燃气锅炉的组织返回整个文档。这意味着我也得到了没有燃气锅炉的房产。我想要一个只有“燃气锅炉”的属性的列表

示例文档：

db.organisation.insert({
  _id: 'Organsiation 1',
  district: [
  {
    _id: 'District 1',
    property: [
      {
        _id: 'Property 1',
        address_line_1: 'Adress 1',
        installation: [
          {
            _id: 'HS01',
            installation_type: 'Gas boiler',
            component: [
              {
                _id: 'P01',
                component_type: 'Circulation pump',
              },
              {
                _id: 'EXP01',
                component_type: 'Expansion tank',
              },
            ],
          },
          {
            _id: 'HW01',
            installation_type: 'Electric water heater',
            component: [
              {
                _id: 'P01',
                component_type: 'Circulation pump',
              },
            ],
          },
          {
            _id: 'V01',
            installation_type: 'Ventilation',
          }
        ]
      },
    ]
  },
  {
    _id: 'District 2',
    property: [
      {
        _id: 'Property 2',
        address_line_1: 'Adress 2',
        installation: [
          {
            _id: 'HS01',
            installation_type: 'Geo Heat Pump',
          },
          {
            _id: 'HS02',
            installation_type: 'Gas boiler',
          }
        ]
      },
      {
        _id: 'Property 3',
        installation: [
          {
            _id: 'HS01',
            installation_type: 'Gas boiler',
          } 
        ]
      },
    ],
  }
]
})

我该怎么做呢？

标签： mongodbmongodb-queryaggregation-framework

解决方案

使用这个多个嵌套数组，您可以$unwind使用或使用如下过滤器，但使用$unwind会爆炸带有大量文档的集合，因此这样做应该可以工作，因为它应该在$addFields小于原始集合大小的文档大小上运行：

db.collection.aggregate([
    /** This match can be optional if dealing with data set of small size, else it can help a lot to filter docs which has at-least 'Gas boiler' */
    {
      $match: {
        "district.property.installation.installation_type": "Gas boiler"
      }
    },
    /** Iterate over each array till property array but on installation use filter to filter only object matches with required criteria,
     *  Use mergeObjects to merge actual object with returned array, to preserve other fields apart from `property` & `installation` */
    {
      $addFields: {
        district: {
          $map: {
            input: "$district",
            as: "d",
            in: {
              $mergeObjects: [
                "$$d",
                {
                  property: {
                    $map: {
                      input: "$$d.property",
                      as: "p",
                      in: {
                        $mergeObjects: [
                          "$$p",
                          {
                            installation: {
                              $filter: {
                                input: "$$p.installation",
                                cond: {
                                  $eq: [
                                    "$$this.installation_type",
                                    "Gas boiler"
                                  ]
                                }
                              }
                            }
                          }
                        ]
                      }
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  ])

测试： MongoDB-游乐场

推荐阅读