mongodb for each document retrieve its fields + count of its id in another collection by reference

Question

Lets say I have a user collection where a single document has fields like this

{
    "_id" : "4775222e-8e4f-4f84-8dba-b097291bbd39",
    "surname" : "Smith",
    "forename" : "John"
}

and lets say I have jobs collection where a document has some regular fields but also has db ref to user id like so

{
    "_id" : "f4bdda3e-0e8d-4e8d-b070-7d01421f5a51",
    "description" : "do something",
    "manager" : {
        "$ref" : "users",
        "$id" : "4775222e-8e4f-4f84-8dba-b097291bbd39"
    }
}

is it possible to retrieve a list of users but also for each user a count of jobs belonging to him in a single query, so the end output would be a list of entities where a single entity would look like

{
    "_id" : "4775222e-8e4f-4f84-8dba-b097291bbd39",
    "surname" : "Smith",
    "forename" : "John",
    "jobsCount":3
}

Answer 1

You can do this with a simple $lookup stage:

db.users.aggregate([
  {
    "$lookup": {
      "from": "jobs",
      "localField": "_id",
      "foreignField": "manager.$id",
      "as": "jobsCount"
    }
  },
  {
    "$addFields": {
      "jobsCount": {
        $size: "$jobsCount"
      }
    }
  }
])

Mongo Playground