was wondering if you could figure out what's wrong with this pipeline I have here:
[{
"$project": {
"sex": 1,
"index": 1,
"bYear": 1,
"categories": 1
}
}, {
"$match": {
"$and": [{"index": {"$gte": 0}},
{"$and": [{"bYear": {"$lte": 1994}},
{"bYear": {"$gte": 1898}}]}
]}
}, {
"$unwind": "$categories"
}, {
"$match": {
"$or": [{"category.id": 6168},{"category.id": 6169}]
}
}, {
"$group": {"_id": "$sex", "sexCount": { "$sum": 1 } }
}, {
"$sort": {
"sexCount": -1
}
}]
Object is something like this:
sex
index
bYear
categories[{}, {}, ...]
If I omit the UNWIND and second MATCH (right after unwind), it works fine. However if I do the filter by categoryid, I get no results but I know it should find records.
Any idea what is wrong?
Thank you
This pipeline is unlikely to be correct.
Biggest problem is you are projecting and unwinding a field called "categories" and then you try to filter on a key named "category" - unwinding an array named "categories" will create documents with a field named "categories" still.
You also have a lot of unneeded $and and $or operators in your $match. I suspect you don't need to unwind categories because all you are doing with the result is $matching - so that can be merged into the other $match you have.
Here's my guess as to what the correct pipeline would be for you:
[
{
"$project" : {
"sex" : 1,
"index" : 1,
"bYear" : 1,
"categories" : 1
}
},
{
"$match" : {
"index" : {
"$gte" : 0
},
"bYear" : {
"$lte" : 1994,
"$gte" : 1898
},
"category.id" : {
"$in" : [
6168,
6169
]
}
}
},
{
"$group" : {
"_id" : "$sex",
"sexCount" : {
"$sum" : 1
}
}
},
{
"$sort" : {
"sexCount" : -1
}
}
]
Sorry guys, the problem was somewhere else in the code, I think this pipeline is correct.