CODEWITHSUNDEEP
mongodbnosqlaggregate
Sana
Sana

Reputation: 39

MongoDB $group and $project

I need to find the name, dob, address and license number of drivers sharing the same license number. This is the code I uses:

db.employee.aggregate([
  {
    $group: {
      _id: {
        "license": "$EMPLOYEE.licence"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $match: {
      count: {
        "$gt": 1
      }
    }
  },
  {
    $project: {
      "EMPLOYEE.name": 1,
      "EMPLOYEE.dob": 1,
      "EMPLOYEE.address": 1,
      "EMPLOYEE.licence": 1
    }
  }
])

So this is working but it is only displaying the license number which is duplicate. It is not displaying the $project part. What have I done wrong? This is the sample data if you want to test it:

db={
  "employee": [
    {
      "_id": "6",
      "EMPLOYEE": {
        "e#": "6",
        "name": "Michael Jones",
        "dob": "05-OCT-65",
        "address": "23 Waterloo Ave. Surry Hills, NSW 2502",
        "hiredate": "12-JAN-93",
        "position": "mechanic",
        "licence": "7773",
        "status": "ON_LEAVE",
        "experience": "STANDARD",
        "maintenances": [
          {
            "registration": "QRT834",
            "time": "40",
            "maintenance date": "12-JUN-99"
          },
          {
            "registration": "QRT834",
            "time": "40",
            "maintenance date": "15-JUN-98"
          },
          {
            "registration": "SYF777",
            "time": "30",
            "maintenance date": "01-AUG-98"
          }
        ]
      }
    },
    {
      "_id": "9",
      "EMPLOYEE": {
        "e#": "9",
        "name": "Alex Davis",
        "dob": "15-AUG-93",
        "address": "23 Waterloo Ave. Surry Hills, NSW 2502",
        "hiredate": "12-JAN-93",
        "position": "mechanic",
        "licence": "9385",
        "status": "ON_LEAVE",
        "experience": "STANDARD",
        "maintenances": [
          {
            "registration": "QRT834",
            "time": "40",
            "maintenance date": "30-JUN-02"
          }
        ]
      }
    },
    {
      "_id": "23",
      "EMPLOYEE": {
        "e#": "23",
        "name": "Rose Mathews",
        "dob": "20-NOV-80",
        "address": "23 Waterloo Ave. Surry Hills, NSW 2502",
        "hiredate": "12-JAN-93",
        "position": "mechanic",
        "licence": "7773",
        "status": "ON_LEAVE",
        "experience": "STANDARD",
        "maintenances": [
          {
            "registration": "QRT834",
            "time": "40",
            "maintenance date": "12-JUN-99"
          },
          {
            "registration": "LUCY01",
            "time": "200",
            "maintenance date": "12-MAR-97"
          },
          {
            "registration": "QRT834",
            "time": "40",
            "maintenance date": "30-JUN-02"
          }
        ]
      }
    }
  ]
}

I ran it on this website: https://mongoplayground.net

Upvotes: 2

Views: 125

Answers (1)

Vijay Rajpurohit
Vijay Rajpurohit

Reputation: 1352

You need to pass those fields from $group stage as well.

I have updated the query, have a look:

db.sample.aggregate([
  {
    $group:{
      "_id":"$EMPLOYEE.licence",
      "count":{
        $sum:1
      },
      "EMPLOYEE":{
        $addToSet:{
        "name":"$EMPLOYEE.name",
        "dob":"$EMPLOYEE.dob",
        "address":"$EMPLOYEE.address",
        "licence":"$EMPLOYEE.licence",
        }
      }
    }    
  },
  {
    $match:{
      count:{
        $gt:1
      }
    }
  },
  {
    $unwind:"$EMPLOYEE"
  },
  {
    $project:{
     "EMPLOYEE.name": 1,
     "EMPLOYEE.dob": 1,
     "EMPLOYEE.address": 1,
     "EMPLOYEE.licence": 1
    }
  }
]).pretty()

Output

{
        "_id" : "7773",
        "EMPLOYEE" : {
                "name" : "Michael Jones",
                "dob" : "05-OCT-65",
                "address" : "23 Waterloo Ave. Surry Hills, NSW 2502",
                "licence" : "7773"
        }
}
{
        "_id" : "7773",
        "EMPLOYEE" : {
                "name" : "Rose Mathews",
                "dob" : "20-NOV-80",
                "address" : "23 Waterloo Ave. Surry Hills, NSW 2502",
                "licence" : "7773"
        }
}

Hope this will help :)

Upvotes: 2

Related Questions