I have a collection named Vote that looks like the following:

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

I want to do an aggregation on this collection such that it returns the following structure.

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

I created the following aggregation:

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

This works fine but I do not know how can I also get an array in the response holding the key postIds. Each document inside the collection vote has postId and I want to group the posts having the same topic and push to an array. How can I do this?

CodePudding user response：

2nd stage ($group) - Add postId into postIds array via $push.

3rd stage ($group) - Add postIds array into postIds array via $push. This will leads postIds become nested array.

[[1,2], ...]

4th stage ($project) - For postIds field, use $reduce operator to flatten the postIds array by $concat.

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

Sample Mongo Playground