mongoDB distinct & where in same query?
MongodbMongodb Problem Overview
Let's say I have the following documents
Article { Comment: embedMany }
Comment { Reply: embedMany }
Reply { email: string, ip: string }
I want to make a query that selects distinct Reply.ip
where Reply.email = xxx
Something like this, only it doesn't work..
db.Article.find("Comment.Reply.email" : "xxx").distinct("Comment.Reply.ip")
JSON export:
{
"_id":{
"$oid":"4e71be36c6eed629c61cea2c"
},
"name":"test",
"Comment":[
{
"name":"comment test",
"Reply":[
{
"ip":"192.168.2.1",
"email":"yyy"
},
{
"ip":"127.0.0.1",
"email":"zzz"
}
]
},
{
"name":"comment 2 test",
"Reply":[
{
"ip":"128.168.1.1",
"email":"xxx"
},
{
"ip":"192.168.1.1",
"email":"xxx"
}
]
}
]
}
I run : db.Article.distinct("Comment.Reply.ip",{"Comment.Reply.email" : "xxx"})
I expect : ["128.168.1.1", "192.168.1.1"]
I get : ["127.0.0.1", "128.168.1.1", "192.168.1.1", "192.168.2.1"]
Mongodb Solutions
Solution 1 - Mongodb
Distinct
query in mongo with condition works like this
db.Article.distinct("Comment.Reply.ip",{"Comment.Reply.email" : "xxx"})
not other way around
EDIT:
I understand the problem now, inorder to match/filter subdocuments we need to use $elemMatch operator, like this
db.Article.distinct("Comment.Reply.ip",{Comment: {$elemMatch: {"Reply.email" : "xxx"}}})
but this will not work if the sub-document contains sub arrays (in your case, you have array of replies). There is an existing issue $elemMatch on subArray is opened. And its planned for mongo 2.1. You can check out the link for more info
Solution 2 - Mongodb
Maybe you could try this
db.Article.aggregate([
{$unwind: "$Comment"},
{$unwind: "$Comment.Reply"},
{$match: {"Comment.Reply.email": "xxx"}},
{$group: {_id: "$Comment.Reply.ip"}}
])
The result of example should be
/* 1 */
{
"_id" : "192.168.1.1"
}
/* 2 */
{
"_id" : "128.168.1.1"
}
Solution 3 - Mongodb
db.collection.distinct("field_name", query)
will return all the distinct values from your collection in the form of an array
field_name should be the field for which you want to return distinct values.
query specifies the documents from where you want to retrieve the distinct values. ex :- {"Comment.Reply.email" : "xxx"}
or {'country' : 'India','state' : 'MH','city' : 'mumbai'}
Solution 4 - Mongodb
distinctdates=db.dailyreport_detailed.find(
{'uid':personemail,'month':searchdate2,'year':searchdate1}
)
.distinct('date_only')
where find will retrieve as per condition and distinct at the end will give unique dates.