使用聚合查询创建结构，该查询按多个ids分组。

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

我希望对此集合进行聚合，使其返回以下结构。

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

我创建了以下聚合：

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

这很好，但我不知道如何在响应中获得一个包含键postIds的数组。集合投票中的每个文档都有postId，我希望将具有相同主题的帖子分组并推送到数组中。我该怎么做？

Answer 1

第二阶段($group) -通过$push将postId添加到postIds数组中。

第三阶段($group) -通过$push将postIds数组添加到postIds数组中。这将导致postIds成为嵌套数组。

[[1,2], ...]

第四阶段($project) -对于postIds字段，使用$reduce操作符将postIds数组通过$concat扁平。$setUnion 更新:使用更新数组中的不同项。

db.collection.aggregate([
  // match stage
  {
    $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: {
        $setUnion: [
          {
            $reduce: {
              input: "$postIds",
              initialValue: [],
              in: {
                $concatArrays: [
                  "$$value",
                  "$$this"
                ]
              }
            }
          }
        ]
      }
    }
  },
  // sort stage
])

蒙戈游乐场样本