Trying to return this document from a collection by checking if a variable (userId) matches the second element in users array.

I created a playground. Expected result is the user document xyz as thats the only user who liked rrr and the user rrr has not liked back - https://mongoplayground.net/p/WI7hqR7SIMh

Expected result:

[
  {
    "count": 1,
    "users": [
      {
        "_id": "xyz",
        "group": 1,
        "name": "xyyy"
      }
    ]
  }
]

My query is below where variable userId is xw5vk1s and is the second element in above array. The two conditions I am checking are like: true and userId = second element is users array

  const users = await db
    .collection('users')
    .aggregate([
      {
        $lookup: {
          from: "likes",
          let: {userId: "$_id"},
          pipeline: [{$match: {$expr: {$and: [{like: true, "users.1": userId} ]}}}],
          as: "valid"
        }
      },
      {$match: {
          "valid.0": {$exists: true},
        }
      },
      {$unset: ["valid"]},
      {$group: {_id: 0, users: {$push: "$$ROOT"}, count: {$sum: 1}}}
    ])

The query is not working.

CodePudding user response：

** I hope this will solve your problem **

https://mongoplayground.net/p/8Ax_NaRhfHs

[
  {
    $addFields: {
      secondItem: {
        $arrayElemAt: [
          "$users",
          1
        ]
      }
    }
  },
  {
    $match: {
      $and: [
        {
          users: {
            $in: [
              "$users",
              "xw5vk1s"
            ]
          }
        },
        {
          $expr: {
            $eq: [
              "$secondItem",
              "xw5vk1s"
            ]
          }
        }
      ]
    }
  }
]

CodePudding user response：

One option is using two $lookups:

1. First find the pairs for partnerships that "likes" rrr
2. $match only the unpaired
3. Get the user data and format the response

db.partnership.aggregate([
  {$match: {$expr: {$eq: [{$last: "$users"}, "rrr"]}}},
  {$lookup: {
      from: "partnership",
      let: {likes: {$last: "$users"}, me: {$first: "$users"}},
      pipeline: [
        {$match: {
            $expr: {$and: [
                {$eq: [{$last: "$users"}, "$$me"]},
                {$eq: [{$first: "$users"}, "$$likes"]}
              ]
            }
          }
        }
      ],
      as: "paired"
    }
  },
  {$match: {"paired.0": {$exists: false}}},
  {$project: {_id: 0, user: {$first: "$users"}}},
  {$lookup: {
      from: "users",
      localField: "user",
      foreignField: "_id",
      as: "user"
  }},
  {$project: {user: {$first: "$user"}}},
  {$replaceRoot: {newRoot: "$user"}}
])

See how it works on the playground example