I've a collection that has a self-referencing structure between Employees and Managers, linked through _id and managerIDs fields. Managers are employees themselves:

Employee:

{
  "_id": "61b9f07300127afb99f8c1ea",
  "title": "Developer",
  "firstName": "Jack",
  "lastName": "Strauss",
  "managerIDs": [
    "61cedf84800749316306c6da"
  ],
  "deptID": "61b9f073267500832f5d94d0"
},
{
  "_id": "61cedf84800749316306c6da",
  "title": "Sr. Developer",
  "firstName": "Richard",
  "lastName": "Haris",
  "managerIDs": null,
  "deptID": "61b9f073267500832f5d94d0"
},
{
  "_id": "61cedf17800749316306c6cf",
  "title": "Manager App Development",
  "firstName": "Arnold",
  "lastName": "Cliff",
  "deptID": "61b9f073267500832f5d94d0"
},
{
  "_id": "61d4503e1223496ab8a5ae3c",
  "title": "Developer",
  "firstName": "Andrew",
  "lastName": "Turner",
  "managerIDs": [
    "61cedf17800749316306c6cf",
    "61cedf84800749316306c6da"
  ],
  "deptID": "61b9f073267500832f5d94d0"
}

• i.e. an employee can be managed by none, one or more managers (hence the array).
• managerIDs field can have either null, undefined, one or more IDs.
• Developers can't be managers, so they will not be included in the list of managers. Also, developers are the only ones getting managed.
• All non-developer employees are managers and they'll have a count of developers they managed (i.e. their id will appear in managerIDs array for developers).

I've to write a query that will list all managers with their name, title and a count of developers they manage. I've tried $lookup in MongoDB aggregation without much luck.

How can I write a query for a self-referencing structure like this in MongoDB or Mongoose?

CodePudding user response：

If I understand who can be a manager, here's one way you could do it.

db.employees.aggregate([
  { // managers won't have any managerIDs
    "$match": {
      "managerIDs.0": {"$exists": false}
    }
  },
  { // find managed Developers
    "$graphLookup": {
      "from": "employees",
      "startWith": "$_id",
      "connectFromField": "_id",
      "connectToField": "managerIDs",
      "as": "managedDevs",
      "maxDepth": 0
    }
  },
  { // format output
    "$project": {
      "_id": 0,
      "title": 1,
      "firstName": 1,
      "lastName": 1,
      "devCount": {"$size": "$managedDevs"}
    }
  }
])

Sample output:

[
  {
    "devCount": 2,
    "firstName": "Richard",
    "lastName": "Haris",
    "title": "Sr. Developer"
  },
  {
    "devCount": 1,
    "firstName": "Arnold",
    "lastName": "Cliff",
    "title": "Manager App Development"
  }
]

Try it on mongoplayground.net.

Mostly out of curiosity, I "inverted" the query by getting a histogram of the manager "_id"s present in the "managerIDs" arrays and then "$lookup" the manager details.

N.B.: I'm a javascript noob so my "$accumulator" could probably be improved.

db.employees.aggregate([
  {// managers won't have any managerIDs
    "$match": {
      "managerIDs.0": {"$exists": true}
    }
  },
  { // get histogram of counts per managerID
    "$group": {
      "_id": null,
      "managerIDs": {
        "$accumulator": {
          "init": "function() {return new Object()}",
          "accumulate": "function(state, mgrArray) {mgrArray.forEach((elem) => {state[elem] = state[elem]   1 || 1}); return state}",
          "accumulateArgs": ["$managerIDs"],
          "merge": "function(state1, state2) {Object.entries(state2).forEach((key, val) => {state1[key] = state1[key]   val || val}); return state1}",
          "finalize": "function(state) {const obj = Object.assign({},state); return Object.entries(obj).map(([key, val]) => {return {'_id':key, 'count':val}})}",
          "lang": "js"
        }
      }
    }
  },
  {"$unwind": "$managerIDs"},
  {
    "$lookup": {
      "from": "employees",
      "localField": "managerIDs._id",
      "foreignField": "_id",
      "as": "manager"
    }
  },
  {
    "$set": {
      "manager": {"$first": "$manager"}
    }
  },
  { // format output
    "$project": {
      "_id": 0,
      "title": "$manager.title",
      "firstName": "$manager.firstName",
      "lastName": "$manager.lastName",
      "devCount": "$managerIDs.count"
    }
  }
])

Try it on mongoplayground.net.