Here is my sample MongoDB collection, I need a list of all the records by ascending order of number of subjects student enrolled.
The corresponding mysql query will be
SELECT name, COUNT(subject_code) AS NUM FROM table_name ORDER BY NUM ASC
Please help me to find a MongoDb query to do the same:
{
["name"]=> string(5) "David"
["subject_code"]=> array(5) {
[0]=> int(101)
[1]=> int(111)
[2]=> int(121)
[3]=> int(123)
[4]=> int(53)
}
}
I have checked the already available answers. Those are not really helpful. Hope this answer will be helpful.
Suppose you have the following documents
{ "_id" : 1, "name" : "david", subject_code: [ 101, 111, 121 ] }
{ "_id" : 2, "name" : "joe", subject_code: [ 131 ] }
{ "_id" : 3, "name" : "sam", subject_code: [ ] }
This query will list the length of list as field len
db.collname.aggregate([{ $project: { name: 1, len: { $size: "$subject_code" }}}])
Output will be
{ "_id" : 1, "name" : "david", len: 3 }
{ "_id" : 2, "name" : "joe", len: 1 ] }
{ "_id" : 3, "name" : "sam", len: 0 }
Now things are easy.. use a sort function on the output (on field "len")
db.collname.aggregate(
[
{$project: {"name": 1, "nump": { $size: { "$ifNull": ["$subject_code", []] }}}},
{$sort: {nump: -1}}
])
And this is your complete query. It will sort by the field nump and uses [] emply list if the subject_code field is of some other type or Null
