CODEWITHSUNDEEP
mongodb
Deepanshu Gautam
Deepanshu Gautam

Reputation: 59

How to write mongo query

How I can get the total number of seats available for a particular movie (seats present in all the theatres for that movie) from the mongodb schema below.

I need to write a mongo query to get the results

{
    "_id" : ObjectId("5d637b5ce27c7d60e5c42ae7"),
    "name" : "Bangalore",
    "movies" : [
        {
            "name" : "KGF",
            "theatres" : [
                {
                    "name" : "PVR",
                    "seats" : 45
                },
                {
                    "name" : "IMAX",
                    "seats" : 46
                }
            ]
        },
        {
            "name" : "Avengers",
            "theatres" : [
                {
                    "name" : "IMAX",
                    "seats" : 50
                }
            ]
        }
    ],
    "_class" : "com.BMS_mongo.ZZ_BMS_mongo_demo.Entity.CityInfo"
}

I have written this code : 

db.cities.aggregate( [ 
    { "$unwind" : "$movies" }, { "$unwind" : "$theatres" } , 
    { "$group" : { _id : "$movies.theatre`enter code here`s.seats" , 
        total : { "$sum" : "$seats" } } 
    } 
] )

My schema: image

Upvotes: 1

Views: 111

Answers (3)

Deepanshu Gautam
Deepanshu Gautam

Reputation: 59

I got the answer using this query ...

db.cities.aggregate( [ 
    { "$match" : { "name" : "Bangalore" } },
    { "$unwind" : "$movies" } , 
    { "$match" : {"movies.name" : "KGF"} }, 
    { "$unwind" : "$theatres" },
    { "$group" : { _id : "$movies.name", total : { "$sum" : "$movies.theatres.seats" 
                  } } } 
] )

Upvotes: 0

Dexter
Dexter

Reputation: 982

agg pipeline

Query:

db.movie.aggregate([{ $unwind: {  path: "$movies",} }, 
{ $unwind: {  path: "$movies.theatres",} }, 
{ $group: {  _id: "$movies.name",  "moviename": { $first: "$movies.name"  },
"totalSeats": { $sum: "$movies.theatres.seats"  }} }])

Upvotes: 0

Himanshu Sharma
Himanshu Sharma

Reputation: 3010

The following query can get us the expected output:

db.collection.aggregate([
    {
        $unwind:"$movies"
    },
    {
        $unwind:"$movies.theatres"
    },
    {
        $group:{
            "_id":"$movies.name",
            "movie":{
                $first:"$movies.name"
            },
            "totalSeats":{
                $sum:"$movies.theatres.seats"
            }
        }
    },
    {
        $project:{
            "_id":0
        }
    }
]).pretty()

Data set:

{
    "_id" : ObjectId("5d637b5ce27c7d60e5c42ae7"),
    "name" : "Bangalore",
    "movies" : [
        {
            "name" : "KGF",
            "theatres" : [
                {
                    "name" : "PVR",
                    "seats" : 45
                },
                {
                    "name" : "IMAX",
                    "seats" : 46
                }
            ]
        },
        {
            "name" : "Avengers",
            "theatres" : [
                {
                    "name" : "IMAX",
                    "seats" : 50
                }
            ]
        }
    ],
    "_class" : "com.BMS_mongo.ZZ_BMS_mongo_demo.Entity.CityInfo"
}

Output:

{ "movie" : "Avengers", "totalSeats" : 50 }
{ "movie" : "KGF", "totalSeats" : 91 }

Upvotes: 3

Related Questions