Search Filter

Search and Filter Reference

Basic Filter Syntax

Every filter follows this pattern:

(FieldName,operator,value)

• Wrap each condition in parentheses.
• Separate field name, operator, and value with commas.
• Field names are case-sensitive -- use the exact name from the table schema.

Example: find all records where Status equals "Active":

(Status,eq,Active)

Text and General Operators

Notes:

• like matches anywhere in the text. (Name,like,oh) matches "John" and "Mohit."
• in takes a comma-separated list of values after the operator.

Numeric Operators

Important: The btw operator may not work correctly with Currency fields. For Currency fields, combine gte and lte instead:

(Price,gte,10)~and(Price,lte,100)

Null and Empty Operators

These operators take no value -- just the field and operator:

(Email,notblank)
(Notes,blank)
(Phone,notnull)

Use blank / notblank when you want "has any value" or "has no value" without worrying about null vs. empty distinctions.

Boolean (Checkbox) Operators

No value needed:

(IsActive,checked)
(Archived,notchecked)

Multi-Select Operators

For fields that allow multiple selections:

Date and Time Filtering

Date filters use a different structure with sub-operators.

Relative Date Ranges (isWithin)

Find records within a relative time window:

(CreatedAt,isWithin,pastWeek)
(CreatedAt,isWithin,pastMonth)
(CreatedAt,isWithin,pastYear)
(CreatedAt,isWithin,pastNumberOfDays,30)
(CreatedAt,isWithin,nextWeek)
(CreatedAt,isWithin,nextMonth)
(CreatedAt,isWithin,nextYear)
(CreatedAt,isWithin,nextNumberOfDays,14)

Relative Date Comparisons

Compare a date field to a relative reference point:

(DueDate,eq,today)
(DueDate,eq,yesterday)
(DueDate,eq,tomorrow)
(DueDate,gt,daysAgo,7)
(DueDate,lt,daysFromNow,30)
(DueDate,gte,daysAgo,14)

Exact Date Comparisons

Compare to a specific calendar date:

(DueDate,eq,exactDate,2024-12-31)
(DueDate,lt,exactDate,2025-01-01)
(DueDate,gte,exactDate,2024-06-01)
(DueDate,btw,exactDate,2024-01-01,exactDate,2024-12-31)

Common Date Filter Patterns

Logical Operators (Combining Filters)

Combine multiple conditions with logical operators. Always use the tilde prefix (~).

Combining Multiple Conditions

Two conditions with AND:

(Status,eq,Active)~and(Priority,eq,High)

Three conditions:

(Status,eq,Active)~and(Priority,eq,High)~and(DueDate,lt,today)

Mixing AND and OR:

(Status,eq,Active)~and((Priority,eq,High)~or(Priority,eq,Urgent))

Using NOT:

(Status,eq,Active)~and~not(Category,eq,Internal)

Special Values and Edge Cases

Filtering for Null or Empty

(Notes,blank)           -- no value at all
(Notes,notblank)        -- has some value

Field Names with Spaces

Use the field name as-is. Spaces are allowed inside the parentheses:

(First Name,eq,John)
(Order Total,gt,100)

Values with Commas

If the value itself contains a comma, the filter may misinterpret it. Avoid values with commas in eq filters. Use like for partial matching instead:

(Address,like,New York)

Numeric Strings

Numeric operators only work on numeric fields. For text fields that contain numbers, use eq or like:

(ZipCode,eq,10001)      -- text field, use eq
(Amount,gt,100)          -- numeric field, use gt

Sorting

The sort parameter in queryRecords accepts an array of sort rules. Each rule has a field and direction.

sort: [{ "field": "CreatedAt", "direction": "desc" }]

Multiple sort levels (sort by Status first, then by Date within each status):

sort: [
  { "field": "Status", "direction": "asc" },
  { "field": "DueDate", "direction": "asc" }
]

Sort directions:

• asc -- smallest/earliest/A first
• desc -- largest/latest/Z first

Practical Examples

1. Active high-priority tasks due this week

where: "(Status,eq,Active)~and(Priority,eq,High)~and(DueDate,isWithin,pastWeek)"
sort: [{ "field": "DueDate", "direction": "asc" }]

2. Orders over $500 from the past 30 days

where: "(Total,gt,500)~and(OrderDate,isWithin,pastNumberOfDays,30)"
sort: [{ "field": "Total", "direction": "desc" }]

3. Contacts without an email address

where: "(Email,blank)"
fields: ["Name", "Phone", "Company"]

4. Products in Electronics or Clothing categories

where: "(Category,in,Electronics,Clothing)"
sort: [{ "field": "Name", "direction": "asc" }]

5. Unresolved support tickets created more than 7 days ago

where: "(Resolution,blank)~and(CreatedAt,lt,daysAgo,7)"
sort: [{ "field": "CreatedAt", "direction": "asc" }]

6. Records updated today

where: "(UpdatedAt,eq,today)"
sort: [{ "field": "UpdatedAt", "direction": "desc" }]

7. Inventory items with low stock

where: "(Quantity,lte,10)~and(Quantity,gt,0)"
sort: [{ "field": "Quantity", "direction": "asc" }]

8. Invoices between $1,000 and $5,000

where: "(Amount,gte,1000)~and(Amount,lte,5000)"
sort: [{ "field": "Amount", "direction": "desc" }]

9. Tasks assigned to a specific person that are not completed

where: "(AssignedTo,eq,Jane Smith)~and(Status,neq,Completed)"
sort: [{ "field": "Priority", "direction": "desc" }, { "field": "DueDate", "direction": "asc" }]

10. Records with any tag containing "Urgent"

where: "(Tags,anyof,Urgent)"

11. Exclude archived and test records

where: "(Archived,notchecked)~and(Name,nlike,Test)"

12. Date range -- all of Q1 2025

where: "(Date,gte,exactDate,2025-01-01)~and(Date,lte,exactDate,2025-03-31)"

Quick Reference

When building a filter:

1. Get the exact field names from mcp__nocodb__getTableSchema.
2. Pick the right operator for the field type (text, number, date, checkbox, multi-select).
3. Combine conditions with ~and or ~or (always with tilde).
4. Test with a small pageSize first to verify results before running large queries.
5. Add sort to control the order of results.
6. Use fields to return only the columns you need.