Get distinct records values

Question

Is there a way to query for objects with not same values on some field? For example I have Records:

{ id : 1, name : "my_name", salary : 1200 }
{ id : 2, name : "my_name", salary : 800 }
{ id : 3, name : "john", salary : 500 }

Query : find all with NOT_THE_SAME(name)

I just want records with id 1 and 3 because I specified that I don't want records with same value in field name or 2 and 3, it does not matter in this situation.

score 30 · Accepted Answer · answered Dec 18 '13 at 10:30

30

You can use db.collection.distinct to get back an array of unique values:

> db.test.distinct("name")
[ "my_name", "john" ]

answered Dec 18 '13 at 10:30

Chris Seymour

83,387
30
160
202

score 19 · Answer 2 · answered Sep 07 '16 at 21:38

19

You can also use a distinct sentence with filtered collection. For example, you can get distinct values of names from salaries over 800 with the following query:

db.test.distinct("name", { "salary": { $gt: 800 } })

answered Sep 07 '16 at 21:38

J.C. Gras

4,934
1
37
44

score 16 · Answer 3 · answered Jul 03 '17 at 14:00

db.test.aggregate([{$group: {_id: "$name", salary: {$max: "$salary"}}}])

should list all names with their salaries.

$max returns the highest salary per element. You could also choose $first etc, see https://docs.mongodb.com/manual/reference/operator/aggregation/group/#accumulator-operator.

Skippy le Grand Gourou · Answer 4 · 2020-01-15T17:41:51.190

If you don't care about the content of varying fields but want the whole records, this will keep the (whole, thanks to $$ROOT) first document found for each "name" :

db.coll.aggregate([
  { "$group": { 
    "_id": "$name", 
    "doc": { "$first": "$$ROOT" }
  }},
  { "$replaceRoot": {
    "newRoot": "$doc"
  }}
])

$replaceRoot will serve them as documents instead of encapsulating them in a doc field with _id.

If you are concerned about the content of the varying fields, I guess you may want to sort the documents first :

db.coll.aggregate([
  { "$sort": { "$salary": 1 }},
  { "$group": { 
    "_id": "$name", 
    "doc": { "$first": "$$ROOT" } 
  }},
  { "$replaceRoot": {
    "newRoot": "$doc"
  }}
])

Worked like a charm. This was something I wouldn't find very easily. Thanks! — Khushboo Mulani, Nov 04 '20 at 06:14

score 2 · Answer 5 · edited Nov 18 '18 at 00:32

2

Mongo DB version > 3.4

db.test.distinct("name")

Mongo DB version < 3.4

db.test.aggregate([{$group: {_id: "$name", salary: {$max: "$salary"}}}])

edited Nov 18 '18 at 00:32

Stephen Rauch

47,830
31
106
135

answered Nov 18 '18 at 00:09

Ramesh Seera

171
1
4

score 1 · Answer 6 · edited Jul 03 '17 at 13:50

1

db.runCommand ( {
    distinct: "CollectionName", 
    key: "key", 
    query: { "createdDate": { 
        $gte:new ISODate("2017-04-04T23:59:59Z"),
        $lte:new ISODate("2017-04-25T23:59:59Z")}} } )

this query helps find data in collection , will fetch value of key from all documents which satisfy condition of between date

edited Jul 03 '17 at 13:50

serv-inc

35,772
9
166
188

answered Apr 26 '17 at 14:58

GSK

553
5
11

While this code snippet may solve the question, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, this reduces the readability of both the code and the explanations! – Martin Tournoij Apr 26 '17 at 23:37
@Carpetsmoker - added comments ,hope this helps – GSK Apr 28 '17 at 09:07

Get distinct records values

6 Answers6

Mongo DB version > 3.4

Mongo DB version < 3.4

Linked

Related