Mongodb: advanced conditional query
I have the following list of documents: (collection contains over 100 documents)
{name : 'Tom', gender : 'male'},
{name : 'Sandra', gender : 'female'},
{name : 'Alex', gender : 'male'}
I only want to return 4 records, of which 2 of them are men and 2 of them are women.
So far I have tried this:
db.persons.find({'gender' : { $in : ['male','female']},{$limit : 4});
which brings 4 entries as expected, but is not guaranteed to have 2 males and 2 females. Is there a way to filter documents to return a specified list and also not require two separate db calls to be made?
Thanks in advance.
source to share
I am trying to find the correct solution to your problem, but it seems to be a tricky one.
The only way I thought of being able to call the database once was to group the information by gender and then project the resulting array of names, slicing it and limiting the size of the array to 2.
This is not possible in the aggregation pipeline as you cannot use operators such as $slice
.
However, I was able to group the database records by gender and return values in an array, which can then be manipulated.
After many tries, I came up with a solution below:
var people = db.people.aggregate([
{
$group: {
_id: '$gender',
names: { $push: '$name'}
}
}
]).toArray();
var new_people = [];
for (var i = 0; i < people.length; i++) {
for (var j = 0; j < 2; j++) {
new_people.push({
gender: people[i]._id,
name: people[i].names[j]
});
}
}
If you want to filter data, you have two options, based on my example:
-
Filter the data in the aggregation pipeline at the stage
$match
-
Filter data when looping through an array represented by aggregation
source to share
Making two calls is easy, and I see no reason not to.
Collect the results of two find
s:
var males = db.person.find({"gender": "male"}, {"name":1}).limit(2);
var females = db.person.find({"gender": "female"}, {"name":1}).limit(2);
var all = [];
var collectToAll = function(person) { all.push(person); };
males.forEach(collectToAll)
females.forEach(collectToAll)
Then all
there is
[
{
"_id" : ObjectId("549289765732b52ca191fdae"),
"name" : "Tom"
},
{
"_id" : ObjectId("549289865732b52ca191fdb0"),
"name" : "Alex"
},
{
"_id" : ObjectId("549289805732b52ca191fdaf"),
"name" : "Sandra"
}
]