CODEWITHSUNDEEP
mongodbmongodb-query
ticktock
ticktock

Reputation: 1703

MongoDb find to return an array of values of a field for a given condition

Suppose I have a collection that might be something along the lines of

{
  name: "ticktock",
  age: 102,
  residence : {
    street : "123 Main Street",
    city: "Gotham"
  }
}

and I construct a query that returns me the 'city' field. Something like

db.people.find({age: {$gt: 50}, {residence.city: 1, _id:0}).toArray();

this would return me an array whose elements look like

{"residence" : {"city": "Gotham"}}

Now if I wanted an array of just the values. Ex.:- ["Gotham", "Metropolis", "Minas Tirith"], How might I go about that conversion?

Upvotes: 1

Views: 72

Answers (2)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17925

Update :

For an array with duplicates, you can use aggregation-pipeline :

db.collection.aggregate([
  { $match: { age: { $gt: 50 } } },
  { $group: { _id: "", city: { $push: "$residence.city" } } }, // Use $addToSet for unique cities
  { $project: { city: 1, _id: 0 } }
]);

Test : MongoDB-Playground

Old :

You can take advantage of .distinct() , which is a way to get unique values on a given field as an array. Let's take this example :

Collection Data :

/* 1 */
{
    "_id" : ObjectId("5e6c2331d6be1c3d27afaecb"),
    "name" : "ticktock",
    "age" : 102.0,
    "residence" : {
        "street" : "123 Main Street",
        "city" : "Gotham"
    }
}

/* 2 */
{
    "_id" : ObjectId("5e6c2331d6be1c3d27afae22"),
    "name" : "ticktock2",
    "age" : 99.0,
    "residence" : {
        "street" : "123 Main Street",
        "city" : "Gotham"
    }
}

/* 3 */
{
    "_id" : ObjectId("5e6c2331d6be1c3d27afaecc"),
    "name" : "ticktock3",
    "age" : 102.0,
    "residence" : {
        "street" : "123 Main Street",
        "city" : "Metropolis"
    }
}

/* 4 */
{
    "_id" : ObjectId("5e6c2331d6be1c3d27afaecd"),
    "name" : "ticktock4",
    "age" : 102.0,
    "residence" : {
        "street" : "123 Main Street",
        "city" : "Minas Tirith"
    }
}

/* 5 */
{
    "_id" : ObjectId("5e6c2331d6be1c3d27afaece"),
    "name" : "ticktock5",
    "age" : 10.0,
    "residence" : {
        "street" : "123 Main Street",
        "city" : "SomeCityLess-AGE"
    }
}

/* 6 */
{
    "_id" : ObjectId("5e6c2331d6be1c3d27afaecf"),
    "name" : "ticktock6",
    "age" : 50.0,
    "residence" : {
        "street" : "123 Main Street",
        "city" : "someCity-AGE=50"
    }
}

/* 7 */
{
    "_id" : ObjectId("5e6c2331d6be1c3d27afaed0"),
    "name" : "ticktock7",
    "age" : 102.0,
    "residence" : {
        "street" : "123 Main Street",
        "city" : "Metropolis"
    }
}

/* 8 */
{
    "_id" : ObjectId("5e6c2498c16b5679b4043d66"),
    "name" : "ticktock8",
    "residence" : {
        "street" : "123 Main Street",
        "city" : "city-No-AGE"
    }
}

Query :

db.collection.distinct('residence.city')

Result : Would get you an array of unique values for given field 'residence.city'.

/* 1 */
[
    "Gotham",
    "Metropolis",
    "Minas Tirith",
    "SomeCityLess-AGE",
    "city-No-AGE",
    "someCity-AGE=50"
]

So let's have your condition, age: {$gt: 50} :

Query :

db.collection.distinct('residence.city',{age: {$gt:50}})

Result : So now it has got the unique city names that match the given criteria.

/* 1 */
[
    "Gotham",
    "Metropolis",
    "Minas Tirith"
]

Note : Just as a remainder, as like any other database query you need to have a proper indexes for these two fields age & residence.city, If your queries are more targeted towards age > 50 you can have a partial index on age field rather than having it as normal index.

Upvotes: 1

Joe
Joe

Reputation: 28366

That looks a lot like javascript, so perhaps Array.map:

db.people.find({age: {$gt: 50}, {residence.city: 1, _id:0}).toArray().map(d=>d.residence.city)

Upvotes: 2

Related Questions