MongoDB聚合按主题分组如何返回同组postId数组

问题背景 #

现有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 .. 

需要聚合返回按主题分组的统计结果，包含主题名、主题出现次数、情感分布计数、同主题下所有postId组成的数组，预期返回结构：

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

现有聚合语句已经可以实现主题、出现次数、情感分布的统计，但缺少postIds数组的归集逻辑，原有聚合的核心问题是第一次按「主题+情感」分组时没有保留原始postId数据，导致后续按主题分组时无法归集对应的postId列表。原有可用聚合代码如下：

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

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

        }
    },
    {
        $group: {
            _id: "$_id.topic",
            total: {
                $sum: "$total"
            },
            text_sentiments: {
                $push: {
                    k: "$_id.text_sentiment",
                    v: "$total"
                }
            }
        }
    },
    {
        $project: {
            topic: "$_id",
            topicOccurance: "$total",
            sentiment: {
                "$arrayToObject": "$text_sentiments"
            }
        }
    },
    {
        $sort: {
            "topicOccurance": -1
        }
    }
])

修改后完整聚合代码 #

只需要在两个分组阶段新增postId的收集、合并逻辑，最后在投影阶段输出即可，完整代码如下：

db.Vote.aggregate([
    {
        $match: {
            surveyId: "e6d38e1ecd",
            "comment.topic": { $exists: 1 }
        }
    },
    {
        $group: {
            _id: {
                topic: "$comment.topic",
                text_sentiment: "$comment.text_sentiment"
            },
            total: { $sum: 1 },
            // 收集当前「主题+情感」分组下的所有postId
            postIds: { $push: "$postId" }
        }
    },
    {
        $group: {
            _id: "$_id.topic",
            total: { $sum: "$total" },
            text_sentiments: {
                $push: {
                    k: "$_id.text_sentiment",
                    v: "$total"
                }
            },
            // 收集当前主题下所有情感分组对应的postId数组
            groupedPostIds: { $push: "$postIds" }
        }
    },
    {
        $project: {
            topic: "$_id",
            topicOccurance: "$total",
            // 补全三类情感的默认值，无统计结果时返回0
            sentiment: {
                $let: {
                    vars: { s: { $arrayToObject: "$text_sentiments" } },
                    in: {
                        positive: { $ifNull: ["$$s.positive", 0] },
                        negative: { $ifNull: ["$$s.negative", 0] },
                        neutral: { $ifNull: ["$$s.neutral", 0] }
                    }
                }
            },
            // 将嵌套的postId数组合并为一维数组
            postIds: {
                $reduce: {
                    input: "$groupedPostIds",
                    initialValue: [],
                    in: { $concatArrays: ["$$value", "$$this"] }
                }
            }
        }
    },
    {
        $sort: { "topicOccurance": -1 }
    }
])

可选调整项 #

• 如果需要返回数值类型的postId，将第一次分组的$push逻辑改为postIds: { $push: { $toInt: "$postId" } }即可
• 如果存在重复postId需要去重，将投影阶段的postIds逻辑外层套$setUnion，写法为postIds: { $setUnion: [{ $reduce: { /* 上述reduce逻辑不变 */ }] }

内容的提问来源于stack exchange，提问作者Suhail Gupta