Field Filter for Query does not return result, but Text Field works

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

Hi @ChristineChetty
The syntax of Field Filters is without a reference of the field/column:

[ {$match: {{Company}} } ]

https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type

1 Like

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).