Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

count of $lookup result mongodb

Using mongodb with NodeJS driver

I have 2 collections. One for department and other for students.

Sample data for Deparmtent.

{ 
 "_id" : ObjectId("5a24d20590d3d12155f3094e"), "name" : "CSE", "hod" : 
 "abc", 
 "students" : [ 
  ObjectId("5a2129172c3e542acb78c1f5"),
  ObjectId("5a2129172c3e542acb78c1f7"), 
  ObjectId("5a2129172c3e542acb78c1f9"), 
  ObjectId("5a2129172c3e542acb78c1fb")
 ]
}

{
 "_id" : ObjectId("5a24d20590d3d12155f3094f"), 
 "name" : "IT", 
 "hod" : "xyz", , 
 "students" : [ 
  ObjectId("5a2129172c3e542acb78c1f6"),
  ObjectId("5a2129172c3e542acb78c1f8"), 
  ObjectId("5a2129172c3e542acb78c1fa"),
  ObjectId("5a2129172c3e542acb78c1fc") 
 ] 
}

and sample data for Students

{
    "_id" : ObjectId("5a2129172c3e542acb78c1f4"),
    "rollNumber" : 11,
    "name" : "Thor",
    "branch" : ObjectId("5a24d20590d3d12155f3094e"),
}

Here is the query:

aggregate([
   {$lookup: 
     { from: "students", 
       localField: "_id", 
       foreignField: "branch",
       as: "studentData"
     }
   }
])

Output of the query

[ 
  { _id: 5a24d20590d3d12155f3094e,
    name: 'CSE',
    hod: 'abc',
    studentData: [ [Object], [Object], [Object], [Object], [Object] ] 
  },
  { _id: 5a24d20590d3d12155f3094f,
    name: 'IT',
    hod: 'xyz',
    studentData: [ [Object], [Object], [Object], [Object] ] } 
]

I have two questions

  1. How to print complete student data.
  2. How to get the count of studentData for each Department
like image 261
Khurshid Avatar asked Dec 04 '17 06:12

Khurshid


People also ask

How do you count aggregation?

Returns as a BIGINT the number of rows in each group where the expression is not NULL . If the query has no GROUP BY clause, COUNT returns the number of table rows. The COUNT aggregate function differs from the COUNT analytic function, which returns the number over a group of rows within a window.

How do I count fields in MongoDB?

First stage $project is to turn all keys into array to count fields. Second stage $group is to sum the number of keys/fields in the collection, also the number of documents processed. Third stage $project is subtracting the total number of fields with the total number of documents (As you don't want to count for _id ).

What is $lookup in MongoDB?

$lookup performs an equality match on the localField to the foreignField from the documents of the from collection. If an input document does not contain the localField , the $lookup treats the field as having a value of null for matching purposes.


2 Answers

To get complete student data you can do something like

db.student.aggregate(
        [
            {
            $lookup:
                {
                    from:"department",
                    localField:"branch",
                    foreignField:"_id",
                    as:"branch"
                }
            }
        ]
    )

This will give you something like this:

{
    "_id" : 1.0,
    "rollNumber" : 110.0,
    "name" : "Thor",
    "branch" : [ 
        {
            "_id" : 1.0,
            "name" : "CSE",
            "hod" : "abc"
        }
    ]
}

To get the count of studentData for each Department

db.getCollection('student').aggregate(
    [
        {
        $lookup:
            {
                from:"department",
                localField:"branch",
                foreignField:"_id",
                as:"branch"
            }
        },
        {
            $group:
            {
                _id:"$branch",
                "numOfStudent":{$sum:1},
                "listOfStudents":{$push:"$name"}
            }
        }
    ]
)

This will give you something like this:

{
    "_id" : [ 
        {
            "_id" : 2.0,
            "name" : "IT",
            "hod" : "xyz"
        }
    ],
    "numOfStudent" : 1.0,
    "listOfStudents" : [ 
        "Ironman2"
    ]
}
{
    "_id" : [ 
        {
            "_id" : 1.0,
            "name" : "CSE",
            "hod" : "abc"
        }
    ],
    "numOfStudent" : 3.0,
    "listOfStudents" : [ 
        "Thor", 
        "Ironman", 
        "Ironman3"
    ]
}

You can change $push:$name to $push:$_id If you want to store the Ids of students and not their names.

EDIT Get similar result using "Departments" collection:

db.department.aggregate([
{
    $lookup:
                {
                    from:"student",
                    localField:"students",
                    foreignField:"_id",
                    as:"studentsDetails"
                }
},
{
    $project:{
            _id:0,
            name:"$name",
            hod:"$hod",
            numOfStudents:{$size:"$studentsDetails"},
            students:"$studentsDetails"
        }
}
])

This will give you something like this:

{
    "name" : "CSE",
    "hod" : "abc",
    "numOfStudents" : 2,
    "students" : [ 
        {
            "_id" : 1.0,
            "rollNumber" : 110.0,
            "name" : "Thor",
            "branch" : 1.0
        }, 
        {
            "_id" : 3.0,
            "rollNumber" : 111.0,
            "name" : "Ironman2",
            "branch" : 2.0
        }
    ]
}
like image 191
Anirudh Bagri Avatar answered Sep 20 '22 08:09

Anirudh Bagri


Your $lookup is completely fine. To add a count of students to your output data, you can use $addFields.

Add this to your aggregation:

{ $addFields: {studentCount: {$size: "$studentData"}}}

}

So, your query should be something like this:

aggregate([
   {$lookup: 
     { from: "students", 
       localField: "_id", 
       foreignField: "branch", as: "studentData"
     }
   }, { $addFields: {studentCount: {$size: "$studentData"}}
}
])
like image 26
Shantanu Shubham Avatar answered Sep 20 '22 08:09

Shantanu Shubham