CODEWITHSUNDEEP
mongodb
Ritika
Ritika

Reputation: 29

writing complex query in mongo DB

I want to write complex query in mongodb using following fields :

the query should return me result sets based upon the following conditions:

1) class = "a" && rate > "20000"

OR

2) class = "b" && (rate X hrs X 52) > "20000"

The records in the collection am trying to query looks something like this:

{
    "_id": "3434sfsf",
    "rate": "60",
    "class": "a"
},
{
    "_id": "sdsdsd",
    "rate": "60",
    "class": "b",
    "hrs": "8"
}

How can I write my query to achieve what am looking for... Thanks!

Upvotes: 2

Views: 18985

Answers (2)

Yathish Manjunath
Yathish Manjunath

Reputation: 2029

Please Try the below query :

Step 1 : we are filtering only those documents with "class" : "a" OR "class" : "b" and "hrs" key exists.

Step 2 : we are doing multiplication of "rate" , "hrs" with 52 and storing the result in the key "rateMultiply"

Step 3 : we are filtering the documents on the below mentioned criteria : class = "a" && rate > "20000" OR class = "b" && (rate X hrs X 52) > "20000"

Step 4 : we are displaying the documnets with only those fields which we need.

Lets create a collection " exp4 " and insert the below documents :

db.exp4.insert({ "_id" : "3434sfsf", "rate" : 60, "class" : "a" });
db.exp4.insert({ "_id" : "sdsdsd", "rate" : 60, "class" : "b", "hrs" : 8 });
db.exp4.insert({ "_id" : "123", "rate" : 30000, "class" : "a", "hrs" : 8 });
db.exp4.insert({ "_id" : "12567", "rate" : 12000, "class" : "b" });

Now lets QUERY the collection :

db.exp4.aggregate([
     { $match: 
       {$or : [ {"class" : "a"},
                {$and : [{"class":"b"},{"hrs": {"$exists" : 1}}]}
              ]
       }
     },
     { $project : 
        { rateMultiply : { $multiply: ["$rate","$hrs",52]}, 
         rate:1, class:1, hrs : 1 
        } 
     },
     { $match : 
       {$or : [ 
                { $and : [ {"class" : "a"} , 
                           {"rate" : {"$gt" : 20000}} 
                         ] 
                } , 
                { $and : [ {"class" : "b"}, 
                           {rateMultiply: {$gt:20000}}
                         ]
                 } 
               ] 
       }
     },
     { $project: {class : 1 , rate : 1 , hrs : 1 }
     }
  ])

RESULT :

{ "_id" : "sdsdsd", "rate" : 60, "class" : "b", "hrs" : 8 }
{ "_id" : "123", "rate" : 30000, "class" : "a", "hrs" : 8 }

Upvotes: 4

Neo-coder
Neo-coder

Reputation: 7840

Let's check your document structure looks like following way where rate and hrs in number 

{ "_id" : ObjectId("55c105d097658f88b5a2979e"), "rate" : 60, "class" : "a" }
{ "_id" : ObjectId("55c105d097658f88b5a2979f"), "rate" : 60, "class" : "b", "hrs" : 8 }
{ "_id" : ObjectId("55c105d097658f88b5a297a0"), "rate" : 30000, "class" : "a", "hrs" : 8 }
{ "_id" : ObjectId("55c105d097658f88b5a297a1"), "rate" : 3000, "class" : "b", "hrs" : 8 }

Then first you should calculate multiplication of rate and hrs separately using aggregation and multiplication $mulitiply takes parameter rate,hrs and 52 to calculate multiply value as below :

db.collectionName.aggregate({
"$project": {
    "multiply": {
        "$multiply": ["$rate", "$hrs", 52]
    },
    "rate": 1,
    "class": 1
}
})

so documents looks like :

{ "_id" : ObjectId("55c105d097658f88b5a2979e"), "rate" : 60, "class" : "a", "multiply" : null }
{ "_id" : ObjectId("55c105d097658f88b5a2979f"), "rate" : 60, "class" : "b", "multiply" : 24960 }
{ "_id" : ObjectId("55c105d097658f88b5a297a0"), "rate" : 30000, "class" : "a", "multiply" : 12480000 }
{ "_id" : ObjectId("55c105d097658f88b5a297a1"), "rate" : 3000, "class" : "b", "multiply" : 1248000 }

contains new key multiply now used this multiply in $match with $or and $and as below :

db.collectionName.aggregate({
    "$project": {
        "multiply": {
            "$multiply": ["$rate", "$hrs", 52]
        },
        "rate": 1,
        "class": 1
    }
}, {
    "$match": {
        "$or": [{
            "$and": [{
                "class": "a"
            }, {
                "rate": {
                    "$gt": 20000
                }
            }]
        }, {
            "$and": [{
                "class": "b"
            }, {
                "multiply": {
                    "$gt": 20000
                }
            }]
        }]
    }
})

Upvotes: 3

Related Questions