19

I have a document like below : 

{
  "user_id": NumberLong(1),
  "updated_at": ISODate("2016-11-17T09:35:56.200Z"),
  "created_at": ISODate("2016-11-17T09:35:07.981Z"),
  "banners": {
    "normal_x970h90": "/images/banners/4/582d79cb3aef567d64621be9/photo-1440700265116-fe3f91810d72.jpg",
    "normal_x468h60": "/images/banners/4/582d79cb3aef567d64621be9/photo-1433354359170-23a4ae7338c6.jpg",
    "normal_x120h600": "/images/banners/4/582d79cb3aef567d64621be9/photo-1452570053594-1b985d6ea890.jpg"
  },
  "name": "jghjghjghj",
  "budget": "2000",
  "plan": null,
  "daily_budget": "232323",
  "daily_budget_auto": "",
  "href": "qls2.ir",
  "targets": {
    "cats": [
      "fun",
      "news"
    ],
    "region": "inIran",
    "iran_states": null,
    "os": "all",
    "gold_network": true,
    "dont_show_between_1_n_8": true
  },
  "payment": {
    "bank": "mellat",
    "tax": "add"
  },
  "click_cost": "102000",
  "status": null
}

I want to check if budget is lower than click_cost while I'm checking some other parameters in my query :

db.bcamp.aggregate(
    [
        {
            $match:{
                $and: [ 
                    {"targets.cats":{
                        "$in" : ["all"]
                        }
                    },

                    {"banners.normal_x970h90":{
                        "$exists":true
                        }
                    },

                    {"href": {
                        $nin: ["qls.ir"]
                        }
                    }
                ]
            }
        }
    ]).pretty();

I have tried compare method like : 

db.bcamp.aggregate(
    [
        {$project: {ab: {$cmp: ['$budget','$clickcost']}}},
        {$match: {ab:{$gt:1}}}
    ]).pretty();

But I was getting wrong result, it returns always 4 document which their budget may or may not be grater than click_cost, which it means it fetch wrong data.

How can I add that comparison to my mongoDB pipeline?

s7vr
  • 73,656
  • 11
  • 106
  • 127
Mohammad_Hosseini
  • 2,481
  • 3
  • 30
  • 53

1 Answers1

42

The problem is not with comparison operator, its the type of value you are comparing. You should change types of variable to Numbers. Change your match to 1, -1 or 0 based on your comparison.

db.bcamp.aggregate(
[
  {$project: {ab: {$cmp: ['$budget','$clickcost']}}},
  {$match: {ab:{$eq:1}}}
]).pretty();

You can use $expr in 3.6 version.

db.bcamp.aggregate(
[
  {$match: {$expr: {$eq: ["$budget", "$clickcost"]}}}
]).pretty();

Or

db.bcamp.find(
 {$expr: {$eq: ["$budget", "$clickcost"]}}
).pretty();
s7vr
  • 73,656
  • 11
  • 106
  • 127
  • when I change the values to int budget is changing to NumberLong() but click_cost is be like : "click_cost" : 150 – Mohammad_Hosseini Dec 31 '16 at 04:57
  • How are you updating those values ? The numbers are default stored as float. You have to update them as NumberLong("somevalue") to be a long value. Something like this db.collection.update( { user_id: 1 }, { $set: { click_cost: NumberLong("102000") } } ). NumberInt for 32 bit values. – s7vr Dec 31 '16 at 05:04
  • Ok I've changed them into NumberLong(...) and then I ran your code but I got 4 object in result- which my objects budget are lower than click_cost and some of them have no budget or click_cost value - how is this happening ? there is no object with the budget same as it's click_cost – Mohammad_Hosseini Dec 31 '16 at 05:22
  • Change the match to -1 if you want rows where budget values are lower than click cost. -1 if the first value is less than the second. 1 if the first value is greater than the second. 0 if the two values are equivalent from the docs – s7vr Dec 31 '16 at 05:25
  • Looks like there is a typo click_cost vs clickcost in the aggregate query – s7vr Dec 31 '16 at 05:29
  • Not sure what you are asking. You can use the above answer the way you want if it worked – s7vr Dec 31 '16 at 05:37