mongodb - 聚合 : Perform countBy on multiple $lookup arrays in a single query

Question

转载:https://mongoplayground.net/p/f9BraX4emor

操场查询完成了一半的工作。它找到匹配的文档，但不聚合 bounties和 emotes使用 COUNT BY 操作。

我想要的结果是这样的:

[
  {
    "_id": ObjectId("5e7d7bf7d86d85088863f4de"),
    "channelId": "23435553",
    "bounties": {
        "user1": 150, // user1 had 2 bounties with 100 + 50 amounts
        "user2": 200, // user2 had 1 bounty with 200 amount
    },
    "emotes": {
        "A": 2, // There were 2 docs with emoteId == A
        "B": 1, // There was 1 doc with emoteId == B
    },
  }
]

我其实并不关心聚合结果的具体数据结构。例如。如果 bounties 我会很好和 emotes是数组而不是字典。我认为 $unwind运算符可以在这里使用，但我不确定如何使用。

目前，我正在执行太多查询来满足 REST API 请求。

例如，我的 API 收到一个请求，为一个 channel 查找 10 条建议。首先，我运行 1 个返回 10 条建议的查询。然后对于每个建议，我运行 1 个查询来聚合 bounties ，另一个聚合 emotes .那是 21 个查询......在单个 REST API 调用中...... :(。我想运行 1 个查询，而不是 21。

Answer 1

要根据文档值创建对象，我们需要使用 $arrayToObject运算符(operator)。
优雅的解决方案
说明

我们执行 $map运算符获取 [k:"...", v:"..."]结构体。

另外，我们需要计算多少次emotes/bounties重复

db.suggestions.aggregate([
  {
    $match: {
      "channelId": "23435553"
    }
  },
  {
    $lookup: {
      from: "emotes",
      localField: "_id",
      foreignField: "suggestionId",
      as: "emotes"
    }
  },
  {
    $lookup: {
      from: "bounties",
      localField: "_id",
      foreignField: "suggestionId",
      as: "bounties"
    }
  },
  {
    $project: {
      _id: 1,
      channelId: 1,
      emotes: {
        $arrayToObject: {
          $map: {
            input: "$emotes",
            as: "emote",
            in: {
              k: "$$emote.emoteId",
              v: {
                $size: {
                  $filter: {
                    input: "$emotes",
                    as: "e",
                    cond: {
                      $eq: [
                        "$$emote.emoteId",
                        "$$e.emoteId"
                      ]
                    }
                  }
                }
              }
            }
          }
        }
      },
      bounties: {
        $arrayToObject: {
          $map: {
            input: "$bounties",
            as: "bounty",
            in: {
              k: "$$bounty.userId",
              v: {
                $sum: {
                  $map: {
                    input: {
                      $filter: {
                        input: "$bounties",
                        as: "b",
                        cond: {
                          $eq: [
                            "$$bounty.userId",
                            "$$b.userId"
                          ]
                        }
                      }
                    },
                    in: "$$this.bountyAmount"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])

MongoPlayground
经典方案
说明

我们需要压平 emotes/bounties之后 $lookup .

我们执行 $group减少重复值的阶段

我们创建 [{k:"...", v:"..."}]将其转换为 Object 的结构与 $arrayToObject运算符(operator)。

db.suggestions.aggregate([
  {
    $match: {
      "channelId": "23435553"
    }
  },
  {
    $lookup: {
      from: "emotes",
      localField: "_id",
      foreignField: "suggestionId",
      as: "emotes"
    }
  },
  {
    $unwind: {
      path: "$emotes",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: {
        "_id": "$_id",
        "emoteId": "$emotes.emoteId"
      },
      channelId: {
        $first: "$channelId"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      channelId: {
        $first: "$channelId"
      },
      emotes: {
        $push: {
          k: "$_id.emoteId",
          v: "$count"
        }
      }
    }
  },
  {
    $lookup: {
      from: "bounties",
      localField: "_id",
      foreignField: "suggestionId",
      as: "bounties"
    }
  },
  {
    $unwind: {
      path: "$bounties",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: {
        "_id": "$_id",
        "userId": "$bounties.userId"
      },
      channelId: {
        $first: "$channelId"
      },
      emotes: {
        $first: "$emotes"
      },
      bountyAmount: {
        $sum: "$bounties.bountyAmount"
      }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      channelId: {
        $first: "$channelId"
      },
      emotes: {
        $first: "$emotes"
      },
      bounties: {
        $push: {
          k: "$_id.userId",
          v: "$bountyAmount"
        }
      }
    }
  },
  {
    $project: {
      _id: 1,
      channelId: 1,
      emotes: {
        $arrayToObject: "$emotes"
      },
      bounties: {
        $arrayToObject: "$bounties"
      },
      
    }
  }
])

MongoPlayground