MongoDB: How to remove duplicate records from a query?

Question

Example collections:

employee

{"FNAME" : "John", "LNAME" : "Smith", "SSN" : "123456789", "SALARY" : 30000, "SUPERSSN" : "333445555"}
{"FNAME" : "Franklin", "LNAME" : "Wong", "SSN" : "333445555", "SALARY" : 40000, "SUPERSSN" : "888665555"}
{"FNAME" : "Joyce", "LNAME" : "English", "SSN" : "453453453", "SALARY" : 25000, "SUPERSSN" : "333445555"}
{"FNAME" : "Ramesh", "LNAME" : "Narayan", "SSN" : "666884444", "SALARY" : 38000, "SUPERSSN" : "333445555"}
{"FNAME" : "James", "LNAME" : "Borg", "SSN" : "888665555", "SALARY" : 55000, "SUPERSSN" : "", "DNO" : 1 }
{"FNAME" : "Jennifer", "LNAME" : "Wallace", "SSN" : "987654321", "SALARY" : 43000, "SUPERSSN" : "888665555"}
{"FNAME" : "Ahmad", "LNAME" : "Jabbar", "SSN" : "987987987", "SALARY" : 25000, "SUPERSSN" : "987654321"}
{"FNAME" : "Alicia", "LNAME" : "Zelaya", "SSN" : "999887777", "SALARY" : 25000, "SUPERSSN" : "987654321"}
{"FNAME" : "John", "LNAME" : "Smith", "SSN" : "123456789", "SALARY" : 30000, "SUPERSSN" : "333445555"}
{"FNAME" : "Franklin", "LNAME" : "Wong", "SSN" : "333445555", "SALARY" : 40000, "SUPERSSN" : "888665555"}
{"FNAME" : "Joyce", "LNAME" : "English", "SSN" : "453453453", "SALARY" : 25000, "SUPERSSN" : "333445555"}
{"FNAME" : "Ramesh", "LNAME" : "Narayan", "SSN" : "666884444", "SALARY" : 38000, "SUPERSSN" : "333445555"}
{"FNAME" : "James", "LNAME" : "Borg", "SSN" : "888665555",  "SALARY" : 55000, "SUPERSSN" : "", "DNO" : 1 }
{"FNAME" : "Jennifer", "LNAME" : "Wallace", "SSN" : "987654321",  "SALARY" : 43000, "SUPERSSN" : "888665555"}
{"FNAME" : "Ahmad", "LNAME" : "Jabbar", "SSN" : "987987987",  "SALARY" : 25000, "SUPERSSN" : "987654321"}
{"FNAME" : "Alicia", "LNAME" : "Zelaya", "SSN" : "999887777", "SALARY" : 25000, "SUPERSSN" : "987654321"}

works_on

{ "ESSN" : "123456789", "PNO" : 1, "HOURS" : 32.5 }
{ "ESSN" : "123456789", "PNO" : 2, "HOURS" : 7.5 }
{ "ESSN" : "333445555", "PNO" : 2, "HOURS" : 10 }
{ "ESSN" : "333445555", "PNO" : 3, "HOURS" : 10 }
{ "ESSN" : "333445555", "PNO" : 10, "HOURS" : 10 }
{ "ESSN" : "333445555", "PNO" : 20, "HOURS" : 10 }
{ "ESSN" : "453453453", "PNO" : 1, "HOURS" : 20 }
{ "ESSN" : "453453453", "PNO" : 2, "HOURS" : 20 }
{ "ESSN" : "666884444", "PNO" : 3, "HOURS" : 40 }
{ "ESSN" : "888665555", "PNO" : 20, "HOURS" : 0 }
{ "ESSN" : "987654321", "PNO" : 20, "HOURS" : 15 }
{ "ESSN" : "987654321", "PNO" : 30, "HOURS" : 20 }
{ "ESSN" : "987987987", "PNO" : 10, "HOURS" : 35.5 }
{ "ESSN" : "987987987", "PNO" : 30, "HOURS" : 5.5 }
{ "ESSN" : "999887777", "PNO" : 10, "HOURS" : 10 }
{ "ESSN" : "999887777", "PNO" : 30, "HOURS" : 30 }

I want to remove the duplicate records from a "join" of the following MongoDB query:

