Filtering
The where query parameter filters the records that a NocoDB API request returns. Each condition has the form (colName,operator,value). Join conditions with the logical operators.
(colName,eq,colValue)~or(colName2,gt,colValue2)Comparison Operators
| Operation | Meaning | Example |
|---|---|---|
| eq | equal | (colName,eq,colValue) |
| neq | not equal | (colName,neq,colValue) |
| not | not equal (alias of neq) | (colName,not,colValue) |
| gt | greater than | (colName,gt,colValue) |
| ge | greater or equal | (colName,ge,colValue) |
| lt | less than | (colName,lt,colValue) |
| le | less or equal | (colName,le,colValue) |
| is | is | (colName,is,true/false/null) |
| isnot | is not | (colName,isnot,true/false/null) |
| in | in | (colName,in,val1,val2,val3,val4) |
| btw | between | (colName,btw,val1,val2) |
| nbtw | not between | (colName,nbtw,val1,val2) |
| like | like | (colName,like,%name) |
| nlike | not like | (colName,nlike,%name) |
| isWithin | is Within (Available in Date and DateTime only) | (colName,isWithin,sub_op) |
| allof | includes all of | (colName,allof,val1,val2,...) |
| anyof | includes any of | (colName,anyof,val1,val2,...) |
| nallof | does not include all of (includes none or some, but not all of) | (colName,nallof,val1,val2,...) |
| nanyof | does not include any of (includes none of) | (colName,nanyof,val1,val2,...) |
btw and nbtw take two numbers on Number, Decimal, Currency, Percent, Rating, and Duration fields: (price,btw,10,100). They do not work on Date, DateTime, and Checkbox fields. On a Date or DateTime field, write the range with two bounds: (dueDate,gte,exactDate,2026-01-01)~and(dueDate,lte,exactDate,2026-03-31).
A condition on a User, Created by or Last modified by field takes an email address as well as a user id, with eq, neq, not, in, anyof, allof, nanyof and nallof: (Assignee,eq,alice@example.com). An address that belongs to no member of the project matches no user, so eq finds no record and neq finds every record.
The v3 filter endpoints check each condition when you save it. A condition that names a field the table does not have, or an operator the field type does not support, is refused with ERR_FILTER_VERIFICATION_FAILED. The stored filters do not change.
Comparison Sub-Operators
These sub-operators are available in Date and DateTime columns.
| Operation | Meaning | Example |
|---|---|---|
| today | today | (colName,eq,today) |
| tomorrow | tomorrow | (colName,eq,tomorrow) |
| yesterday | yesterday | (colName,eq,yesterday) |
| oneWeekAgo | one week ago | (colName,eq,oneWeekAgo) |
| oneWeekFromNow | one week from now | (colName,eq,oneWeekFromNow) |
| oneMonthAgo | one month ago | (colName,eq,oneMonthAgo) |
| oneMonthFromNow | one month from now | (colName,eq,oneMonthFromNow) |
| daysAgo | number of days ago | (colName,eq,daysAgo,10) |
| daysFromNow | number of days from now | (colName,eq,daysFromNow,10) |
| exactDate | exact date | (colName,eq,exactDate,2022-02-02) |
in matches one of several exact dates. Write the exactDate sub-operator, then a comma-separated list: (colName,in,exactDate,2022-02-02,2022-03-04).
For isWithin in Date and DateTime columns, use these different sub-operators.
| Operation | Meaning | Example |
|---|---|---|
| pastWeek | the past week | (colName,isWithin,pastWeek) |
| pastMonth | the past month | (colName,isWithin,pastMonth) |
| pastYear | the past year | (colName,isWithin,pastYear) |
| nextWeek | the next week | (colName,isWithin,nextWeek) |
| nextMonth | the next month | (colName,isWithin,nextMonth) |
| nextYear | the next year | (colName,isWithin,nextYear) |
| nextNumberOfDays | the next number of days | (colName,isWithin,nextNumberOfDays,10) |
| pastNumberOfDays | the past number of days | (colName,isWithin,pastNumberOfDays,10) |
Logical Operators
| Operation | Example |
|---|---|
| ~or | (checkNumber,eq,JM555205)~or((amount,gt,200)~and(amount,lt,2000)) |
| ~and | (checkNumber,eq,JM555205)~and((amount,gt,200)~and(amount,lt,2000)) |
| ~not | ~not(checkNumber,eq,JM555205) |
v3 Where Query Parameter
In the v3 data API, the where clause is slightly different from the earlier versions. You can put values in quotes: double quotes, single quotes, or backticks. Then you can safely use special characters that can break the where clauses of earlier versions. This helps most with strings that contain commas, parentheses, or other delimiters.
Example: Search for a phrase with special characters:
("My Field", like, "Let's come home, and go straight to bed")
Another Example: Search for a value that has a comma:
("Product Name", eq, "Laptop, 15-inch")
Example: Use single quotes for a value:
(City, eq, 'New York')
Usage on v2 API
You can also use the v3 where clause with the v2 API. Put an @ sign before the where clause. Then you can use the advanced functions of the v3 where clause in v2 API calls.
Example: Use a v3 where clause to find titles that are not blank, in a v2 API call:
@("Title", not, blank)
Another Example: Combine multiple conditions with special characters in a v2 API call:
@("Description", like, "High-performance, water-resistant")
Related
Last updated on