When retrieving data from 2 collections, I aim to establish a connection between the two based on the MongoDB playground. Specifically, while extracting information from the second collection, I seek to filter results by matching a specific tag.
This is the query I have formulated:
db.Vote.aggregate([
{
$match: {
comment: {
$ne: null,
},
"comment.topic": {
$exists: 1,
$regex: ".",
$options: "i",
},
},
},
{
$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: {
topicOccurance: -1,
},
},
{
$lookup: {
from: "Post",
localField: "postIds",
foreignField: "_id",
as: "tag",
},
},
{
$addFields: {
postIds: {
$setUnion: "$postIds",
},
tag: {
$setUnion: {
$map: {
input: "$tag",
in: "$$this.tag",
},
},
},
},
},
]);
The resulting output resembles this:
{
"_id" : "Collaboration & Teamwork",
"topic" : "Collaboration & Teamwork",
"topicOccurance" : 355,
"sentiment" : {
"Negative" : 102,
"Neutral" : 132,
"Positive" : 121
},
"postIds" : [
"0iWc2U8FVz",
"3Qzysi2cXD",
"3hRx7qAvcb",
"BsrTDkHmkE",
"LT2HE2uEa5",
"Qw0WcUBcnY",
"U72zss2Af5",
"V9DcRcSawi",
"hNwFVJ2bBk"
],
"tag" : [
[
"Engagement"
],
[
"Environment"
],
[
"Feedback & Recognition"
],
[
"Leadership"
],
[
"Management"
],
[
"Meaningful Work"
],
[
"Open Text"
]
],
"totalDocs" : 39
}
Following the tag match, only Posts with a specific tag = foo will be included in the response. How can this be achieved?
Explore the Mongo DB Playground for the query and sample data.