Compare between 2 fields of a document in MongoDB

Question

Consider an Invoice collection with the following documents:

{  "invoice_no" : "1001", "payable_amount" : 100, "paid_amount" : 50 }
{  "invoice_no" : "1002", "payable_amount" : 200, "paid_amount" : 200 }
{  "invoice_no" : "1003", "payable_amount" : 300, "paid_amount" : 150 }
{  "invoice_no" : "1004", "payable_amount" : 400, "paid_amount" : 400 }

Need those documents where payable_amount > paid_amount

Expected Result:

{  "invoice_no" : "1001", "payable_amount" : 100, "paid_amount" : 50 }
{  "invoice_no" : "1003", "payable_amount" : 300, "paid_amount" : 150 }

can you please look into this, https://stackoverflow.com/questions/8433046/mongodb-query-with-fields-in-the-same-documents — Krushnakant Ladani, Aug 24 '17 at 05:24

glytching · Accepted Answer · 2017-08-24T07:56:11.853

You'll find plenty of examples of using the $where operator for this but the aggregation framework can do this for you ...

db.collection.aggregate([
    {
        "$addFields": {
            "isPayableAmountGreater": { "$cmp": [ "$payable_amount", "$paid_amount" ] }
        }
    },
    { "$match": { "isPayableAmountGreater": 1 } }
])

Or, even simpler and without creating the (probably unwanted) isPayableAmountGreater quasi attribute:

db.collection.aggregate([
    {
        "$redact": {
            "$cond": [
                { "$gt": [ "$payable_amount", "$paid_amount" ] },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

Compare between 2 fields of a document in MongoDB

1 Answers1

Linked