mongodb - left join with conditions

Question

I am attempting an left antijoin on these two collections.

I want all users where department is equal to 'IT' that aren't in a meeting that had an endAt time > 175. Either as a creator or receiver. So essentially whoever hasn't been in a meeting in the last xxx time.

Based on below collections: John would be retrieved because he is apart of department IT and has not been a receiver or creator after '175'. Jane has an endAt time after 175 and is in IT so wouldn't be retrieved Bill is apart of finance so even though he hasn't been it doesn't matter Bob has an endAt time after 175 and is in IT so wouldn't be retrieved Mary is in IT and has not been in any meetings so she is retrieved.

Users Collection:

[
  {
    _id: ObjectId("1"),
    name: "john",
    department: 'IT'
  },
  {
    _id: ObjectId("2"),
    name: "jane",
    department: 'IT'
  },
  {
    _id: ObjectId("3"),
    name: "bill",
    department: 'finance'
  },
  {
    _id: ObjectId("4"),
    name: "Bob",
    department: 'IT'
  },
  {
    _id: ObjectId("5"),
    name: "Mary",
    department: 'IT'
  }
]

Meetings Collection:

[
  {
    _id: ObjectId("a"),
    endedAt: 100,
    creator_id: ObjectId("1"),
    receiver_id: ObjectId("2")
  },
  {
    _id: ObjectId("b"),
    endedAt: 150,
    creator_id: ObjectId("1"),
    receiver_id: ObjectId("3")
  },
  {
    _id: ObjectId("c"),
    endedAt: 200,
    creator_id: ObjectId("4"),
    receiver_id: ObjectId("2")
  },
  {
    _id: ObjectId("d"),
    endedAt: 250,
    creator_id: ObjectId("2"),
    receiver_id: 
  }
]

Output:

[
  {
    _id: ObjectId("1"),
    name: "john",
    department: 'IT'
  },
  {
    _id: ObjectId("5"),
    name: "Mary",
    department: 'IT'
  }
]

My approach:

db.users.aggregate([
        {
            $match:
                {
                    type: 'IT'
                }
        },
        {
            $lookup:
                {
                    from: "meetings",
                    let:
                        {
                            userid: "$_id",
                        },
                    pipeline: [
                        { $match:
                                { $expr:
                                    {
                                        $and:[
                                            {
                                                $or: [
                                                    { $eq: ["$receiver_id", "$$userid"] },
                                                    { $eq: ["$creator_id", "$$userid"] },
                                                ]
                                            },
                                            { $gt: ["$endAt", 175] }
                                        ]
                                    }
                                }
                        }
                        ],
                    as: "result"
                }
        },

        {
            $unwind:
                {
                    path: "$result",
                    preserveNullAndEmptyArrays: true
                }
        },

        {
            $match:
                {
                    result: {$exists:false}
                }
        }
    ])

try `$lookup` with pipeline, see mongodb documentation, and to remove not joined documents, filter out the empty array (the join result) — Takis, Sep 30 '21 at 03:21
thanks @Takis_ I had tried and was getting weird results. I'll make another attempt and post my attempt. — CirqueM, Sep 30 '21 at 03:23

score 1 · Answer 1 · answered Sep 30 '21 at 05:55

aggregate

db.users.aggregate([
  {
    "$match": {
      department: "IT"
    }
  },
  {
    "$lookup": {
      "from": "meeting",
      "localField": "_id",
      "foreignField": "creator_id",
      "as": "meeting_creator"
    }
  },
  {
    "$lookup": {
      "from": "meeting",
      "localField": "_id",
      "foreignField": "receiver_id",
      "as": "meeting_receiver"
    }
  },
  {
    "$match": {
      "$and": [
        {
          "meeting_creator.endedAt": {
            "$not": {
              "$gt": 175
            }
          }
        },
        {
          "meeting_receiver.endedAt": {
            "$not": {
              "$gt": 175
            }
          }
        }
      ]
    }
  },
  {
    "$project": {
      _id: 1,
      name: 1,
      department: 1
    }
  }
])

