MongoDb query condition when comparing two fields

Question

I have a collection T , with two fields: Grade1 and Grade2 , and I want to select those with the condition Grade1 > Grade2 , how can I get a query like in MySQL?

 Select * from T Where Grade1 > Grade2 

Answer 1

You can use $ where. Just keep in mind that this will be pretty slow (should execute Javascript code for each entry), so combine with indexed queries if you can.

 db.T.find( { $where: function() { return this.Grade1 > this.Grade2 } } ); 

or more compact:

 db.T.find( { $where : "this.Grade1 > this.Grade2" } ); 

UPD for mongodb v. 3. 6+

you can use $expr as described in a recent answer

Answer 2

If your request consists only of the $where operator, you can only pass a JavaScript expression:

 db.T.find("this.Grade1 > this.Grade2"); 

---

For best performance, run the aggregate operation with the $redact to filter documents that satisfy this condition.

The $redact includes the functionality of $project and $match to implement a field where it will return all documents matching the condition using $$KEEP and remove those that don’t use the $$PRUNE variable from the pipeline results.

---

Performing the following aggregation operation filters documents more efficiently than using $where for large collections, since it uses a single pipeline and MongoDB's own 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 the inclusion of 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 later:

 db.T.aggregate([ { "$addFields": { "isGrade1Greater": { "$cmp": [ "$Grade1", "$Grade2" ] } } }, { "$match": { "isGrade1Greater": 1 } } ]) 

Answer 3

You can use $ expr (operator version 3.6 of mongo) to use the aggregation functions in a regular query.

Comparison of query operators

Normal query:

 db.T.find({$expr:{$gt:["$Grade1", "$Grade2"]}}) 

Aggregation Request:

 db.T.aggregate({$match:{$expr:{$gt:["$Grade1", "$Grade2"]}}}) 

Answer 4

If performance is more important than readability, and while your condition consists of simple arithmetic, you can use the aggregation pipeline. First use $ project to calculate the left side of the condition (take all the fields to the left). Then use $ match to compare with 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) 

Use of the unit:

 %timeit -n1 -r1 list(coll.aggregate([ { '$project': { 'diff': {'$subtract': ['$Grade1', '$Grade2']}, 'Grade1': 1, 'Grade2': 1 } }, { '$match': {'diff': {'$gt': 0}} } ])) 

1, best of 1: 192 ms per cycle

Using find and $ where:

 %timeit -n1 -r1 list(coll.find({'$where': 'this.Grade1 > this.Grade2'})) 

1, best 1: 4.54 s per cycle

Answer 5

{"_id": ObjectId ("5cb74e7d7c0b004588331dec"), "id": 10000505506.0, "shop_id": 1000113031, "__v": 0, "haraoes_extend_data": {"collection_id": []}, published ": false", is_deted ": 0," body_html ":"

gggggggg

"," body_plain ": null," creation_at ": ISODate (" 2019-04-11T09: 30: 24.502Z ")," handle ":" imac "," images ": [{" src ":" https: / /product.hara.vn/1000113031/product/jessy-smith-534048-unsplash_af6078d228b2485098b519452e32f09f.jpg "," file_name ":" jessy-smith-534048-unsplash_af6078, 10000 ": 0505985_85 create 501 0ate 508 085 085 085 085 085 085 085 085 04-11T09: 30: 36.436Z ")," updated_at ": ISODate (" 2019-04-11T09: 30: 37.205Z ")," _id ": ObjectId (" 5cb83a293002553558094cbb ")," id ": 10002753793.0," attachment ": null," variable_ids ": []}]," product_type ":" imac "," publ_at ": ISODate (" 2019-04-11T09: 30: 24.445Z ")," area_key ":" global "," tags ":" imac "," suffix_pattern ":" product "," header ":" Imac "," updated_at ": ISODate (" 2019-04-12T10: 15: 41.312Z ")," options ": [{" inventory_advance ": {" qty_avaiable ": 0," qty_onhand ": 0," qty_commited ": 0," qty_incoming ": 0}," sync_ghn_wfs_status ":" SYNCED "," sync_ghn_wfs_error ": nul l, "barcode": "I M00000001", "inventory_management": "haravan", "inventory_policy": "deny", "sku": "imac1", "title": "Default title", "option1" : "Default header", "option2": null, "option3": null, "compare_at_price": 0, "gram": 0, "inventory_quantity": 1, "old_inventory_quantity": 1, "inventory_quantity_adjustment": null, " position ": 1," price ": 0," product_id ": 10000505506.0," image_id ": null," require_shipping ": true," taxable ": true," creation_at ": ISODate (" 2019-04-11T09: 30: 24.502Z ")," updated_at ": ISODate (" 2019-04-12T10: 15: 41.312Z ")," _id ": ObjectId (" 5cb83a293002553558094cba ")," executement_service ": null," id ": 101559365," sync_ghn_wfs_id ": 43137," sync_ghn_wfs_at_ 201: ": -04-18T09: 17: 02.088Z")}], "vendor": "apple", "options": [{"name": "Title", "position": 1, "product_id": 10000505506.0, "_id": ObjectId ("5cb83a293002553558094cb9"), "id": 1003985654}], "on ly_hide_from_list ": false," not_allow_promotion ": false}

I have to find documents have updated_at greater than or equal to sync_ghn_wfs_last_sync_at, but it seems that they are not working

Answer 6

but does not work between two decimal fields. How can I compare 2 decimal fields?