在其他集合上执行聚合时，从其他集合中获取数据

Question

我有一个名为投票的集合，看起来如下：

{
  postId: "1",
  comment:{ 
    text_sentiment: "positive",
    topic: "A"
  }
}, // DOC-1

{
  postId: "2",
  comment:{ 
     text_sentiment: "negative",
     topic: "A"
  }
}, // DOC-2

{
  postId: "3",
  comment:{ 
     text_sentiment: "positive",
     topic: "B"
  }
},..//DOC-3 .. 

和一个名为post的集合，如下所示？

{
  _id: "1",
  category: "a"
},
{
  _id: "2",
  category: "b",
}

我想对此集合进行聚合，以便它返回以下结构。 （还考虑了其​​他post集合`

[
   {
      _id: "hash",
      topic: "A",
      topicOccurance: 2,
      sentiment: {
        positive: 1,
        negative: 1,
        neutral: 0
      },
      postIds: [1,2],
      categories: [a,b]
   },
   ..
]

我创建了以下汇总：

    db.Vote.aggregate([
    {
        $match: {
            surveyId: "e6d38e1ecd",
            "comment.topic": {
                $exists: 1
            },

        }
    },
    {
        $group: {
            _id: {
                topic: "$comment.topic",
                text_sentiment: "$comment.text_sentiment"
            },
            total: {
                $sum: 1
            },
            postIds: {
                $push: "$postId"
            }

        }
    },
    {
        $group: {
            _id: "$_id.topic",
            total: {
                $sum: "$total"
            },
            text_sentiments: {
                $push: {
                    k: "$_id.text_sentiment",
                    v: "$total"
                }
            },
            postIds: {
                "$push": "$postIds"
            }
        }
    },
    {
        $project: {
            topic: "$_id",
            topicOccurance: "$total",
            sentiment: {
                "$arrayToObject": "$text_sentiments"
            },
            postIds: {
                $reduce: {
                  input: "$postIds",
                  initialValue: [],
                  in: {
                    $concatArrays: [
                      "$$value",
                      "$$this"
                    ]
                  }
                }
            }
        }
    },
    {
        $sort: {
            "topicOccurance": -1
        }
    }
])

这可以正常工作，但我不知道，如何获得类别命名帖子我如何在执行聚合时查询不同的集合？

Answer 1

简单的$查找就足够了：

db.Vote.aggregate([
  {
    $match: {
      "comment.topic": {
        $exists: 1
      },
      
    }
  },
  {
    $group: {
      _id: {
        topic: "$comment.topic",
        text_sentiment: "$comment.text_sentiment"
      },
      total: {
        $sum: 1
      },
      postIds: {
        $push: "$postId"
      }
    }
  },
  {
    $group: {
      _id: "$_id.topic",
      total: {
        $sum: "$total"
      },
      text_sentiments: {
        $push: {
          k: "$_id.text_sentiment",
          v: "$total"
        }
      },
      postIds: {
        "$push": "$postIds"
      }
    }
  },
  {
    $project: {
      topic: "$_id",
      topicOccurance: "$total",
      sentiment: {
        "$arrayToObject": "$text_sentiments"
      },
      postIds: {
        $reduce: {
          input: "$postIds",
          initialValue: [],
          in: {
            $concatArrays: [
              "$value",
              "$this"
            ]
          }
        }
      }
    }
  },
  {
    $sort: {
      "topicOccurance": -1
    }
  },
  {
    $lookup: {
      from: "post",
      localField: "postIds",
      foreignField: "_id",
      as: "categories"
    }
  },
  {
    $addFields: {
      categories: {
        "$setUnion": {
          $map: {
            input: "$categories",
            in: "$this.category"
          }
        }
      }
    }
  }
])

mongo playground