CODEWITHSUNDEEP
mongodb
petomalina
petomalina

Reputation: 2140

Get distinct records values

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.

Upvotes: 37

Views: 31489

Answers (6)

Skippy le Grand Gourou
Skippy le Grand Gourou

Reputation: 7694

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

Upvotes: 17

Ramesh Seera
Ramesh Seera

Reputation: 191

Mongo DB version > 3.4

db.test.distinct("name")

Mongo DB version < 3.4

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

Upvotes: 3

serv-inc
serv-inc

Reputation: 38147

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.

Upvotes: 18

GSK
GSK

Reputation: 563

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

Upvotes: 1

J.C. Gras
J.C. Gras

Reputation: 5442

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

Upvotes: 21

Chris Seymour
Chris Seymour

Reputation: 85765

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

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

Upvotes: 34

Related Questions