CODEWITHSUNDEEP
mongodbmongodb-query
Victor
Victor

Reputation: 1435

How can I subtract an array in MongoDB

The data in the MongoDB is like this:

{ "_id" : ObjectId("5b30d15d1e8b07d6a659ea81"), "group" : "2", "data" : { "count" : [  {  "f1" : 56,  "f2" : 48 },  {  "f1" : 95,  "f2" : 6 } ] } }
{ "_id" : ObjectId("5b30d1671e8b07d6a659ea82"), "group" : "3", "data" : { "count" : [  {  "f1" : 15,  "f2" : 72 },  {  "f1" : 56,  "f2" : 74 } ] } }
{ "_id" : ObjectId("5b30d1731e8b07d6a659ea83"), "group" : "4", "data" : { "count" : [  {  "f1" : 12,  "f2" : 96 },  {  "f1" : 85,  "f2" : 85 } ] } }
{ "_id" : ObjectId("5b30d17c1e8b07d6a659ea84"), "group" : "5", "data" : { "count" : [  {  "f1" : 73,  "f2" : 56 },  {  "f1" : 96,  "f2" : 79 } ] } }

I need to get the data where f1 - f2 > 0, which means I need the first and fourth records and filter out the second and third records. How can I do that?

Is there something similar to db.events.find({"$and":[{"difference":{"$subtract":{"data.count.f1": "data.count.f2"}}}, {"difference": {"$gt": 0}}]})?

Upvotes: 1

Views: 1070

Answers (2)

chridam
chridam

Reputation: 103365

Using the $expr operator (found in versions 3.6 and above) as the find() method query expression, you can make use of the $anyElementTrue operator which takes in an array as a set and returns true if any of the elements are true and false otherwise. An empty array returns false.

To produce the array with elements that evaluate to true or false based on the subtract logic above, use $map to iterate over the count array and return the evaluated logic.

The following shows this in action:

db.getCollection('collection').find(
    {
        "$expr": {
            "$anyElementTrue": [
                { "$map": {
                    "input": "$data.count",
                    "as": "count",
                    "in": { 
                        "$gt": [
                            { "$subtract": [ "$$count.f1", "$$count.f2" ] },
                            0
                        ]
                    }
                } }
            ]
        }
    }
)

For earlier versions you can use $redact as follows:

db.getCollection('collection').aggregate([
    {
        "$redact": {
            "$cond": [
                {
                    "$anyElementTrue": [
                        { "$map": {
                            "input": "$data.count",
                            "as": "count",
                            "in": { 
                                "$gt": [
                                    { "$subtract": [ "$$count.f1", "$$count.f2" ] },
                                    0
                                ]
                            }
                        } }
                    ]
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

Upvotes: 3

felix
felix

Reputation: 9285

you need to use aggregation for this, because $subtract can only be used within a $project stage Here is the aggregation query: 

db.collection.aggregate([
  {
    "$unwind": "$data.count"
  },
  {
    "$project": {
      "group": 1,
      "data": 1,
      "diff": {
        "$subtract": [
          "$data.count.f1",
          "$data.count.f2"
        ]
      }
    }
  },
  {
    "$match": {
      "diff": {
        "$gt": 0
      }
    }
  }
])

you can try it here: mongoplayground.net/p/Jp_G6Q0y26Q

Upvotes: 0

Related Questions