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?
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. Update: with $setUnion to distinct items in array.
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
])