skip to Main Content

I am pulling data from 2 collections as shown in this MongoDB playground. While pulling the data from the second collection, I want to create a match on tag, such that only those posts are returned that have the particular tag associated with them.

Here is the query that I created:

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 result looks similar to:

{
    "_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
}

After the tag match, the response will only have Posts that have tag = foo. How can I do that?

Mongo DB Playground : This has the above query with the sample data.

2

Answers


  1. Edit: If you want the query to return the sentiment and postIds that match the tag criteria, you can use the $lookup on an earlier phase.

    db.Vote.aggregate([
      {$match: {"comment": {$ne: null},
          "comment.topic": {$exists: 1, $regex: ".", $options: "i"}}
      },
      {$lookup: {
          from: "Post",
          let: {postId: "$postId"},
          pipeline: [
            {$match: {$expr: {$and: [{$eq: ["$_id", "$$postId"]}, {$in: ["a", "$tag"]}]}}}
          ],
          as: "tag"
        }
      },
      {$match: {$expr: {$gt: [{$size: "$tag"}, 0]}}},
      {$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}},
      {$addFields: {postIds: {$setUnion: "$postIds"},
          tag: {$setUnion: {$map: {input: "$tag", in: "$$this.tag"}}
          }
        }
      }
    ])
    

    See how it works on the playground example

    Login or Signup to reply.
  2. Please add this code in the look up it should help you

    {
          $lookup:
             {
               from: "Post",
               let: { tagInit: "$tag", postidInit: "$postIds" },
               pipeline: [
                  { $match:
                     { $expr:
                        { $and:
                           [
                             { $eq: [ "$_id",  "$$postids" ] },
                             { $eq: [ "$tag", "$$tagInit" ] }
                           ]
                        }
                     }
                  }
               ],
               as: "tag"
             }
        }
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search