CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
LGEYH
LGEYH

Reputation: 63

MongoDB compare two fields & sum another field on all docs

Sample Docs :

db.l5p1s3.insertMany([{x1:0,x2:0,v:0.08},{x1 : 0,x2:1,v:0.07},{x1:0,x2:2,v:0.04},{x1:0,x2:3,v:0.00}])
db.l5p1s3.insertMany([{x1:1,x2:0,v:0.06},{x1 : 1,x2:1,v:0.15},{x1:1,x2:2,v:0.05},{x1:1,x2:3,v:0.04}])
db.l5p1s3.insertMany([{x1:2,x2:0,v:0.05},{x1 : 2,x2:1,v:0.04},{x1:2,x2:2,v:0.10},{x1:2,x2:3,v:0.06}])
db.l5p1s3.insertMany([{x1:3,x2:0,v:0.00},{x1 : 3,x2:1,v:0.03},{x1:3,x2:2,v:0.04},{x1:3,x2:3,v:0.07}])
db.l5p1s3.insertMany([{x1:4,x2:0,v:0.00},{x1 : 4,x2:1,v:0.01},{x1:4,x2:2,v:0.05},{x1:4,x2:3,v:0.06}])

Query :

db.l5p1s3.aggregate({$match : { x1 :{$eq : "x2"} }} , {$group : {_id : {} , sum : {$sum : "$v"}}})

I have to get the sum of all values of v at x1 == x2. I tried the above query and did not get any output.

Upvotes: 1

Views: 617

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17925

Your match stage ({$match : { x1 :{$eq : "x2"} }}) says get all docs where x1 field has string value x2 - which is not what you're looking for. So to compare two fields & get required o/p, try below aggregation query :

db.collection.aggregate([
  /** Matches docs where x1 == x2 */
  { $match: { $expr: { $eq: ["$x1", "$x2"] } } },
  /** Iterate over all docs & sum -up 'v' field */
  { $group: { _id: "", sum: { $sum: "$v" } } }
]);

Test : MongoDB-Playground

Upvotes: 1

Related Questions