mongodbcountdbref

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


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
}

Solution

  • 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