Mongodb3
ASSIGNMENT 3
1] Find the titles of all the films starting with the letter ‘A’ released during the year 2019.
> db.Movie.find({Fname:/^A/},{$and:[{Release:{$gt:'2009'}},{Release:{$lt:'2019'}}]})
{ "_id" : ObjectId("5d7c7197244112940e51b775") }
> db.Movie.aggregate([{$group:{_id:'$Actors',name:{$push:'$Fname'}}}]).pretty()
{
"_id" : [
{
"Afname" : "Patrick",
"Alname" : "Louse"
}
],
"name" : [
"IT"
]
}
{
"_id" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"name" : [
"DDLJ",
"Love you Zindagi"
]
}
{
"_id" : [
{
"Afname" : [
"Akshay",
"Ritesh"
],
"Alname" : [
"Kumar",
"Deshmukh"
]
}
],
"name" : [
"Housefull"
]
}
{
"_id" : [
{
"Afname" : "Amanda",
"Alname" : "Rush"
}
],
"name" : [
"Anabelle"
]
}
2] Find the list of films acted by an actor "___________".
> db.Movie.find({'Actors.Afname':'Sharukh'}).pretty()
{
"_id" : ObjectId("5d7c72b0244112940e51b777"),
"Fid" : "2",
"Fname" : "DDLJ",
"Release" : "1999",
"Genre" : [
"Comedy",
"Romance"
],
"Actors" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"Director" : [
{
"Dfname" : "Karan",
"Dlname" : "Johar"
}
],
"Releasedetails" : [
{
"Place" : "Delhi",
"date" : "12/01/1999",
"rating" : "4"
}
]
}
{
"_id" : ObjectId("5d7c76de244112940e51b779"),
"Fid" : "4",
"Fname" : "Love you Zindagi",
"Release" : "2017",
"Genre" : "Drama",
"Actors" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"Director" : [
{
"Dfname" : "Gauri",
"Dlname" : "Khan"
}
],
"Releasedetails" : [
{
"Place" : "Delhi",
"date" : "15/05/2017",
"rating" : "4"
}
]
}
3] Find all the films released in 90s.
> db.Movie.find({$and:[{Release:{$gt:'1999'}},{Release:{$lt:'2019'}}]}).pretty()
{
"_id" : ObjectId("5d7c7197244112940e51b775"),
"Fid" : "1",
"Fname" : "Anabelle",
"Release" : "2017",
"Genre" : "Horror",
"Actors" : [
{
"Afname" : "Amanda",
"Alname" : "Rush"
}
],
"Director" : [
{
"Dfname" : "Michel",
"Dlname" : "Ken"
}
],
"Releasedetails" : [
{
"Place" : "England",
"date" : "09/12/2017",
"rating" : "3"
}
]
}
{
"_id" : ObjectId("5d7c759b244112940e51b778"),
"Fid" : "3",
"Fname" : "IT",
"Release" : "2017",
"Genre" : "Horror",
"Actors" : [
{
"Afname" : "Patrick",
"Alname" : "Louse"
}
],
"Director" : [
{
"Dfname" : "Gauri",
"Dlname" : "Khan"
}
],
"Releasedetails" : [
{
"Place" : [
"Delhi",
"Dehradun"
],
"date" : [
"12/01/2017",
"30/01/2017"
],
"rating" : "4"
}
]
}
{
"_id" : ObjectId("5d7c76de244112940e51b779"),
"Fid" : "4",
"Fname" : "Love you Zindagi",
"Release" : "2017",
"Genre" : "Drama",
"Actors" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"Director" : [
{
"Dfname" : "Gauri",
"Dlname" : "Khan"
}
],
"Releasedetails" : [
{
"Place" : "Delhi",
"date" : "15/05/2017",
"rating" : "4"
}
]
}
{
"_id" : ObjectId("5d7c78b6244112940e51b77a"),
"Fid" : "5",
"Fname" : "Housefull",
"Release" : "2014",
"Genre" : "Comedy",
"Actors" : [
{
"Afname" : [
"Akshay",
"Ritesh"
],
"Alname" : [
"Kumar",
"Deshmukh"
]
}
],
"Director" : [
{
"Dfname" : "Sajid",
"Dlname" : "Nadiwala"
}
],
"Releasedetails" : [
{
"Place" : "London",
"date" : "12/03/2014",
"rating" : "3"
}
]
}
4] Find all films belonging to “Comedy” and “Romance” genre.
> db.Movie.find({Genre:{$in:['Comedy','Romance']}}).pretty()
{
"_id" : ObjectId("5d7c72b0244112940e51b777"),
"Fid" : "2",
"Fname" : "DDLJ",
"Release" : "1999",
"Genre" : [
"Comedy",
"Romance"
],
"Actors" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"Director" : [
{
"Dfname" : "Karan",
"Dlname" : "Johar"
}
],
"Releasedetails" : [
{
"Place" : "Delhi",
"date" : "12/01/1999",
"rating" : "4"
}
]
}
{
"_id" : ObjectId("5d7c78b6244112940e51b77a"),
"Fid" : "5",
"Fname" : "Housefull",
"Release" : "2014",
"Genre" : "Comedy",
"Actors" : [
{
"Afname" : [
"Akshay",
"Ritesh"
],
"Alname" : [
"Kumar",
"Deshmukh"
]
}
],
"Director" : [
{
"Dfname" : "Sajid",
"Dlname" : "Nadiwala"
}
],
"Releasedetails" : [
{
"Place" : "London",
"date" : "12/03/2014",
"rating" : "3"
}
]
}
5] Find all the films having ‘3’ rating.
> db.Movie.find({'Releasedetails.rating':'3'}).pretty()
{
"_id" : ObjectId("5d7c7197244112940e51b775"),
"Fid" : "1",
"Fname" : "Anabelle",
"Release" : "2017",
"Genre" : "Horror",
"Actors" : [
{
"Afname" : "Amanda",
"Alname" : "Rush"
}
],
"Director" : [
{
"Dfname" : "Michel",
"Dlname" : "Ken"
}
],
"Releasedetails" : [
{
"Place" : "England",
"date" : "09/12/2017",
"rating" : "3"
}
]
}
{
"_id" : ObjectId("5d7c78b6244112940e51b77a"),
"Fid" : "5",
"Fname" : "Housefull",
"Release" : "2014",
"Genre" : "Comedy",
"Actors" : [
{
"Afname" : [
"Akshay",
"Ritesh"
],
"Alname" : [
"Kumar",
"Deshmukh"
]
}
],
"Director" : [
{
"Dfname" : "Sajid",
"Dlname" : "Nadiwala"
}
],
"Releasedetails" : [
{
"Place" : "London",
"date" : "12/03/2014",
"rating" : "3"
}
]
}
6] Arrange the film names in ascending order and release year should be in descending order.
> db.Movie.aggregate([{$sort:{Fname:1,Release:-1}}]).pretty()
{
"_id" : ObjectId("5d7c7197244112940e51b775"),
"Fid" : "1",
"Fname" : "Anabelle",
"Release" : "2017",
"Genre" : "Horror",
"Actors" : [
{
"Afname" : "Amanda",
"Alname" : "Rush"
}
],
"Director" : [
{
"Dfname" : "Michel",
"Dlname" : "Ken"
}
],
"Releasedetails" : [
{
"Place" : "England",
"date" : "09/12/2017",
"rating" : "3"
}
]
}
{
"_id" : ObjectId("5d7c72b0244112940e51b777"),
"Fid" : "2",
"Fname" : "DDLJ",
"Release" : "1999",
"Genre" : [
"Comedy",
"Romance"
],
"Actors" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"Director" : [
{
"Dfname" : "Karan",
"Dlname" : "Johar"
}
],
"Releasedetails" : [
{
"Place" : "Delhi",
"date" : "12/01/1999",
"rating" : "4"
}
]
}
{
"_id" : ObjectId("5d7c78b6244112940e51b77a"),
"Fid" : "5",
"Fname" : "Housefull",
"Release" : "2014",
"Genre" : "Comedy",
"Actors" : [
{
"Afname" : [
"Akshay",
"Ritesh"
],
"Alname" : [
"Kumar",
"Deshmukh"
]
}
],
"Director" : [
{
"Dfname" : "Sajid",
"Dlname" : "Nadiwala"
}
],
"Releasedetails" : [
{
"Place" : "London",
"date" : "12/03/2014",
"rating" : "3"
}
]
}
{
"_id" : ObjectId("5d7c759b244112940e51b778"),
"Fid" : "3",
"Fname" : "IT",
"Release" : "2017",
"Genre" : "Horror",
"Actors" : [
{
"Afname" : "Patrick",
"Alname" : "Louse"
}
],
"Director" : [
{
"Dfname" : "Gauri",
"Dlname" : "Khan"
}
],
"Releasedetails" : [
{
"Place" : [
"Delhi",
"Dehradun"
],
"date" : [
"12/01/2017",
"30/01/2017"
],
"rating" : "4"
}
]
}
{
"_id" : ObjectId("5d7c76de244112940e51b779"),
"Fid" : "4",
"Fname" : "Love you Zindagi",
"Release" : "2017",
"Genre" : "Drama",
"Actors" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"Director" : [
{
"Dfname" : "Gauri",
"Dlname" : "Khan"
}
],
"Releasedetails" : [
{
"Place" : "Delhi",
"date" : "15/05/2017",
"rating" : "4"
}
]
}
> db.Movie.find().sort({Fname:1},{Release:-1}).pretty()
{
"_id" : ObjectId("5d7c7197244112940e51b775"),
"Fid" : "1",
"Fname" : "Anabelle",
"Release" : "2017",
"Genre" : "Horror",
"Actors" : [
{
"Afname" : "Amanda",
"Alname" : "Rush"
}
],
"Director" : [
{
"Dfname" : "Michel",
"Dlname" : "Ken"
}
],
"Releasedetails" : [
{
"Place" : "England",
"date" : "09/12/2017",
"rating" : "3"
}
]
}
{
"_id" : ObjectId("5d7c72b0244112940e51b777"),
"Fid" : "2",
"Fname" : "DDLJ",
"Release" : "1999",
"Genre" : [
"Comedy",
"Romance"
],
"Actors" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"Director" : [
{
"Dfname" : "Karan",
"Dlname" : "Johar"
}
],
"Releasedetails" : [
{
"Place" : "Delhi",
"date" : "12/01/1999",
"rating" : "4"
}
]
}
{
"_id" : ObjectId("5d7c78b6244112940e51b77a"),
"Fid" : "5",
"Fname" : "Housefull",
"Release" : "2014",
"Genre" : "Comedy",
"Actors" : [
{
"Afname" : [
"Akshay",
"Ritesh"
],
"Alname" : [
"Kumar",
"Deshmukh"
]
}
],
"Director" : [
{
"Dfname" : "Sajid",
"Dlname" : "Nadiwala"
}
],
"Releasedetails" : [
{
"Place" : "London",
"date" : "12/03/2014",
"rating" : "3"
}
]
}
{
"_id" : ObjectId("5d7c759b244112940e51b778"),
"Fid" : "3",
"Fname" : "IT",
"Release" : "2017",
"Genre" : "Horror",
"Actors" : [
{
"Afname" : "Patrick",
"Alname" : "Louse"
}
],
"Director" : [
{
"Dfname" : "Gauri",
"Dlname" : "Khan"
}
],
"Releasedetails" : [
{
"Place" : [
"Delhi",
"Dehradun"
],
"date" : [
"12/01/2017",
"30/01/2017"
],
"rating" : "4"
}
]
}
{
"_id" : ObjectId("5d7c76de244112940e51b779"),
"Fid" : "4",
"Fname" : "Love you Zindagi",
"Release" : "2017",
"Genre" : "Drama",
"Actors" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"Director" : [
{
"Dfname" : "Gauri",
"Dlname" : "Khan"
}
],
"Releasedetails" : [
{
"Place" : "Delhi",
"date" : "15/05/2017",
"rating" : "4"
}
]
}
7] Sort the actors in ascending order according to their age.
> db.Actor.aggregate({$group:{_id:'$fname',Age:{$push:'$age'}}}).pretty()
{ "_id" : "ShahRukh", "Age" : [ "52" ] }
8] Find movies that are comedies or Romance and are released after 1999.
> db.Movie.find({$and:[{Genre:{$in:['Comedy','Romance']}},{Release:{$gt:'1999'}}]}).pretty()
{
"_id" : ObjectId("5d7c78b6244112940e51b77a"),
"Fid" : "5",
"Fname" : "Housefull",
"Release" : "2014",
"Genre" : "Comedy",
"Actors" : [
{
"Afname" : [
"Akshay",
"Ritesh"
],
"Alname" : [
"Kumar",
"Deshmukh"
]
}
],
"Director" : [
{
"Dfname" : "Sajid",
"Dlname" : "Nadiwala"
}
],
"Releasedetails" : [
{
"Place" : "London",
"date" : "12/03/2014",
"rating" : "3"
}
]
}
9] Show the latest 2 films acted by an actor “_________”.
> db.Movie.find({'Actors.Afname':'Sharukh'}).sort({_id:-1}).limit(2).pretty()
{
"_id" : ObjectId("5d7c76de244112940e51b779"),
"Fid" : "4",
"Fname" : "Love you Zindagi",
"Release" : "2017",
"Genre" : "Drama",
"Actors" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"Director" : [
{
"Dfname" : "Gauri",
"Dlname" : "Khan"
}
],
"Releasedetails" : [
{
"Place" : "Delhi",
"date" : "15/05/2017",
"rating" : "4"
}
]
}
{
"_id" : ObjectId("5d7c72b0244112940e51b777"),
"Fid" : "2",
"Fname" : "DDLJ",
"Release" : "1999",
"Genre" : [
"Comedy",
"Romance"
],
"Actors" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"Director" : [
{
"Dfname" : "Karan",
"Dlname" : "Johar"
}
],
"Releasedetails" : [
{
"Place" : "Delhi",
"date" : "12/01/1999",
"rating" : "4"
}
]
}
10] List the titles of films acted by actors “________” and “___________”.
> db.Movie.aggregate({$match:{'Actors.Afname':{$in:['Akshay','Ritesh']}}}).pretty()
{
"_id" : ObjectId("5d7c78b6244112940e51b77a"),
"Fid" : "5",
"Fname" : "Housefull",
"Release" : "2014",
"Genre" : "Comedy",
"Actors" : [
{
"Afname" : [
"Akshay",
"Ritesh"
],
"Alname" : [
"Kumar",
"Deshmukh"
]
}
],
"Director" : [
{
"Dfname" : "Sajid",
"Dlname" : "Nadiwala"
}
],
"Releasedetails" : [
{
"Place" : "London",
"date" : "12/03/2014",
"rating" : "3"
}
]
}
> db.Movie.find({'Releasedetails.Place':'Delhi'}).pretty()
{
"_id" : ObjectId("5d7c72b0244112940e51b777"),
"Fid" : "2",
"Fname" : "DDLJ",
"Release" : "1999",
"Genre" : [
"Comedy",
"Romance"
],
"Actors" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"Director" : [
{
"Dfname" : "Karan",
"Dlname" : "Johar"
}
],
"Releasedetails" : [
{
"Place" : "Delhi",
"date" : "12/01/1999",
"rating" : "4"
}
]
}
{
"_id" : ObjectId("5d7c759b244112940e51b778"),
"Fid" : "3",
"Fname" : "IT",
"Release" : "2017",
"Genre" : "Horror",
"Actors" : [
{
"Afname" : "Patrick",
"Alname" : "Louse"
}
],
"Director" : [
{
"Dfname" : "Gauri",
"Dlname" : "Khan"
}
],
"Releasedetails" : [
{
"Place" : [
"Delhi",
"Dehradun"
],
"date" : [
"12/01/2017",
"30/01/2017"
],
"rating" : "4"
}
]
}
{
"_id" : ObjectId("5d7c76de244112940e51b779"),
"Fid" : "4",
"Fname" : "Love you Zindagi",
"Release" : "2017",
"Genre" : "Drama",
"Actors" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"Director" : [
{
"Dfname" : "Gauri",
"Dlname" : "Khan"
}
],
"Releasedetails" : [
{
"Place" : "Delhi",
"date" : "15/05/2017",
"rating" : "4"
}
]
}
11] Retrieve films with actor details.
> db.Movie.aggregate([{$group:{_id:'$Actors',title1:{$push:'$Fname'}}}]).pretty()
{
"_id" : [
{
"Afname" : "Patrick",
"Alname" : "Louse"
}
],
"title1" : [
"IT"
]
}
{
"_id" : [
{
"Afname" : "Sharukh",
"Alname" : "Khan"
}
],
"title1" : [
"DDLJ",
"Love you Zindagi"
]
}
{
"_id" : [
{
"Afname" : [
"Akshay",
"Ritesh"
],
"Alname" : [
"Kumar",
"Deshmukh"
]
}
],
"title1" : [
"Housefull"
]
}
{
"_id" : [
{
"Afname" : "Amanda",
"Alname" : "Rush"
}
],
"title1" : [
"Anabelle"
]
}
Comments
Post a Comment