I am trying to use a Field Filter in my MongoDB query.
The data type of the field in the collection is “type/Text” for the original schema. My query:
{$match: {
"role.handle": {$in: [
"NON-CRITICAL",
"CRITICAL"
]
},
"company.name": {{Company}}
}
}
The Field Filter I chose was of type Category, when I apply the filter from list it returns no result. however when I use a Text Filter instead and manually type in the value I am searching for, results are returned.
Please assist me in finding out why my filters only work when I apply Text Field, but not the Field Filter
Thank you. I have tried this as well and am given an error: “JSON reader was expecting ‘:’ but found ‘}’.”
for query:
[
{
$lookup: {
from: "generaltypes",
localField: "healthStatus",
foreignField: "_id",
as: "health",
}
},
{
$lookup: {
from: "roles",
localField: "role",
foreignField: "_id",
as: "role",
}
},
{
$lookup: {
from: "schools",
localField: "school",
foreignField: "_id",
as: "company",
}
},
{
$lookup: {
from: "subjects",
localField: "subjects",
foreignField: "_id",
as: "department",
}
},
{$match: {
"role.handle": {$in: [
"NON-CRITICAL",
"CRITICAL"
]
},
{{Company}}
}
},
{$unwind: "$health"},
{
$group: {
_id: "$health._id",
status: { $min: "$health.value"
},
total: { $sum: 1
}
}
}
]);
@ChristineChetty And now try {$match: {{Company}}
nothing else. Check your MongoDB log to see if it gives more information about why it’s failing, or perhaps it shows the entire query, so you can spot the problem.
I am receiving no results when using field filter, but again only when the text is entered manually using Text Variable filter. Will look into MongoDB logs. I have tried doing this on a new DB with original schema not altered, produces the same results
My logs are not showing the query. How can I see what the query is that is being parsed to MongoDB? I can probably identify why it is returning no result based on this and then gain better understanding as how to write my queries in the metebase sql editor
@ChristineChetty You’ll have to search a MongoDB forum on how to enable query logging (aka debug logging).