Different from multiple fields and with condition in mongodb

Question

I want to write a query equivalent to distinct and where in mongodb . sql query select DISTINCT key,score from GPC where note="test2" and notetwo = "meet2"

 { "_id" : ObjectId("4dc86fef6a0aa8513ab5f21c"), "key" : "SAGAR","score" : 16, "note" : "test1", "notetwo" : "meet1" } { "_id" : ObjectId("4dc86ffd6a0aa8513ab5f21d"), "key" : "SAGAR456", "score" : 17, "note" : "testjh1", "notetwo" : "meetjh1" } { "_id" : ObjectId("4dc8700b6a0aa8513ab5f21e"), "key" : "SAGAR33", "score" : 37, "note" : "test2", "notetwo" : "meet2" } { "_id" : ObjectId("4dc871686a0aa8513ab5f21f"), "key" : "SAGAR33", "score" : 37, "note" : "test2", "notetwo" : "meet2" } { "_id" : ObjectId("4dc871696a0aa8513ab5f220"), "key" : "SAGAR33", "score" : 37, "note" : "test2", "notetwo" : "meet2" } { "_id" : ObjectId("4dc8716c6a0aa8513ab5f221"), "key" : "SAGAR456", "score" : 17, "note" : "testjh1", "notetwo" : "meetjh1" } 

Expected Query Result

[{"key": "SAGAR33", "score": 37}]

What is the equivalent query in mongodb . I use mongoose to execute queries.

Answer 1

You need to use aggregate queries for this. Here is an example that will work in the shell (which can be easily translated to Mongoose):

 db.gpc.aggregate([ // your where clause: note="test2" and notetwo = "meet2" {"$match" : {note:"test2", notetwo:"meet2"}}, // group by key, score to get distinct {"$group" : {_id : {key:"$key", score:"$score"}}}, // Clean up the output {"$project" : {_id:0, key:"$_id.key", score:"$_id.score"}} ]) 

Output:

 { "result" : [ { "key" : "SAGAR33", "score" : 37 } ], "ok" : 1 }