mongoDB distinct & where in same query?

Mongodb

Mongodb 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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionInoryyView Question on Stackoverflow
Solution 1 - MongodbRameshVelView Answer on Stackoverflow
Solution 2 - MongodbKaimView Answer on Stackoverflow
Solution 3 - MongodbAkshansh SaxenaView Answer on Stackoverflow
Solution 4 - MongodbAbilash RaghuView Answer on Stackoverflow