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

A comparison operator compares a field with a value. The UI filter column gives the name of the same filter in the filter menu of a NocoDB view. Use it to turn a filter you built in the UI into a where condition.

blank, notblank, empty, notempty, null, notnull, checked and notchecked take no value. To find records that have no value at all, use blank. It matches both NULL and empty text, the same as is blank in the UI.

Some UI filter names change with the field type:

• eq and neq show as = and != on number and Time fields. They show as is and is not on Date, DateTime, Created time, Last modified time, Single select, User and link fields.
• like and nlike show as filenames contain and filenames don't contain on Attachment fields.

The NocoDB filter menu hides is empty, is not empty, is null and is not null until you enable Show NULL and EMPTY in Filter for the project. See Enabling NULL and EMPTY filters. With the setting on, the menu shows them only on text fields, such as Single line text, Long text, Email, URL and Phone number. The API accepts these operators without this setting.

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.

like and nlike on the same fields match the display name of the member, or their email address when they have no display name. They never match the stored user id. On these fields, a % in the middle of the value and _ match only themselves.

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.

Match text with like

The like operator finds records where a text field contains the value. It is the is like filter in the NocoDB filter menu. If the value does not start or end with %, NocoDB adds the % wildcard to both ends for you, so (Title,like,acme) matches "Acme Corp" and "The acme shop". A % in the middle of the value does not stop this: ac%me becomes %ac%me%.

Case sensitivity depends on the database. On PostgreSQL the match is not case-sensitive. On MySQL it follows the collation of the column. On SQLite it ignores case for ASCII letters only.

Put % at one end of the value to anchor the match to the start or the end of the text. NocoDB then uses the value as you wrote it:

An _ in the value matches any one character. (Code,like,A_1) matches A01 and AB1.

The % anchors work on Single line text, Email, URL and Phone number fields. On a Long text field, NocoDB always adds % to both ends of the value. So on a Long text field, acme% and %acme also match text that contains acme anywhere.

The nlike operator (is not like) returns the records that like does not match. It also returns the records where the field is empty. On a Long text field, nlike keeps the % anchors, but like does not. So there, (Notes,nlike,acme%) excludes only text that starts with acme, and it is not the exact opposite of (Notes,like,acme%).

Encode the percent sign in a URL

In a raw query string, write % as %25. An unencoded % starts a URL escape sequence, so the server reads a different value. Encode the whole where value:

(Title,like,Acme%)  →  where=%28Title%2Clike%2CAcme%25%29

Most HTTP clients encode query parameters for you. With curl, use -G and --data-urlencode, as in the Multi select example.

Comparison Sub-Operators

These sub-operators are available in Date and DateTime columns.

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

On a DateTime field, exactDate also takes a time, and on Postgres and SQLite it compares down to the millisecond: (dueAt,lt,exactDate,2030-01-01 10:30:45.500+00:00) excludes a record stored at 10:30:45.700. A value with no time still matches the whole day. API responses and CSV exports from those two databases carry the milliseconds too, for example 2030-01-01 10:30:45.700+00:00, so read a DateTime with a parser that accepts an optional fractional part. MySQL, SQL Server and Oracle keep second precision.

For isWithin in Date and DateTime columns, use these different sub-operators.

Logical Operators

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 or single quotes. 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", notblank)

Another Example: Combine multiple conditions with special characters in a v2 API call: @("Description", like, "High-performance, water-resistant")

Filtering by tags (Multi select)

A Multi select field, such as a Tags field, holds one or more options for each record. To filter on these options, use anyof, allof, nanyof and nallof. Put the options after the operator, separated by commas.

nanyof and nallof also return the records that have no options.

Each value must be the full name of an option. (Tags,anyof,urg) does not match the option urgent.

A Multi select option name cannot contain a comma. NocoDB refuses the option when you create it. So in these filters, a comma always separates two options. Put an option name in quotes when it contains a parenthesis: (Tags,anyof,"Q3 (draft)",bug).

The v2 API reads quotes only after the @ prefix, see Usage on v2 API. Without @, do not put a space after the commas. (Tags,anyof,urgent, bug) looks for an option named bug, with a leading space.

Example request

A GET request to /api/v3/data/{baseId}/{tableId}/records with where=(Tags,anyof,urgent,bug) returns the records whose Tags field has urgent or bug. URL-encode the where value:

curl -H "xc-token: <your-token>" \
  "https://app.nocodb.com/api/v3/data/<baseId>/<tableId>/records?where=%28Tags%2Canyof%2Curgent%2Cbug%29"

To let curl encode the value for you, use -G with --data-urlencode:

curl -G -H "xc-token: <your-token>" \
  "https://app.nocodb.com/api/v3/data/<baseId>/<tableId>/records" \
  --data-urlencode "where=(Tags,anyof,urgent,bug)"

Replace https://app.nocodb.com with the address of your NocoDB instance if you self-host. To find <baseId> and <tableId>, see IDs.

Related

• Query Parameters
• Pagination
• Filters in the NocoDB UI
• Multi select field