In the first query, we need to count all non-deleted products by user and shop:
[
{
$match:{
"user_id": 1,
"shop_id": 1
}
},
{
$group:{
"_id":null,
"count":{
$sum:{
$cond:[{$eq:["$deleted_at",null]},1,0]
}
}
}
}
]
perfect, it’s working! Now I need to add a full-text search:
[
{
$match:{
"user_id": 1,
"shop_id": 1,
$text: {$search : "API"}
}
},
{
$group:{
"_id":null,
"count":{
$sum:{
$cond:[{$eq:["$deleted_at",null]},1,0]
}
}
}
}
]
should work, right??? No, it’s not working correctly and returning 0, even if I have matched documents. The solution is by changing $cond
to this $cond: [{$eq: [{$type : "$deleted_at"}, 'missing']},1,0]
, so the working query is :
[
{
$match: {
"user_id": 1,
"shop_id": 1,
$text: {$search : "API"}
}
},
{
$group: {
_id: null,
count: {
$sum: {
$cond: [{$eq: [{$type : "$deleted_at"}, 'missing']},1,0]
}
}
}
}
]
Okay so maybe we can change a $cond
in the initial query to be the same as in the full-test search query? Let’s try:
[
{
$match:{
"user_id": 1,
"shop_id": 1
}
},
{
$group:{
"_id":null,
"count":{
$sum:{
$cond: [{$eq: [{$type : "$deleted_at"}, 'missing']},1,0]
}
}
}
}
]
Hah, it’s not working and always returning 0. So we found that if our query contain full-text search we need to use $cond: [{$eq: [{$type : "$deleted_at"}, 'missing']},1,0]
, otherwise we need to use $cond:[{$eq:["$deleted_at",null]},1,0]
.
Now let’s imagine we need to find non-deleted products by user/shop and made by one of provided brands. So we know that if we do not need a full-text search we need to use $cond
like this $cond:[{$eq:["$deleted_at",null]},1,0]
, right??? Let’s see:
[
{
$match:{
"user_id": 1,
"shop_id": 1
}
},
{
$match:{
"brand":{
$exists:true,
$in:[
"brand1",
"brand2"
]
}
}
},
{
$group:{
"_id":null,
"count":{
$sum:{
$cond:[{$eq:["$deleted_at",null]},1,0]
}
}
}
}
]
hmmm, it’s returning 0, but should be more… Let’s try with $cond: [{$eq: [{$type : "$deleted_at"}, 'missing']},1,0]
:
[
{
$match:{
"user_id": 1,
"shop_id": 1
}
},
{
$match:{
"brand":{
$exists:true,
$in:[
"brand1",
"brand2"
]
}
}
},
{
$group:{
"_id":null,
"count":{
$sum:{
$cond: [{$eq: [{$type : "$deleted_at"}, 'missing']},1,0]
}
}
}
}
]
and hooray, it’s working. So the question can someone explain to me how does it work? In the application I have dynamic filters, users can apply only one filter or multiple filters at the same time, and additionally, there can be a full-text search, how can I write a query if mongo behaves unexpectedly…