data

db={
  "users": [
    {
      _id: "1",
      name: "john",
      department: "IT"
    },
    {
      _id: "2",
      name: "jane",
      department: "IT"
    },
    {
      _id: "3",
      name: "bill",
      department: "finance"
    },
    {
      _id: "4",
      name: "Bob",
      department: "IT"
    },
    {
      _id: "5",
      name: "Mary",
      department: "IT"
    }
  ],
  "meeting": [
    {
      _id: "a",
      endedAt: 100,
      creator_id: "1",
      receiver_id: "2"
    },
    {
      _id: "b",
      endedAt: 150,
      creator_id: "1",
      receiver_id: "3"
    },
    {
      _id: "c",
      endedAt: 200,
      creator_id: "4",
      receiver_id: "2"
    },
    {
      _id: "d",
      endedAt: 250,
      creator_id: "2",
      receiver_id: ""
    }
  ]
}

result

[
  {
    "_id": "1",
    "department": "IT",
    "name": "john"
  },
  {
    "_id": "5",
    "department": "IT",
    "name": "Mary"
  }
]

mongoplayground

score 1 · Accepted Answer · answered Sep 30 '21 at 10:32

Query

match "IT"
join if >175 AND (userid in any of the 2 (creator/receiver))
*its lookup pipeline, because multiple join creteria
reject those that are joined

Test code here

db.users.aggregate([
  {
    "$match": {
      "department": {
        "$eq": "IT"
      }
    }
  },
  {
    "$lookup": {
      "from": "meetings",
      "let": {
        "userid": "$_id"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$and": [
                {
                  "$gt": [
                    "$endedAt",
                    175
                  ]
                },
                {
                  "$or": [
                    {
                      "$eq": [
                        "$$userid",
                        "$creator_id"
                      ]
                    },
                    {
                      "$eq": [
                        "$$userid",
                        "$receiver_id"
                      ]
                    }
                  ]
                }
              ]
            }
          }
        },
        {
          "$project": {
            "_id": 1
          }
        }
      ],
      "as": "meetings"
    }
  },
  {
    "$match": {
      "$expr": {
        "$eq": [
          "$meetings",
          []
        ]
      }
    }
  },
  {
    "$unset": [
      "meetings"
    ]
  }
])

CirqueM · Answer 3 · 2021-09-30T17:02:32.317

This is the solution I came up with that ended up working, does anyone have any details what would be the most efficient?

db.users.aggregate([
        {
            $match:
                {
                    type: 'IT'
                }
        },
        {
            $lookup:
                {
                    from: "meetings",
                    let:
                        {
                            userid: "$_id",
                        },
                    pipeline: [
                        { $match:
                                { $expr:
                                    {
                                        $and:[
                                            {
                                                $or: [
                                                    { $eq: ["$receiver_id", "$$userid"] },
                                                    { $eq: ["$creator_id", "$$userid"] },
                                                ]
                                            },
                                            { $gt: ["$endAt", 175] }
                                        ]
                                    }
                                }
                        }
                        ],
                    as: "result"
                }
        },

        {
            $unwind:
                {
                    path: "$result",
                    preserveNullAndEmptyArrays: true
                }
        },

        {
            $match:
                {
                    result: {$exists:false}
                }
        }
    ])

queries are almost same, except no `$project _id` (used to save less data in the array), you could replace it also with `{"group" {"_id" : null}}` (would be a bit better i think), because we dont care about the data, we only care if it joined with at least one. Checking if an array is empty doesn't need unwind and check, we can check the 1 document (instead of the many after the unwind). The most important is to have mongodb >= 5, so it can use indexes, in the $expr in the loookup pipeline. — Takis, Sep 30 '21 at 17:32

mongodb - left join with conditions

3 Answers3