CODEWITHSUNDEEP
mongodbmongodb-query
made_in_india
made_in_india

Reputation: 2279

document returned by mongoShell query is zero for comparing column in same document

I have collection with something similar datastructure

{
   id: 1
   limit: {
        max: 10000,
        used: 0     
   }
}

and I tried running the below query but it is giving 0 results

db.getCollection('promos').aggregate(
[
{ $match: { id:  1} },
{$match: { $expr: {$gt ["limit.max" , "limit.used"]}}}

])

I also used the below query

db.getCollection('promos').aggregate(
[
{ $match: { id:  1} },
{$match: { "$limit.max": {$gt: "limit.used"}}}

])

None of them is giving the result . Any help will be appreciated.

Upvotes: 0

Views: 204

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151132

You need to prefix "field expressions" with the $. This also can be simply done in a .find()

db.getCollection('promos').find({
  "id": 1,
  "$expr": { "$gt": [ "$limit.max" , "$limit.used" ] }
})

Or a single $match stage if you really need to use aggregate instead:

db.getCollection('promos').aggregate([
  { "$match": {
    "id": 1,
    "$expr": { "$gt": [ "$limit.max" , "$limit.used" ] }
  }}
])

That's how $expr works and you can "mix it" with other regular query operators in the same query or pipeline stage.

Also see $gt for general usage examples

Of course if you don't actually even have MongoDB 3.6, then you use $redact instead:

db.getCollection('promos').aggregate([
  { "$match": { "id": 1 } },
  { "$redact": {
    "$cond": {
      "if": { "$gt": [ "$limit.max" , "$limit.used" ] },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

Or use $where. Works in all versions:

db.getCollection('promos').find({
  "id": 1,
  "$where": "this.limit.max > this.limit.used"
})

Upvotes: 1

Related Questions