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