使用聚合查询创建结构，该查询通过多个ID分组

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

我想对此集合进行汇总，以便它返回以下结构。

[
   {
      _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） - 添加postid到postids通过$ push数组。

第三阶段（$ group） - 添加postids数组postids通过$ push数组。这将导致postids成为嵌套数组。

[[1,2], ...]

第四阶段（$ project） - 对于postids字段，使用$降低运算符将postids by by <代码> $ 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
])

sample蒙哥游乐场