MongoDB conditional relation query

Question

MongoDB 4.2.2,

I have 2 collections Polls and Votes, I need to grab field voter_selection from "Votes" collection and add it to Polls but only if certain conditions match poll_id & voter_id

User:

{
"_id" : ObjectId("5f867e0d126ddbde24d6ee73"),
"name" : "Jaskson"
"age" : "24"
}

Polls:

{
"_id" : ObjectId("5f87d988ddae726a3dbe5459"),
"name" : "RedVsWhite"
},
{
"_id" : ObjectId("5f51408ffc1d0437fa31d6f7"),
"name" : "ApplesVsOrange",
"total_votes" : "0",
}

Votes:

{
"_id" : ObjectId("5f864addddae726a3dbe53de"),
"voter_id" : ObjectId("5f867e0d126ddbde24d6ee73"), //this is the id of the person
"poll_id" : ObjectId("5f87d988ddae726a3dbe5459"), //this is the poll id
"voter_selection" : "red"
}

I need this Result:

{
"_id" : ObjectId("5f87d988ddae726a3dbe5459"),
"name" : "RedVsWhite",
"total_votes" : "0",
"voter_selection" : red // show this if it finds votes with this _id and also if user_id match & voter_id
},
{
"_id" : ObjectId("5f51408ffc1d0437fa31d6f7"),
"name" : "ApplesVsOrange",
"total_votes" : "0"
/// dont show voter_selection if nothing match with voter_id and poll_id
}

This query works, but the problem is that if nothing match it wont show the polls rows, and what I need is to get all polls even if there is no match but if there is a match add the field voter_selection to the row

db.getCollection("Polls").aggregate(
    [
        {
            "$lookup": {
                "localField": "_id",
                "from": "VotesRecords",
                "foreignField": "poll_id",
                "as": "VotesRecords"
            }
        },
        {
            "$unwind": {
                "path": "$VotesRecords",
                "preserveNullAndEmptyArrays": true
            }
        },
        {
            "$match": {
                "VotesRecords.voter_id": ObjectId("5f867e0d126ddbde24d6ee73")
            }
        },
        {
            "$project": {
                "_id": "$_id",
                "name": "$name",
                "voter_selection": "$VotesRecords.voter_selection"
            }
        }
    ]
);

user_8987128 · Answer

$group by null and make a array of all elements of root
$lookup to join collection with VotesRecords and get user details
$unwind deconstruct VotesRecords array
$project iterate loop through $map on root array and check condition if match poll_id then return voter_selection otherwise nothing using
$unwind deconstruct root array
$replaceWith to replace root object in root

db.getCollection("Polls").aggregate([
  {
    $group: {
      _id: null,
      root: { $push: "$$ROOT" }
    }
  },
  {
    $lookup: {
      from: "VotesRecords",
      as: "VotesRecords",
      pipeline: [{ $match: { voter_id: ObjectId("5f867e0d126ddbde24d6ee73") } }]
    }
  },
  {
    "$unwind": {
      "path": "$VotesRecords",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    $project: {
      root: {
        $map: {
          input: "$root",
          in: {
            $mergeObjects: [
              "$$this",
              {
                $cond: [
                  { $eq: ["$$this._id", "$VotesRecords.poll_id"] },
                  { voter_selection: "$VotesRecords.voter_selection" },
                  {}
                ]
              }
            ]
          }
        }
      }
    }
  },
  { $unwind: "$root" },
  { $replaceWith: "$root" }
])

Playground

Second approach, you can add below changes after your pipelines,

$project to show voter_selection if voter_id match otherwise $$REMOVE
$group by _id and get unique object out of many using $mergeObjects
$replaceWith to replace root object

  // <= skipping your pipelines here
  {
    "$project": {
      "_id": "$_id",
      "name": "$name",
      "total_votes": 1,
      "voter_selection": {
        $cond: [
          { $eq: ["$VotesRecords.voter_id", ObjectId("5f867e0d126ddbde24d6ee73")] },
          "$VotesRecords.voter_selection",
          "$$REMOVE"
        ]
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      root: { $mergeObjects: "$$ROOT" }
    }
  },
  { $replaceWith: "$root" }

Playground

MongoDB conditional relation query

1 Answers