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
}
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"
}
}
}
])