Example collection
"maxdemo" is as follows:
{
"_id"
: ObjectId("5ba49f63a3b0f1fce83f3f3f"),
"name"
: "sangram",
"marks"
: 76.0
}
{
"_id"
: ObjectId("5ba49f73a3b0f1fce83f3f40"),
"name"
: "sagar",
"marks"
: 77.0
}
{
"_id"
: ObjectId("5ba49f7da3b0f1fce83f3f41"),
"name"
: "sachin",
"marks"
: 85.0
}
{
"_id"
: ObjectId("5ba49f87a3b0f1fce83f3f42"),
"name"
: "swapnil",
"marks"
: 80.0
}
{
"_id"
: ObjectId("5ba49f90a3b0f1fce83f3f43"),
"name"
: "saurabh",
"marks"
: 90.0
}
{
"_id"
: ObjectId("5ba49fa0a3b0f1fce83f3f44"),
"name"
: "shashank",
"marks"
: 79.0
}
{
"_id"
: ObjectId("5ba49fbba3b0f1fce83f3f45"),
"name"
: "sunil",
"marks"
: 84.0
}
{
"_id"
: ObjectId("5ba49fcaa3b0f1fce83f3f46"),
"name"
: "sujay",
"marks"
: 64.0
}
{
"_id"
: ObjectId("5ba49fe4a3b0f1fce83f3f47"),
"name"
: "subodh",
"marks"
: 74.0
}
{
"_id"
: ObjectId("5ba49ff6a3b0f1fce83f3f48"),
"name"
: "sunit",
"marks"
: 60.0
}
Finding 3rd max
of marks
db.maxdemo.find({}).sort({"marks":-1}).skip(2).limit(1)
Finding nth max
of marks
db.maxdemo.find({}).sort({"marks":-1}).skip(n-1).limit(1)
Updating our
collection to below using
db.maxdemo.update({"name"
: "vineet"},{$set: { "subject": "maths"
}})
by changing subject
& name field values.
{
"_id"
: ObjectId("5ba49f63a3b0f1fce83f3f3f"),
"name"
: "sangram",
"marks"
: 76.0,
"subject"
: "english"
}
{
"_id"
: ObjectId("5ba49f73a3b0f1fce83f3f40"),
"name"
: "sagar",
"marks"
: 77.0,
"subject"
: "marathi"
}
{
"_id"
: ObjectId("5ba49f7da3b0f1fce83f3f41"),
"name"
: "sachin",
"marks"
: 85.0,
"subject"
: "geography"
}
{
"_id"
: ObjectId("5ba49f87a3b0f1fce83f3f42"),
"name"
: "swapnil",
"marks"
: 80.0,
"subject"
: "geography"
}
{
"_id"
: ObjectId("5ba49f90a3b0f1fce83f3f43"),
"name"
: "saurabh",
"marks"
: 90.0,
"subject"
: "maths"
}
{
"_id"
: ObjectId("5ba49fa0a3b0f1fce83f3f44"),
"name"
: "shashank",
"marks"
: 79.0,
"subject"
: "maths"
}
{
"_id"
: ObjectId("5ba49fbba3b0f1fce83f3f45"),
"name"
: "sunil",
"marks"
: 84.0,
"subject"
: "geography"
}
{
"_id"
: ObjectId("5ba49fcaa3b0f1fce83f3f46"),
"name"
: "sujay",
"marks"
: 64.0,
"subject"
: "marathi"
}
{
"_id"
: ObjectId("5ba49fe4a3b0f1fce83f3f47"),
"name"
: "subodh",
"marks"
: 74.0,
"subject"
: "english"
}
{
"_id"
: ObjectId("5ba49ff6a3b0f1fce83f3f48"),
"name"
: "sunit",
"marks"
: 60.0,
"subject"
: "english"
}
{
"_id"
: ObjectId("5ba4a1fdffaf9599885ab184"),
"name"
: "vivek",
"marks"
: 85.0,
"subject"
: "marathi"
}
{
"_id"
: ObjectId("5ba4a205ffaf9599885ab185"),
"name"
: "vijay",
"marks"
: 85.0
}
{
"_id"
: ObjectId("5ba4a20affaf9599885ab186"),
"name"
: "vineet",
"marks"
: 85.0,
"subject"
: "maths"
}
Now lets play with
this collection.
for sql query
select
subject,max(marks) from maxdemo_table group by subject
equivalent mongo
query is
db.maxdemo.aggregate([
{$group : {_id :
"$subject", maximum_marks : {$max : "$marks"}}}
])
for sql query
select max(marks)
from maxdemo_table
equivalent mongo
query is
db.maxdemo.aggregate([
{$group : {_id :
null, maximum_marks : {$max : "$marks"}}}
for sql query
select
subject,max(marks) from maxdemo_table group by subject having subject
="maths"
])
equivalent mongo
query is
db.maxdemo.aggregate([
{$group : {_id
: "$subject", maximum_marks : {$max : "$marks"}}},
{$match: { _id
: { $eq: "maths"} } },
])
for sql query
select
subject,max(marks) from maxdemo_table group by subject having
max(marks) = 85
equivalent mongo
query is
db.maxdemo.aggregate([
{$group : {_id
: "$subject", maximum_marks : {$max : "$marks"}}},
{$match: {
maximum_marks : { $eq: 85} } },
])
Getting max value
and all records on which max value calculated
db.maxdemo.aggregate([
{
$group : {
_id :
"$subject",
maximum_marks : {$max : "$marks"},
student:
{$push: "$$ROOT"}
}
}
])
Result:
{
"_id"
: "geography",
"maximum_marks" : 85.0,
"student"
: [
{
"_id"
: ObjectId("5ba49f7da3b0f1fce83f3f41"),
"name"
: "sachin",
"marks"
: 85.0,
"subject"
: "geography"
},
{
"_id"
: ObjectId("5ba49f87a3b0f1fce83f3f42"),
"name"
: "swapnil",
"marks"
: 80.0,
"subject"
: "geography"
},
{
"_id"
: ObjectId("5ba49fbba3b0f1fce83f3f45"),
"name"
: "sunil",
"marks"
: 84.0,
"subject"
: "geography"
}
]
}
{
"_id"
: "maths",
"maximum_marks" : 90.0,
"student"
: [
{
"_id"
: ObjectId("5ba49f90a3b0f1fce83f3f43"),
"name"
: "saurabh",
"marks"
: 90.0,
"subject"
: "maths"
},
{
"_id"
: ObjectId("5ba49fa0a3b0f1fce83f3f44"),
"name"
: "shashank",
"marks"
: 79.0,
"subject"
: "maths"
},
{
"_id"
: ObjectId("5ba4a20affaf9599885ab186"),
"name"
: "vineet",
"marks"
: 85.0,
"subject"
: "maths"
}
]
}
{
"_id"
: "marathi",
"maximum_marks" : 85.0,
"student"
: [
{
"_id"
: ObjectId("5ba49f73a3b0f1fce83f3f40"),
"name"
: "sagar",
"marks"
: 77.0,
"subject"
: "marathi"
},
{
"_id"
: ObjectId("5ba49fcaa3b0f1fce83f3f46"),
"name"
: "sujay",
"marks"
: 64.0,
"subject"
: "marathi"
},
{
"_id"
: ObjectId("5ba4a1fdffaf9599885ab184"),
"name"
: "vivek",
"marks"
: 85.0,
"subject"
: "marathi"
},
{
"_id"
: ObjectId("5ba4a205ffaf9599885ab185"),
"name"
: "vijay",
"subject"
: "marathi",
"marks"
: 85.0
}
]
}
{
"_id"
: "english",
"maximum_marks" : 76.0,
"student"
: [
{
"_id"
: ObjectId("5ba49f63a3b0f1fce83f3f3f"),
"name"
: "sangram",
"marks"
: 76.0,
"subject"
: "english"
},
{
"_id"
: ObjectId("5ba49fe4a3b0f1fce83f3f47"),
"name"
: "subodh",
"marks"
: 74.0,
"subject"
: "english"
},
{
"_id"
: ObjectId("5ba49ff6a3b0f1fce83f3f48"),
"name"
: "sunit",
"marks"
: 60.0,
"subject"
: "english"
}
]
}
Saving result
into new collection
var temp =
db.maxdemo.aggregate([
{
$group :
{
_id :
"$subject",
maximum_marks : {$max : "$marks"},
student:
{$push: "$$ROOT"}
}
},
{
$out :
"hc_hosting_stat"
}
])
Getting record by
comparison of two fields
just for
illustration purpose update a record as follows by adding new field
beside maximum marks called marks
{
"_id"
: "geography",
"maximum_marks" : 85.0,
"marks"
: 85.0,
"student"
: [
{
"_id"
: ObjectId("5ba49f7da3b0f1fce83f3f41"),
"name"
: "sachin",
"marks"
: 85.0,
"subject"
: "geography"
},
{
"_id"
: ObjectId("5ba49f87a3b0f1fce83f3f42"),
"name"
: "swapnil",
"marks"
: 80.0,
"subject"
: "geography"
},
{
"_id"
: ObjectId("5ba49fbba3b0f1fce83f3f45"),
"name"
: "sunil",
"marks"
: 84.0,
"subject"
: "geography"
}
]
}
and below
{
"_id"
: "maths",
"maximum_marks" : 90.0,
"marks"
: 89.0,
"student"
: [
{
"_id"
: ObjectId("5ba49f90a3b0f1fce83f3f43"),
"name"
: "saurabh",
"marks"
: 90.0,
"subject"
: "maths"
},
{
"_id"
: ObjectId("5ba49fa0a3b0f1fce83f3f44"),
"name"
: "shashank",
"marks"
: 79.0,
"subject"
: "maths"
},
{
"_id"
: ObjectId("5ba4a20affaf9599885ab186"),
"name"
: "vineet",
"marks"
: 85.0,
"subject"
: "maths"
}
]
}
Now lets
issue a mongo query
db.hc_hosting_stat.find({$where:
function() { return this.maximum_marks == this.marks } } );
Result:
{
"_id"
: "geography",
"maximum_marks" : 85.0,
"marks"
: 85.0,
"student"
: [
{
"_id"
: ObjectId("5ba49f7da3b0f1fce83f3f41"),
"name"
: "sachin",
"marks"
: 85.0,
"subject"
: "geography"
},
{
"_id"
: ObjectId("5ba49f87a3b0f1fce83f3f42"),
"name"
: "swapnil",
"marks"
: 80.0,
"subject"
: "geography"
},
{
"_id"
: ObjectId("5ba49fbba3b0f1fce83f3f45"),
"name"
: "sunil",
"marks"
: 84.0,
"subject"
: "geography"
}
]
}
we are getting all
records where maximum_marks matches marks.
For collection
"hc_hosting_stat"
{
"_id"
: "geography",
"maximum_marks"
: 85.0,
"marks"
: 85.0,
"student"
: [
{
"_id"
: ObjectId("5ba49f7da3b0f1fce83f3f41"),
"name"
: "sachin",
"marks"
: 85.0,
"subject"
: "geography"
},
{
"_id"
: ObjectId("5ba49f87a3b0f1fce83f3f42"),
"name"
: "swapnil",
"marks"
: 80.0,
"subject"
: "geography"
},
{
"_id"
: ObjectId("5ba49fbba3b0f1fce83f3f45"),
"name"
: "sunil",
"marks"
: 84.0,
"subject"
: "geography"
}
]
}
{
"_id"
: "maths",
"maximum_marks"
: 90.0,
"marks"
: 89.0,
"student"
: [
{
"_id"
: ObjectId("5ba49f90a3b0f1fce83f3f43"),
"name"
: "saurabh",
"marks"
: 90.0,
"subject"
: "maths"
},
{
"_id"
: ObjectId("5ba49fa0a3b0f1fce83f3f44"),
"name"
: "shashank",
"marks"
: 79.0,
"subject"
: "maths"
},
{
"_id"
: ObjectId("5ba4a20affaf9599885ab186"),
"name"
: "vineet",
"marks"
: 85.0,
"subject"
: "maths"
}
]
}
{
"_id"
: "marathi",
"maximum_marks"
: 85.0,
"student"
: [
{
"_id"
: ObjectId("5ba49f73a3b0f1fce83f3f40"),
"name"
: "sagar",
"marks"
: 77.0,
"subject"
: "marathi"
},
{
"_id"
: ObjectId("5ba49fcaa3b0f1fce83f3f46"),
"name"
: "sujay",
"marks"
: 64.0,
"subject"
: "marathi"
},
{
"_id"
: ObjectId("5ba4a1fdffaf9599885ab184"),
"name"
: "vivek",
"marks"
: 85.0,
"subject"
: "marathi"
},
{
"_id"
: ObjectId("5ba4a205ffaf9599885ab185"),
"name"
: "vijay",
"subject"
: "marathi",
"marks"
: 85.0
}
]
}
{
"_id"
: "english",
"maximum_marks"
: 76.0,
"student"
: [
{
"_id"
: ObjectId("5ba49f63a3b0f1fce83f3f3f"),
"name"
: "sangram",
"marks"
: 76.0,
"subject"
: "english"
},
{
"_id"
: ObjectId("5ba49fe4a3b0f1fce83f3f47"),
"name"
: "subodh",
"marks"
: 74.0,
"subject"
: "english"
},
{
"_id"
: ObjectId("5ba49ff6a3b0f1fce83f3f48"),
"name"
: "sunit",
"marks"
: 60.0,
"subject"
: "english"
}
]
}
Getting
subject topper and topped marks
db.hc_hosting_stat.find({}).forEach(function(item)
{
var new_item=[];
for(var i=0;i <
item.student.length;i++){
if(item.student[i].marks == item.maximum_marks){
new_item.push(item.student[i])
}
}
db.result.insert(new_item)
})
New result
collection is as below
{
"_id"
: ObjectId("5ba49f7da3b0f1fce83f3f41"),
"name"
: "sachin",
"marks"
: 85.0,
"subject"
: "geography"
}
{
"_id"
: ObjectId("5ba49f90a3b0f1fce83f3f43"),
"name"
: "saurabh",
"marks"
: 90.0,
"subject"
: "maths"
}
{
"_id"
: ObjectId("5ba4a1fdffaf9599885ab184"),
"name"
: "vivek",
"marks"
: 85.0,
"subject"
: "marathi"
}
{
"_id"
: ObjectId("5ba4a205ffaf9599885ab185"),
"name"
: "vijay",
"subject"
: "marathi",
"marks"
: 85.0
}
{
"_id"
: ObjectId("5ba49f63a3b0f1fce83f3f3f"),
"name"
: "sangram",
"marks"
: 76.0,
"subject"
: "english"
}
or
db.hc_hosting_stat.aggregate([
{
$project: {
maximum_marks: 1,
'topper': {
$filter: {
input: '$student',
as: 'item',
cond: {
$eq: ['$$item.marks', '$maximum_marks']
}
}
}
}
}
])
Result:
{
"_id" : "geography",
"maximum_marks" : 85.0,
"topper" : [
{
"_id" : ObjectId("5ba49f7da3b0f1fce83f3f41"),
"name" : "sachin",
"marks" : 85.0,
"subject" : "geography"
}
]
}
{
"_id" : "maths",
"maximum_marks" : 90.0,
"topper" : [
{
"_id" : ObjectId("5ba49f90a3b0f1fce83f3f43"),
"name" : "saurabh",
"marks" : 90.0,
"subject" : "maths"
}
]
}
{
"_id" : "marathi",
"maximum_marks" : 85.0,
"topper" : [
{
"_id" : ObjectId("5ba4a1fdffaf9599885ab184"),
"name" : "vivek",
"marks" : 85.0,
"subject" : "marathi"
},
{
"_id" : ObjectId("5ba4a205ffaf9599885ab185"),
"name" : "vijay",
"subject" : "marathi",
"marks" : 85.0
}
]
}
{
"_id" : "english",
"maximum_marks" : 76.0,
"topper" : [
{
"_id" : ObjectId("5ba49f63a3b0f1fce83f3f3f"),
"name" : "sangram",
"marks" : 76.0,
"subject" : "english"
}
]
}
combining two steps into one:
This query list all subject toppers keeping in concern that many student getting top score in a subject.e.g.vivek & vineet both score top in Marathi.
db.maxdemo.aggregate([
{
$group : {
_id : "$subject",
topscore : {$max : "$marks"},
student: {$push: "$$ROOT"}
}
},
{
$project:
{
topscore: 1,
_id:0,
'subject': '$_id',
'topper': {
$filter: {
input: '$student',
as: 'item',
cond: {
$eq: ['$$item.marks', '$topscore']
}
}
}
}
}
])
Result:
{
"topscore" : 85.0,
"subject" : "geography",
"topper" : [
{
"_id" : ObjectId("5ba49f7da3b0f1fce83f3f41"),
"name" : "sachin",
"marks" : 85.0,
"subject" : "geography"
}
]
}
{
"topscore" : 90.0,
"subject" : "maths",
"topper" : [
{
"_id" : ObjectId("5ba49f90a3b0f1fce83f3f43"),
"name" : "saurabh",
"marks" : 90.0,
"subject" : "maths"
}
]
}
{
"topscore" : 85.0,
"subject" : "marathi",
"topper" : [
{
"_id" : ObjectId("5ba4a1fdffaf9599885ab184"),
"name" : "vivek",
"marks" : 85.0,
"subject" : "marathi"
},
{
"_id" : ObjectId("5ba4a205ffaf9599885ab185"),
"name" : "vijay",
"subject" : "marathi",
"marks" : 85.0
}
]
}
{
"topscore" : 76.0,
"subject" : "english",
"topper" : [
{
"_id" : ObjectId("5ba49f63a3b0f1fce83f3f3f"),
"name" : "sangram",
"marks" : 76.0,
"subject" : "english"
}
]
}
db.hc_hosting_stat.aggregate([
{
$project: {
maximum_marks: 1,
'topper': {
$filter: {
input: '$student',
as: 'item',
cond: {
$eq: ['$$item.marks', '$maximum_marks']
}
}
}
}
}
])
Result:
{
"_id" : "geography",
"maximum_marks" : 85.0,
"topper" : [
{
"_id" : ObjectId("5ba49f7da3b0f1fce83f3f41"),
"name" : "sachin",
"marks" : 85.0,
"subject" : "geography"
}
]
}
{
"_id" : "maths",
"maximum_marks" : 90.0,
"topper" : [
{
"_id" : ObjectId("5ba49f90a3b0f1fce83f3f43"),
"name" : "saurabh",
"marks" : 90.0,
"subject" : "maths"
}
]
}
{
"_id" : "marathi",
"maximum_marks" : 85.0,
"topper" : [
{
"_id" : ObjectId("5ba4a1fdffaf9599885ab184"),
"name" : "vivek",
"marks" : 85.0,
"subject" : "marathi"
},
{
"_id" : ObjectId("5ba4a205ffaf9599885ab185"),
"name" : "vijay",
"subject" : "marathi",
"marks" : 85.0
}
]
}
{
"_id" : "english",
"maximum_marks" : 76.0,
"topper" : [
{
"_id" : ObjectId("5ba49f63a3b0f1fce83f3f3f"),
"name" : "sangram",
"marks" : 76.0,
"subject" : "english"
}
]
}
combining two steps into one:
This query list all subject toppers keeping in concern that many student getting top score in a subject.e.g.vivek & vineet both score top in Marathi.
db.maxdemo.aggregate([
{
$group : {
_id : "$subject",
topscore : {$max : "$marks"},
student: {$push: "$$ROOT"}
}
},
{
$project:
{
topscore: 1,
_id:0,
'subject': '$_id',
'topper': {
$filter: {
input: '$student',
as: 'item',
cond: {
$eq: ['$$item.marks', '$topscore']
}
}
}
}
}
])
Result:
{
"topscore" : 85.0,
"subject" : "geography",
"topper" : [
{
"_id" : ObjectId("5ba49f7da3b0f1fce83f3f41"),
"name" : "sachin",
"marks" : 85.0,
"subject" : "geography"
}
]
}
{
"topscore" : 90.0,
"subject" : "maths",
"topper" : [
{
"_id" : ObjectId("5ba49f90a3b0f1fce83f3f43"),
"name" : "saurabh",
"marks" : 90.0,
"subject" : "maths"
}
]
}
{
"topscore" : 85.0,
"subject" : "marathi",
"topper" : [
{
"_id" : ObjectId("5ba4a1fdffaf9599885ab184"),
"name" : "vivek",
"marks" : 85.0,
"subject" : "marathi"
},
{
"_id" : ObjectId("5ba4a205ffaf9599885ab185"),
"name" : "vijay",
"subject" : "marathi",
"marks" : 85.0
}
]
}
{
"topscore" : 76.0,
"subject" : "english",
"topper" : [
{
"_id" : ObjectId("5ba49f63a3b0f1fce83f3f3f"),
"name" : "sangram",
"marks" : 76.0,
"subject" : "english"
}
]
}
No comments:
Post a Comment