MongoDb query condition on comparing 2 fields

Question

I have a collection T  with 2 fields  Grade1 and Grade2  and I want to select those with condition Grade1  gt  Grade2  how can I get a query like in MySQL   Select   from T Where Grade1  gt  Grade2

User · Answer

If your query consists only of the  where operator  you can pass in just the JavaScript expression   db T find  this Grade1  gt  this Grade2        For greater performance  run an aggregate operation that has a  redact pipeline to filter the documents which satisfy the given condition    The  redact pipeline incorporates the functionality of  project and  match to implement field level redaction where it will return all documents matching the condition using   KEEP and removes from the pipeline results those that don t match using the   PRUNE variable     Running the following aggregate operation filter the documents more efficiently than using  where for large collections as this uses a single pipeline and native MongoDB operators  rather than JavaScript evaluations with  where  which can slow down the query   db T aggregate                   redact                   cond                         gt       Grade1     Grade2                          KEEP                      PRUNE                                     which is a more simplified version of incorporating the two pipelines  project and  match   db T aggregate                   project                  isGrade1Greater       cmp       Grade1     Grade2                    Grade1   1               Grade2   1               OtherFields   1                                           match      isGrade1Greater   1          With MongoDB 3 4 and newer   db T aggregate                   addFields                  isGrade1Greater       cmp       Grade1     Grade2                               match      isGrade1Greater   1

User · Answer

You can use a  where  Just be aware it will be fairly slow  has to execute Javascript code on every record  so combine with indexed queries if you can  db T find     where  function     return this Grade1  gt  this Grade2         or more compact  db T find     where    quot this Grade1  gt  this Grade2 quot        UPD for mongodb v 3 6  you can use  expr as described in recent answer

User · Answer

You can use  expr   3 6 mongo version operator   to use aggregation functions in regular query    Compare query operators vs aggregation comparison operators   Regular Query   db T find   expr   gt    Grade1     Grade2        Aggregation Query   db T aggregate   match   expr   gt    Grade1     Grade2

User · Answer

In case performance is more important than readability and as long as your condition consists of simple arithmetic operations  you can use aggregation pipeline  First  use  project to calculate the left hand side of the condition  take all fields to left hand side   Then use  match to compare with a constant and filter  This way you avoid javascript execution  Below is my test in python   import pymongo from random import randrange  docs      Grade1   randrange 10    Grade2   randrange 10   for    in range 100000    coll   pymongo MongoClient   test db grades coll insert many docs    Using aggregate    timeit -n1 -r1 list coll aggregate                   project                  diff      subtract      Grade1     Grade2                  Grade1   1               Grade2   1                                  match     diff      gt   0               1 loop  best of 1  192 ms per loop  Using find and  where    timeit -n1 -r1 list coll find    where    this Grade1  gt  this Grade2       1 loop  best of 1  4 54 s per loop

[mongodb] MongoDb query condition on comparing 2 fields

Examples related to mongodb

Examples related to compare