Outer join in mongodb using $lookup

Question

I have a Channels collection

{
  channel: "Xamper",  //unique
  subscribers: [
    ObjectId("5a934e000102030405000000"),
    ObjectId("5a934e000102030405000001"),
    ObjectId("5a934e000102030405000002")
  ]
}

And a Users collection

{
  _id: ObjectId("5a934e000102030405000000"),
  name: "Bradman"
},
{
  _id: ObjectId("5a934e000102030405000001"),
  name: "Hartin"
},
{
  _id: ObjectId("5a934e000102030405000002"),
  name: "Migra"
},
{
  _id: ObjectId("5a934e000102030405000004"),
  name: "Polycor"
}

Now I need to find with Channel name "Xamper" and count the users which are not in subscribers array

So the output will be

{
  channel: "Xamper",
  unSubscribers: 1
}

Which is similar to the outer excluding join of SQL

user_2683814 · Answer

You can use below pipeline in 3.6.

$lookup with pipeline to join the Channels collection to Users collection on subscribers

$count with $not $in to compare the subscribers against each id in the Users collection and output the no of matched documents.

$project to project the output fields.

db.Channels.aggregate([
  { "$lookup":  {
    "from": "Users",
    "let": { "subscribers": "$subscribers" },
    "pipeline": [
      { "$match": { "$expr": { "$not": { "$in": [ "$_id", "$$subscribers" ] }}}},
      { "$count": "count" }
    ],
    "as": "lookupresult"
  }},
  { "$project": {
    "channel":  1,
    "unSubscribers": { "$arrayElemAt": [ "$lookupresult.count", 0 ] }
  }}
])

Outer join in mongodb using $lookup

1 Answers