mongoDB distinct & where in same query?

Question

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"]

Have my upvote for the "I run", "I expect", "I get", if only all questions where formatted like this! — adelriosantiago, Jun 28 '15 at 05:45
Here's a variant of the solution http://stackoverflow.com/a/13864518/358013 — Chenna V, Dec 15 '15 at 21:36

RameshVel · Accepted Answer · 2011-09-15T09:45:54.070

107

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

edited Sep 15 '11 at 09:45

answered Sep 15 '11 at 07:03

RameshVel

64,778
30
169
213

I tried it and it seems to ignore condition, so results is the same as db.Article.distinct("Comment.Reply.ip") – Inoryy Sep 15 '11 at 08:23
@Inori, above code works perfectly fine in my environment... can you post your actual data and the result you got so i can dig in... – RameshVel Sep 15 '11 at 08:39
Guess I have to wait for 2.1 :/ Thanks for the reply anyways! – Inoryy Sep 15 '11 at 10:13

Akshansh Saxena · Answer 2 · 2021-07-09T09:12:50.343

4

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'}

edited Jul 09 '21 at 09:12

answered Jun 24 '21 at 03:47

Akshansh Saxena

69
3

This one is really good. I was trying to write aggregation query for this. – Shailesh Jul 08 '21 at 07:23

score 3 · Answer 3 · answered Aug 11 '16 at 01:40

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"
}

score -6 · Answer 4 · edited May 20 '19 at 16:42

-6

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.

edited May 20 '19 at 16:42

Pratibha

1,730
7
27
46

answered Sep 22 '18 at 09:23

Abilash Raghu

383
3
8

1

This does not work. It returns: 'distinct is not a function' – Anytoe Sep 24 '19 at 09:48

mongoDB distinct & where in same query?

4 Answers4

Linked

Related