Adding a $match query with mongodb lookup-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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