Mongodb 结合聚合查询 [英] Mongodb combine aggregate queries

问题描述

我在 MongoDB 中有以下集合

I have following collections in MongoDB

个人资料集

> db.Profile.find()
{ "_id" : ObjectId("5ec62ccb8897af3841a46d46"), "u" : "Test User", "is_del": false }

商店收藏

> db.Store.find()
{ "_id" : ObjectId("5eaa939aa709c30ff4703ffd"), "id" : "5ec62ccb8897af3841a46d46",  "a" : { "ci": "Test City", "st": "Test State" }, "ip" : false }, "op" : [ ], "b" : [ "normal" ], "is_del": false}

物品收藏

> db.Item.find()
{ "_id" : ObjectId("5ea98a25f1246b53a46b9e10"), "sid" : "5eaa939aa709c30ff4703ffd", "n" : "sample", "is_del": false}

这些集合之间的关系定义如下:

Relation among these collections are defined as follows:

1. 简介 ->Store:是1:n关系.Store 中的 id 字段与 Profile 中的 _id 字段相关.
2. Store ->Item:也是1:n关系.Item 中的 sid 字段与 Store 中的 _id 字段相关.

1. Profile -> Store: It is 1:n relation. id field in Store relates with _id field in Profile.
2. Store -> Item: It is also 1:n relation. sid field in Item relates with _id field in Store.

现在，我需要编写一个查询来查找所有配置文件存储以及每个存储的 Item 计数.必须排除 is_del 为 true 的文档.

Now, I need to write a query to find the all the store of profiles alongwith their count of Item for each store. Document with is_del as true must be excluded.

我正在尝试以下方式:

1. 查询 1 以查找每个商店的商品数量.
2. 查询 2 以查找每个配置文件的商店.

然后在应用程序逻辑中使用这两个结果来生成组合输出.

Then in the application logic use both the result to produce the combined output.

我的查询 1 如下:

db.Item.aggregate({$group: {_id: "$sid", count:{$sum:1}}})

查询2如下:

db.Profile.aggregate([{ "$addFields": { "pid": { "$toString": "$_id" }}},   { "$lookup": {"from": "Store","localField": "pid","foreignField": "id",  "as": "stores"}}])

在查询中，is_del 也缺失.有没有更简单的方法可以在单个查询中执行所有这些操作?如果是，对可扩展性有何影响?

In the query, is_del is also missing. Is there any simpler way to perform all these in a single query? If so, what will be scalability impact?

推荐答案

您可以使用不相关的子查询，可从 MongoDB v3.6 获得

You can use uncorrelated sub-queries, available from MongoDB v3.6

db.Profile.aggregate([
  {
    $match: { is_del: false }
  },
  {
    $lookup: {
      from: "Store",
      as: "stores",
      let: {
        pid: { $toString: "$_id" }
      },
      pipeline: [
        {
          $match: {
            is_del: false,
            $expr: { $eq: ["$$pid", "$id"] }
          }
        },
        {
          $lookup: {
            from: "Item",
            as: "items",
            let: {
              sid: { $toString: "$_id" }
            },
            pipeline: [
              {
                $match: {
                  is_del: false,
                  $expr: { $eq: ["$$sid", "$sid"] }
                }
              },
              {
                $count: "count"
              }
            ]
          }
        },
        {
          $unwind: "$items"
        }
      ]
    }
  }
])

蒙戈游乐场

为了提高性能，我建议您将引用 ID 存储为 ObjectId，这样您就不必在每个步骤中转换它们.

To improve performance, I suggest you store the reference ids as ObjectId so you don't have to convert them in each step.

这篇关于Mongodb 结合聚合查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！