Mongo aggregation. Return array element that matches multiple conditions as a new field. (reproduction included)

Question

Current query: https://mongoplayground.net/p/mCD3vLoGG1q

Context: Users cast upvotes or downvotes on suggestions. Suggestions and votes are in their own collection. I am using $lookup to get all votes for a suggestion, it becomes an array during the aggregation.

I want the total number of votes per suggestion, but also vote data on 2 specific users.

1. The logged in user
2. The broadcaster user.

This "vote data" can be a simple boolean (did the user upvote), or the array element itself (preferred). I just need to know how they voted on a suggestion.

Current result

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    // WRONG. The broadcaster downvoted.
    "broadcasterUpvoted": true, 
    "hasUpvoted": true,
    "id": "sid",
    "votesLength": 2
  }
]

Desired result

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
     // CORRECT!!!
    "broadcasterUpvoted": false,
    "hasUpvoted": true,
    "id": "sid",
    "votesLength": 2
  }
]

Getting the array element could be useful, so this result is good too.

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "broadcasterVote": {
      "suggestionId": "sid",
      "voteType": "downVote",
      "user": {
        "id": "broadcasterUserId"
      }
    },,
    "loggedInUserVote": {
      "suggestionId": "sid",
      "voteType": "upVote",
      "user": {
        "id": "loggedInUser"
      }
    },
    "id": "sid",
    "votesLength": 2
  }
]

EDIT: Another question is, how do I make votesLength = (totalUpVotes - totalDownVotes) instead of just sizeOfVotesArray

Answer 1

Try this one:

db.suggestions.aggregate([
  {
    $match: {
      id: "sid"
    }
  },
  {
    $lookup: {
      from: "votes",
      localField: "id",
      foreignField: "suggestionId",
      as: "votes"
    }
  },
  {
    $addFields: {
      hasUpvoted: {
        $filter: {
          input: "$votes",
          cond: { $eq: [ "apiCallUserId", "$$this.user.id"] }
        }
      },
      broadcasterUpvoted: {
        $filter: {
          input: "$votes",
          cond: { $eq: [ "broadcasterUserId", "$$this.user.id"]}
        }
      }
    }
  },
  {
    $project: {
      _id: 1,
      id: 1,
      votesLength: {
        $reduce: {
          input: "$votes",
          initialValue: 0,
          in: {
            $add: [
              "$$value",
              {
                $switch: {
                  branches: [
                    {
                      case: { $eq: ["$$this.voteType","upVote"]},
                      then: 1
                    },
                    {
                      case: { $eq: ["$$this.voteType", "downVote"]},
                      then: -1
                    }
                  ],
                  default: 0
                }
              }
            ]
          }
        }
      },
      broadcasterUpvoted: {
        $arrayElemAt: [
          "$broadcasterUpvoted",
          0
        ]
      },
      hasUpvoted: {
        $arrayElemAt: [
          "$hasUpvoted",
          0
        ]
      }
    }
  }
])

MongoPlayground