A filter is a single condition used to specify and limit the entries returned from a database query. Database queries can be filtered by page property values. The API supports filtering by the following property types: rich_text, phone_number, number, checkbox, select, multi-select, date, people, files, relation, status, and formula. You may also filter a database by created_time or last_edited_time, even if these aren't present as properties on the database.
Property filter object
Each database property filter object must contain a property key and a key corresponding with the type of the database property identified by property. The value is an object containing a type-specific filter condition.
| Property | Type | Description |
|---|---|---|
property | string | The name or ID of the property to filter on. |
Here is an example of a filter on a database property of type text.
{
"filter": {
"property": "Landmark",
"rich_text": {
"contains": "Bridge"
}
}
}
Timestamp filter object
A timestamp filter object must contain a timestamp key corresponding to the type of timestamp and a key matching that timestamp type which contains a date filter condition.
| Property | Type | Description |
|---|---|---|
timestamp | string (enum) | The type of timestamp to filter on. Possible values are: "created_time", "last_edited_time". |
Here's an example of a filter on the created timestamp:
{
"filter": {
"timestamp": "created_time",
"created_time": {
"past_week": {}
}
}
}
Compound filter object
A compound filter object combines several filter objects together using a logical operator and or or. A compound filter can even be combined within a compound filter, but only up to two nesting levels deep.
The compound filter object contains one of the following keys:
| Property | Type | Description |
|---|---|---|
or | array of filter objects or compound filter objects. | Returns pages when any of the filters inside the provided array match. |
and | array of filter objects or compound filter objects. | Returns pages when all of the filters inside the provided array match. |
Here is an example of a compound filter that combines two filter objects. The result of this filter will be one that satisfies the checkbox filter and the number filter.
{
"filter": {
"and": [
{
"property": "Seen",
"checkbox": {
"equals": false
}
},
{
"property": "Yearly visitor count",
"number": {
"greater_than": 1000000
}
}
]
}
}
Here is example of a compound filter that combines a single filter object with another compound filter object:
{
"filter": {
"or": [
{
"property": "Description",
"rich_text": {
"contains": "fish"
}
},
{
"and": [
{
"property": "Food group",
"select": {
"equals": "🥦Vegetable"
}
},
{
"property": "Is protein rich?",
"checkbox": {
"equals": true
}
}
]
}
]
}
}
Type-specific filter conditions
Text filter condition
A text filter condition can be applied to database properties of types "title", "rich_text", "url", "email", and "phone_number".
| Property | Type | Description | Example value |
|---|---|---|---|
equals | string | Only return pages where the page property value matches the provided value exactly. | "Golden Gate Bridge" |
does_not_equal | string | Only return pages where the page property value does not match the provided value exactly. | "Golden Gate Bridge" |
contains | string | Only return pages where the page property value contains the provided value. | "Bridge" |
does_not_contain | string | Only return pages where the page property value does not contain the provided value. | "Park" |
starts_with | string | Only return pages where the page property value starts with the provided value. | "Golden Gate" |
ends_with | string | Only return pages where the page property value ends with the provided value. | "Bridge" |
is_empty | boolean (only true) | Only return pages where the page property value is empty. | true |
is_not_empty | boolean (only true) | Only return pages where the page property value is present. | true |
Number filter condition
A number filter condition can be applied to database properties of type "number".
| Property | Type | Description | Example value |
|---|---|---|---|
equals | number | Only return pages where the page property value matches the provided value exactly. | 42 |
does_not_equal | number | Only return pages where the page property value does not match the provided value exactly. | 24 |
greater_than | number | Only return pages where the page property value is greater than the provided value. | 40 |
less_than | number | Only return pages where the page property value is less than the provided value. | 50 |
greater_than_or_equal_to | number | Only return pages where the page property value is greater than or equal to the provided value. | 42 |
less_than_or_equal_to | number | Only return pages where the page property value is less than or equal to the provided value. | 42 |
is_empty | boolean (only true) | Only return pages where the page property value is empty. | true |
is_not_empty | boolean (only true) | Only return pages where the page property value is present. | true |
Checkbox filter condition
A checkbox filter condition can be applied to database properties of type "checkbox".
| Property | Type | Description | Example value |
|---|---|---|---|
equals | boolean | Only return pages where the page property value matches the provided value exactly. | true |
does_not_equal | boolean | Only return pages where the page property value does not match the provided value exactly. | true |
Select filter condition
A select filter condition can be applied to database properties of type "select".
| Property | Type | Description | Example value |
|---|---|---|---|
equals | string | Only return pages where the page property value matches the provided value exactly. | "This Week" |
does_not_equal | string | Only return pages where the page property value does not match the provided value exactly. | "Backlog" |
is_empty | boolean (only true) | Only return pages where the page property value is empty. | true |
is_not_empty | boolean (only true) | Only return pages where the page property value is present. | true |
Multi-select filter condition
A multi-select filter condition can be applied to database properties of type "multi_select".
| Property | Type | Description | Example value |
|---|---|---|---|
contains | string | Only return pages where the page property value contains the provided value. | "Marketing" |
does_not_contain | string | Only return pages where the page property value does not contain the provided value. | "Engineering" |
is_empty | boolean (only true) | Only return pages where the page property value is empty. | true |
is_not_empty | boolean (only true) | Only return pages where the page property value is present. | true |
Status filter condition
A status filter condition can be applied to database properties of type "status".
| Property | Type | Description | Example value |
|---|---|---|---|
equals | string | Only return pages where the page property value matches the provided value exactly. | "Completed" |
does_not_equal | string | Only return pages where the page property value does not match the provided value exactly. | "In progress" |
is_empty | boolean (only true) | Only return pages where the page property value is empty. | true |
is_not_empty | boolean (only true) | Only return pages where the page property value is present. | true |
Date filter condition
A date filter condition can be applied to database properties of types "date", "created_time", and "last_edited_time".
| Property | Type | Description | Example value |
|---|---|---|---|
equals | string (ISO 8601 date) | Only return pages where the page property value matches the provided date exactly. If a date is provided, the comparison is done against the start and end of the UTC date. If a date with a time is provided, the comparison is done with millisecond precision. Note that if no timezone is provided, the default is UTC. | "2021-05-10" or "2021-05-10T12:00:00" or "2021-10-15T12:00:00-07:00" |
before | string (ISO 8601 date) | Only return pages where the page property value is before the provided date. If a date with a time is provided, the comparison is done with millisecond precision. Note that if no timezone is provided, the default is UTC. | "2021-05-10" or "2021-05-10T12:00:00" or "2021-10-15T12:00:00-07:00" |
after | string (ISO 8601 date) | Only return pages where the page property value is after the provided date. If a date with a time is provided, the comparison is done with millisecond precision. Note that if no timezone is provided, the default is UTC. | "2021-05-10" or "2021-05-10T12:00:00" or "2021-10-15T12:00:00-07:00" |
on_or_before | string (ISO 8601 date) | Only return pages where the page property value is on or before the provided date. If a date with a time is provided, the comparison is done with millisecond precision. Note that if no timezone is provided, the default is UTC. | "2021-05-10" or "2021-05-10T12:00:00" or "2021-10-15T12:00:00-07:00" |
is_empty | boolean (only true) | Only return pages where the page property value is empty. | true |
is_not_empty | boolean (only true) | Only return pages where the page property value is present. | true |
on_or_after | string (ISO 8601 date) | Only return pages where the page property value is on or after the provided date. If a date with a time is provided, the comparison is done with millisecond precision. Note that if no timezone is provided, the default is UTC. | "2021-05-10" or "2021-05-10T12:00:00" or "2021-10-15T12:00:00-07:00" |
past_week | object (empty) | Only return pages where the page property value is within the past week. | {} |
past_month | object (empty) | Only return pages where the page property value is within the past month. | {} |
past_year | object (empty) | Only return pages where the page property value is within the past year. | {} |
this_week | object (empty) | Only return pages where the page property value is within the current week. The current week starts on the most recent Sunday and ends on the upcoming Saturday. | {} |
next_week | object (empty) | Only return pages where the page property value is within the next week. | {} |
next_month | object (empty) | Only return pages where the page property value is within the next month. | {} |
next_year | object (empty) | Only return pages where the page property value is within the next year. | {} |
People filter condition
A people filter condition can be applied to database properties of types "people", "created_by", and "last_edited_by".
| Property | Type | Description | Example value |
|---|---|---|---|
contains | string (UUIDv4) | Only return pages where the page property value contains the provided value. | "6c574cee-ca68-41c8-86e0-1b9e992689fb" |
does_not_contain | string (UUIDv4) | Only return pages where the page property value does not contain the provided value. | "6c574cee-ca68-41c8-86e0-1b9e992689fb" |
is_empty | boolean (only true) | Only return pages where the page property value is empty. | true |
is_not_empty | boolean (only true) | Only return pages where the page property value is present. | true |
Files filter condition
A files filter condition can be applied to database properties of type "files".
| Property | Type | Description | Example value |
|---|---|---|---|
is_empty | boolean (only true) | Only return pages where the page property value is empty. | true |
is_not_empty | boolean (only true) | Only return pages where the page property value is present. | true |
Relation filter condition
A relation filter condition can be applied to database properties of type "relation".
| Property | Type | Description | Example value |
|---|---|---|---|
contains | string (UUIDv4) | Only return pages where the page property value contains the provided value. | "6c574cee-ca68-41c8-86e0-1b9e992689fb" |
does_not_contain | string (UUIDv4) | Only return pages where the page property value does not contain the provided value. | "6c574cee-ca68-41c8-86e0-1b9e992689fb" |
is_empty | boolean (only true) | Only return pages where the page property value is empty. | true |
is_not_empty | boolean (only true) | Only return pages where the page property value is present. | true |
Rollup filter condition
A rollup filter condition can be applied to database properties of type "rollup". Rollups which evaluate to arrays accept a filter with an any, every, or none condition; rollups which evaluate to numbers accept a filter with a number condition; and rollups which evaluate to dates accept a filter with a date condition.
Rollups which evaluate to arrays accept any kind of property in
| Property | Type | Description | Example value |
|---|---|---|---|
any | object | For a rollup property which evaluates to an array, return the pages where any item in that rollup fits this criterion. The criterion itself can be any other property type. | { "any": { "rich_text": { "contains": "kale" } } |
every | object | For a rollup property which evaluates to an array, return the pages where every item in that rollup fits this criterion. The criterion itself can be any other property type. | { "every": { "rich_text": { "contains": "kale" } } |
none | object | For a rollup property which evaluates to an array, return the pages where no item in that rollup fits this criterion. The criterion itself can be any other property type. | { "none": { "rich_text": { "contains": "kale" } } |
number | object (number filter condition) | For a rollup property which evaluates to an number, return the pages where the number fits this criterion. | { "number": { "equals": 42 } } |
date | object (date filter condition) | For a rollup property which evaluates to an date, return the pages where the date fits this criterion. | { "date": { "equals": 42 } } |
Formula filter condition
A formula filter condition can be applied to database properties of type "formula".
| Property | Type | Description |
|---|---|---|
string | object (text filter condition) | Only return pages where the result type of the page property formula is "string" and the provided string filter condition matches the formula's value. |
checkbox | object (checkbox filter condition) | Only return pages where the result type of the page property formula is "checkbox" and the provided checkbox filter condition matches the formula's value. |
number | object (number filter condition) | Only return pages where the result type of the page property formula is "number" and the provided number filter condition matches the formula's value. |
date | object (date filter condition) | Only return pages where the result type of the page property formula is "date" and the provided date filter condition matches the formula's value. |