CODEWITHSUNDEEP
phpmongodb
Andrea Crocco
Andrea Crocco

Reputation: 21

Subquery with NOT IN with MongoDB

I would like to create a query with mongodb which would do this thing:

SELECT * FROM Users WHERE Username NOT IN (SELECT LikeTo FROM Like WHERE Username = "John89").

i did something like this:

[
  '$lookup'=> [
    'from'=> "like",
    'let'=> [ 'username'=> '$username' ],
    'as'=> "leaders",
    'pipeline'=> [
    [
      '$match'=> [
        '$and'=> [
          ['$expr'=> [ '$not'=> ['$in'=> ['$likeTo','$$username']]]],
          ['username'=> "$username" ]
        ]
      ]
    ]
    ]
  ]
]

but i got PlanExecutor error during aggregation :: caused by :: $in requires an array as a second argument, found: string. what did i do wrong? is the query right?

thanks!

Upvotes: 1

Views: 112

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22296

I would personally split this into 2 queries, but here is how yo achieve this in a single pipeline, we actually want the lookup to match the "bad" users so we can use it to filter, like so:

db.users.aggregate([
  {
    "$lookup": {
      "from": "likes",
      let: {
        username: "$username"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$likeTo",
                    "$$username"
                  ]
                },
                {
                  "$eq": [
                    "$username",
                    "John89"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "leaders"
    }
  },
  {
    $match: {
      "leaders.0": {
        $exists: false
      }
    }
  },
  {
    $project: {
      leaders: 0
    }
  }
])

Mongo Playground

Upvotes: 1

Related Questions