MongoDB aggregate with lookup and sort using associated collection fields is slowing down the query

Question

I have two collections in my mongodb database as follows:

employee_details with approximately 330000 documents which has department_id as a reference from departments collection

departments collections with 2 fields _id and dept_name

I have added index to the collections as folows

db.departments.createIndex( { dept_name: 1 } )
db.employee_details.createIndex( { department_id: 1 } )
db.employee_details.createIndex( { employee_fname: 1 } )

I want to fetch the data to list on a data table by joining the two collections. But when I tried to do that I have faced two issues.

First when I add sort after lookup the quer running takes a long time, I have added sort after lookup , since I need to sort with dept_name of collection departments. Query is given below

db.getCollection("employee_details").aggregate([
  {
    $lookup: {
      from: "departments",
      localField: "department_id",
      foreignField: "_id",
      as: "Department"
    }
  },
  { $unwind: { path: "$Department", preserveNullAndEmptyArrays: true } },
  { $sort: { "Department.dept_name": 1 } },
  { $limit: 30 }
]);

Second when I add the sort above lookup, the query become fast but the result give wrong sorting if I sort using dept_name or department_id( sorting works fine for fields of employee_details collection). Query is given below

db.getCollection("employee_details").aggregate([
  { $unwind: { path: "$Department", preserveNullAndEmptyArrays: true } },
  { $sort: { "Department.dept_name": 1 } },
  //{ $sort: { "department_id": 1 } }, // tried this also
  { $limit: 30 },
  {
    $lookup: {
      from: "departments",
      localField: "department_id",
      foreignField: "_id",
      as: "Department"
    }
  }
]);

Can someone give an optimised solution for fetching the data along with sort from all related collections. Thank you in advance.

Answer 1

You can use the following aggregation:

db.getCollection("departments").aggregate([
    {
        $sort: {
            "dept_name": 1
        }
    },
    {
        $lookup: {
            from: "employee_details",
            localField: "_id",
            foreignField: "department_id",
            as: "employees"
        }
    },
    {
        $unwind: "$employees"
    },
    {
        $limit: 30
    },
    { $addFields: { "employees.department_name": "$dept_name" } },
    { $replaceRoot: { newRoot: "$employees" } }
])

This will work for MongoDB version 3.4, it will be fast enough even without any indexes.