1

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
}
tibortru
  • 692
  • 5
  • 26

1 Answers1

2

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

Tom Slabbaert
  • 21,288
  • 10
  • 30
  • 43
  • in your mongo playground example you have "manager": "4775222e-8e4f-4f84-8dba-b097291bbd39" but I need it with example "manager": { "$ref": "users", "$id": "4775222e-8e4f-4f84-8dba-b097291bbd39" } It is not working in that case – tibortru Sep 27 '20 at 15:56
  • Oops I just assumed that `mongoose` syntax, I updated the syntax + the mongoplayground example. All you have to change is the `foreignField` part – Tom Slabbaert Sep 27 '20 at 17:39
  • ye, I am now running into Error "errmsg" : "FieldPath field names may not start with '$'.","code" : 16410 which is I think mentioned here https://stackoverflow.com/a/41677055/1094300 My mongodb version is 4.2.8 EDIT:Mongo PLayground uses 4.4.1 maybe it is possible in that version, I'll see if I can upgrade – tibortru Sep 27 '20 at 18:24
  • I would recommend you change your structure as a name that starts with $ is a recipe for trouble – Tom Slabbaert Sep 28 '20 at 08:12
  • Ye I agree, but I think this is default when using spring-data-mongodb and having @DBRef annotation on certain entity fields in order to achieve behaviour like in relational DB, then the field in mongo db has 2 inner fields $ref for foreign collection and $id for the specific document it references too. Anyway I can confirm this works with version 4.4.1 of mongodb, I have upvoted your answer. Thanks – tibortru Sep 28 '20 at 09:04