在对不同集合执行聚合时从不同集合获取数据

Question

我有一个名为Vote的集合，如下所示：

{
  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
        }
    }
])

这很好，但我不知道如何获得categories，它存在于一个名为posts的单独集合中。如何在执行聚合时查询不同的集合？

Answer 1

一个简单的$lookup就足够了：

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"
          }
        }
      }
    }
  }
])

蒙戈游乐场