db.employee.aggregate([
    {
        $lookup:{
            from: "works_on",    
            localField: "SSN",   
            foreignField: "ESSN",
            as: "works_on_here"  
        }
    },
    {   $unwind:"$works_on_here" },
    {
        $group:{
          _id:"$_id",
          nodes:{
            $addToSet:"$works_on_here"
        }
    },
    {   
        $project:{
            _id : 1,
            FNAME : 1,
            LNAME : 1,
            HOURS : "$works_on_here.HOURS",
        } 
    }
]);

The expected outcome is:

{ "FNAME" : "John", "LNAME" : "Smith", "HOURS" : 32.5 }
{ "FNAME" : "Franklin", "LNAME" : "Wong", "HOURS" : 10 }
{ "FNAME" : "Joyce", "LNAME" : "English", "HOURS" : 20 }
{ "FNAME" : "Ramesh", "LNAME" : "Narayan", "HOURS" : 40 }
{ "FNAME" : "James", "LNAME" : "Borg", "HOURS" : 0 }
{ "FNAME" : "Jennifer", "LNAME" : "Wallace", "HOURS" : 20 }
{ "FNAME" : "Ahmad", "LNAME" : "Jabbar", "HOURS" : 5.5 }
{ "FNAME" : "Alicia", "LNAME" : "Zelaya", "HOURS" : 30 }
{ "FNAME" : "John", "LNAME" : "Smith", "HOURS" : 7.5 }
{ "FNAME" : "Franklin", "LNAME" : "Wong", "HOURS" : 10 }

The actual output is without the "$group" part looks like:

{ "FNAME" : "John", "LNAME" : "Smith", "HOURS" : 32.5 }
{ "FNAME" : "John", "LNAME" : "Smith", "HOURS" : 7.5 }
{ "FNAME" : "Franklin", "LNAME" : "Wong", "HOURS" : 10 }
{ "FNAME" : "Franklin", "LNAME" : "Wong", "HOURS" : 10 }
{ "FNAME" : "Franklin", "LNAME" : "Wong", "HOURS" : 10 }
{ "FNAME" : "Franklin", "LNAME" : "Wong", "HOURS" : 10 }
{ "FNAME" : "Joyce", "LNAME" : "English", "HOURS" : 20 }
{ "FNAME" : "Joyce", "LNAME" : "English", "HOURS" : 20 }
{ "FNAME" : "Ramesh", "LNAME" : "Narayan", "HOURS" : 40 }
{ "FNAME" : "James", "LNAME" : "Borg", "HOURS" : 0 }
{ "FNAME" : "Jennifer", "LNAME" : "Wallace", "HOURS" : 15 }
{ "FNAME" : "Jennifer", "LNAME" : "Wallace", "HOURS" : 20 }
{ "FNAME" : "Ahmad", "LNAME" : "Jabbar", "HOURS" : 35.5 }
{ "FNAME" : "Ahmad", "LNAME" : "Jabbar", "HOURS" : 5.5 }
{ "FNAME" : "Alicia", "LNAME" : "Zelaya", "HOURS" : 10 }
{ "FNAME" : "Alicia", "LNAME" : "Zelaya", "HOURS" : 30 }
{ "FNAME" : "John", "LNAME" : "Smith", "HOURS" : 32.5 }
{ "FNAME" : "John", "LNAME" : "Smith", "HOURS" : 7.5 }
{ "FNAME" : "Franklin", "LNAME" : "Wong", "HOURS" : 10 }
{ "FNAME" : "Franklin", "LNAME" : "Wong", "HOURS" : 10 }

I have the two collections 'employee' and 'works_on' and I try to do something like this "join".

The code with the $group part returns nothing. This should work as the duplicate filter or?

Langdon

#14184599. Refer this – Esmond

Updated my question - the $group part doesn't work. Am I doing smth. wrong? – Langdon

can you post sample documents of both collection and expected result in your question. – Apollo

of course, the question is updated! – Langdon

what is the unique field in employee collection, is it SSN? – Apollo

yes, this is the unique field. Even though the filter could also be made with the document id. – Langdon

Answer 1

• $group by SSN and get first FNAME and LNAME fields, if you want other fields you can add same as FNAME and LNANE
• $lookup with works_on
• $project to show required fields and get total HOURS sum using $sum

db.employee.aggregate([
  {
    $group: {
      _id: "$SSN",
      FNAME: { $first: "$FNAME" },
      LNAME: { $first: "LNAME" }
    }
  },
  {
    $lookup: {
      from: "works_on",
      localField: "_id",
      foreignField: "ESSN",
      as: "HOURS"
    }
  },
  {
    $project: {
      _id: 0,
      FNAME: 1,
      LNAME: 1,
      HOURS: {
        $sum: "$HOURS.HOURS"
      }
    }
  }
])

Playground

Apollo

Ok this solution works fine. Is this also possible with a projection? I'm trying to write something like a little converter, so I'm looking for a general solution. If I don't want to make basic calculations the projection works, but does it also works with calcculations? What does the keyword $first? – Langdon

yes you can use $project instead of $addFields stage – Apollo

What does the keyword $first? – Langdon

$group stage will group your root documents by SSN, so virtually it loop inside group stage and so $first means we are selecting first element from grouped root element. – Apollo

Can you please offer the solution with $project and $sum? Doesn't work for me... – Langdon