search-filter

# Search and Filter Reference NocoDB MCP tools take a filter in one of two forms. **Pass only one of the two** in a call: | Form | Parameter | Use it for | |------|-----------|------------| | **Structured** (preferred) | `filter` | Everything -- names and values are quoted for you, so commas, parentheses and quotes in values are safe | | **String** (fallback) | `where` | Short one-liners, and the REST `where=` query parameter | Both forms work on `queryRecords`, `countRecords`, `groupByRecords`, `updateRecordsByCondition` and on each `aggregate.filterGroups[]` entry (`{ "alias": "...", "filter": ... }` or `{ "alias": "...", "where": "..." }`). ## Structured `filter` A single condition: ``` filter: { "field": "Status", "operator": "eq", "value": "Active" } ``` A group -- `group_operator` is `AND` or `OR`, members may be conditions or nested groups: ``` filter: { "group_operator": "AND", "filters": [ { "field": "Status", "operator": "eq", "value": "Active" }, { "group_operator": "OR", "filters": [ { "field": "Priority", "operator": "eq", "value": "High" }, { "field": "Priority", "operator": "eq", "value": "Urgent" } ] } ] } ``` - `value` is a scalar for most operators, an **array** for `in`, `allof`, `anyof`, `nallof`, `nanyof`, and omitted for `blank`, `notblank`, `null`, `notnull`, `empty`, `notempty`, `checked`, `notchecked`. - Date and DateTime fields add a `sub_operator` (see "Date and Time Filtering" below). - Field titles are case-insensitive in `filter`. ## Basic `where` Syntax Every `where` condition follows this pattern: ``` (FieldName,operator,value) ``` - Wrap each condition in parentheses. - Separate field name, operator, and value with commas. - Field names are case-sensitive in `where` -- use the exact name from the table schema (a wrong case fails with `Column alias '<name>' not found`). Example: find all records where Status equals "Active": ``` (Status,eq,Active) ``` The rest of this page shows the `where` strings; every one has a `filter` equivalent. ## Text and General Operators | Operator | Meaning | Example | |----------|---------|---------| | `eq` | Equals | `(Status,eq,Active)` | | `neq` | Does not equal | `(Status,neq,Closed)` | | `like` | Contains text | `(Name,like,John)` | | `nlike` | Does not contain text | `(Name,nlike,Test)` | | `in` | Matches any value in a list | `(Status,in,Active,Pending)` | 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 | Operator | Meaning | Example | |----------|---------|---------| | `gt` | Greater than | `(Amount,gt,100)` | | `lt` | Less than | `(Amount,lt,50)` | | `gte` | Greater than or equal | `(Amount,gte,100)` | | `lte` | Less than or equal | `(Amount,lte,500)` | ### Ranges: always two bounds `btw` / `nbtw` are **rejected** on Number, Decimal, Currency, Percent, Rating, Duration, Date / DateTime and Checkbox fields (`Operation btw is not supported for type <T>`); only Time and text fields accept them. Two bounds work on every type, so write every range that way: | Need | Filter | |------|--------| | Between 10 and 100 (inclusive) | `(Price,gte,10)~and(Price,lte,100)` | | Not between 0 and 50 | `(Score,lt,0)~or(Score,gt,50)` | As a structured filter: ``` filter: { "group_operator": "AND", "filters": [ { "field": "Price", "operator": "gte", "value": 10 }, { "field": "Price", "operator": "lte", "value": 100 } ] } ``` ## Null and Empty Operators | Operator | Meaning | What It Catches | |----------|---------|-----------------| | `blank` | Field has no value at all | Null or empty string | | `notblank` | Field has some value | Any non-null, non-empty value | | `null` | Field is null | Only null (not empty string) | | `notnull` | Field is not null | Includes empty strings | | `empty` | Field is empty string | Only empty string (not null) | | `notempty` | Field is not empty string | Includes null values | 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 | Operator | Meaning | |----------|---------| | `checked` | Checkbox is checked (true) | | `notchecked` | Checkbox is not checked (false) | No value needed: ``` (IsActive,checked) (Archived,notchecked) ``` ## Multi-Select Operators For fields that allow multiple selections: | Operator | Meaning | Example | |----------|---------|---------| | `allof` | Has all of these values | `(Tags,allof,Urgent,High)` | | `anyof` | Has any of these values | `(Tags,anyof,Urgent,High)` | | `nallof` | Does not have all of these | `(Tags,nallof,Urgent,High)` | | `nanyof` | Does not have any of these | `(Tags,nanyof,Spam,Test)` | ## Date and Time Filtering Date and DateTime fields (and CreatedTime / LastModifiedTime) need a **sub-operator** on every comparison. The `where` form is `(field,operator,sub_operator)` or `(field,operator,sub_operator,value)`; the structured form adds a `sub_operator` key next to `operator`: ``` filter: { "field": "DueDate", "operator": "lt", "sub_operator": "today" } filter: { "field": "DueDate", "operator": "gte", "sub_operator": "exactDate", "value": "2026-06-01" } ``` ### 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. The date goes in the **value slot after `exactDate`** -- never directly after the operator: ``` (DueDate,eq,exactDate,2024-12-31) CORRECT (DueDate,lt,exactDate,2025-01-01) CORRECT (DueDate,gte,exactDate,2024-06-01) CORRECT (DueDate,eq,YYYY-MM-DD) WRONG -- a bare date is read as the sub-operator and rejected: '<date>' is not supported ``` A date **range** is two bounds -- `btw` / `nbtw` are not supported on date fields: ``` (DueDate,gte,exactDate,2024-01-01)~and(DueDate,lte,exactDate,2024-12-31) ``` Match any of several exact dates with `in`: ``` (DueDate,in,exactDate,2024-06-15,2024-07-01) ``` Date fields without a value (`blank`, `notblank`) take no sub-operator: `(DueDate,blank)`. ### Common Date Filter Patterns | Need | Filter | |------|--------| | Created today | `(CreatedAt,eq,today)` | | Due in the next 7 days | `(DueDate,isWithin,nextWeek)` | | Overdue items | `(DueDate,lt,today)` | | Created in last 30 days | `(CreatedAt,isWithin,pastNumberOfDays,30)` | | Due before the end of 2025 | `(DueDate,lte,exactDate,2025-12-31)` | | Due in 2025 | `(DueDate,gte,exactDate,2025-01-01)~and(DueDate,lte,exactDate,2025-12-31)` | | Updated in the past 7 days | `(UpdatedAt,gt,daysAgo,7)` | ## Logical Operators (Combining Filters) Combine multiple conditions with logical operators. **Always use the tilde prefix (`~`).** | Operator | Meaning | Syntax | |----------|---------|--------| | `~and` | Both conditions must match | `(A,eq,1)~and(B,eq,2)` | | `~or` | Either condition can match | `(A,eq,1)~or(B,eq,2)` | | `~not` | Negate a condition | `~not(A,eq,1)` | ### 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)) ``` `~not` only starts an expression or a group -- wrap it in parentheses when it follows `~and` / `~or`. Never put whitespace after `~and`, `~or` or `~not` (`(A,eq,1)~and (B,eq,2)` is a parse error), and write the operators in lowercase. ## 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, Quotes, Parentheses In a `where` string, wrap such a value in quotes: ``` (Address,eq,"12 Main St, Springfield") (Note,eq,"it's here") ```