时间戳

首页 > 解决方案 > MongoDB 数据聚合帮助

mongodb - MongoDB 数据聚合帮助

问题描述

我有以下文件的“客户”集合：

{
  id: 1,
  name: 'Customer Name',
  projects: [
    {
      id: 1000,
      name: 'Project 1',
      description: 'Project description',
      instances: [10, 20],
    },
    {
      id: 2000,
      name: 'Project 2',
      description: 'Project description',
      instances: [30, 40, 10],
    }
  ]
}

我有另一个集合“实例”，如下所示：

[
  {
    id: 10,
    operatingSystem: 'Microsoft Windows 2012R2',
    version: '3.1.5',
    product: {
      id: 100,
      name: 'Product 1',
      vendor: 'Vendor A',
    },
  },
  {
    id: 20,
    operatingSystem: 'Microsoft Windows 2016',
    version: '4.1.0',
    product: {
      id: 200,
      name: 'Product 5',
      vendor: 'Vendor B',
    },
  },
  {
    id: 30,
    operatingSystem: 'Microsoft Windows 2019',
    version: '3.0',
    product: {
      id: 300,
      name: 'Product 2',
      vendor: 'Vendor A',
    },
  },
  {
    id: 40,
    operatingSystem: 'Linux',
    version: '1.0',
    product: {
      id: 100,
      name: 'Product 1',
      vendor: 'Vendor A',
    }
  }
]

我正在尝试使用聚合框架使结果如下所示：

{
  id: 1,
  name: 'Customer Name',
  projects: [
    {
      id: 1000,
      name: 'Project 1',
      description: 'Project description',
      products: [
        {
          id: 100,
          name: 'Product 1',
          vendor: 'Vendor A',
          instances: [
            {
              id: 10,
              operatingSystem: 'Microsoft Windows 2012R2',
              version: '3.1.5',
            },
          ],
        },
        {
          id: 200,
          name: 'Product 5',
          vendor: 'Vendor B',
          instances: [
            {
              id: 20,
              operatingSystem: 'Microsoft Windows 2016',
              version: '4.1.0',
            },
          ],
        },
      ],
    },
    {
      id: 2000,
      name: 'Project 2',
      description: 'Project description',
      products: [
        {
          id: 300,
          name: 'Product 2',
          vendor: 'Vendor A',
          instances: {
            id: 30,
            operatingSystem: 'Microsoft Windows 2019',
            version: '3.0',
          },
        },
        {
          id: 100,
          name: 'Product 1',
          vendor: 'Vendor A',
          instances: [
            {
              id: 40,
              operatingSystem: 'Linux',
              version: '1.0',
            },
            {
              id: 10,
              operatingSystem: 'Microsoft Windows 2012R2',
              version: '3.1.5',
            }
          ]
        }
      ]
    }
  ]
}

我设法建立的当前管道是：

[{$match: {
  _id: 1
}}, {$unwind: {
  path: "$projects"
}
}, {$lookup: {
  from: 'instances',
  localField: 'projects.instances',
  foreignField: '_id',
  as: 'projects.instances'
}}, {$group: {
  _id: "$projects.instances.product",
  test: { "$push": "$$ROOT" }
}}, {$unwind: {
  path: "$_id"
}}, {$unwind: {
  path: "$test"
}}, {$project: {
  _id: "$test._id",
  name: "$test.name",
  description: "$test.description",
  projects: {
    _id: "$test.projects._id",
    name: "$test.projects.name",
    description: "$test.projects.description",
    products: {
      _id: "$_id._id",
      name: "$_id.name",
      vendor: "$_id.vendor",
      instances: "$test.projects.instances",
    }
  }
}}, {$group: {
  _id: "$_id",
  name: {"$first": "$name"},
  projects: {
    "$push": "$projects"
  }
}}]

但是我在“项目”数组中得到了重复项（如果我有相同的项目和不同的产品，它将显示两次，而不是在产品数组中有一个项目有 2 个产品

希望您能帮助我找到正确的管道阶段来操纵我的结果

标签： mongodbaggregation-framework

解决方案

我没有遵循您的管道，因为从头开始重写它对我来说更容易，但我会这样做，明显的数据结构操作概念保持不变：

db.customers.aggregate([
  {
    $match: {
      _id: 1
    }
  },
  {
    $unwind: "$projects"
  },
  {
    $lookup: {
      from: "instances",
      let: {
        instances: "$projects.instances"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $setIsSubset: [
                [
                  "$id"
                ],
                "$$instances"
              ]
            }
          }
        }
      ],
      as: "projects.instances"
    }
  },
  {
    $unwind: "$projects.instances"
  },
  {
    $group: {
      _id: {
        id: "$_id",
        project: "$projects.name",
        product: "$projects.instances.product.id"
      },
      name: {
        $first: "$name"
      },
      description: {
        $first: "$projects.description"
      },
      product: {
        $first: "$projects.instances.product"
      },
      instances: {
        $push: {
          id: "$projects.instances.id",
          operatingSystem: "$projects.instances.operatingSystem",
          version: "$projects.instances.version",
          
        }
      }
    }
  },
  {
    $group: {
      _id: {
        id: "$_id.id",
        project: "$_id.project"
      },
      name: {
        $first: "$name"
      },
      description: {
        $first: "$description"
      },
      products: {
        $push: {
          $mergeObjects: [
            "$product",
            {
              instances: "$instances"
            }
          ]
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id.id",
      name: {
        $first: "$name"
      },
      projects: {
        $push: {
          name: "$project_name",
          description: "$description",
          products: "$products"
        }
      }
    }
  }
])

Mongo游乐场

推荐阅读