Skip to content

Filter

Info

For simplicity, all the examples on this page, assume the HTTP client is already initialized and the requests are unauthenticated.

For an example of how to initialize the HTTP clients and make authenticated requests see authentication section

Basic usage

You can filter the returned rows by specifying conditions on columns. Each condition is a separate query string parameter.

curl http://localhost:8080/rest/todos?id=gt.10
const response = await fetch(endpoint + '/rest/todos?id=gt.10');
const response = await client.get('/rest/todos?id=gt.10');
const response = await client.query({ 
    query: gql`
        query {
            todos(where: {id: {gt: 10}}) {
                id
            }
        }
    `
});

Logical AND

Multiple conditions can be logically conjoined (WHERE cond1 AND cond2 ...)

curl "http://localhost:8080/rest/todos?id=gt.10&completed=eq.false"
const response = await fetch(endpoint + '/rest/todos?id=gt.10&completed=eq.false');
const response = await client.get('/rest/todos?id=gt.10&completed=eq.false');
const response = await client.query({ 
    query: gql`
        query {
            todos(where: {id: {gt: 10}, completed: {eq: false}}) {
                id
            }
        }
    `
});

Logical OR

Multiple conditions can also be disjoined (WHERE cond1 OR cond2 ...) by using the reserved query parameter or=

curl "http://localhost:8080/rest/people?or=(first_name.eq.John,last_name.eq.Smith)"
const response = await fetch(endpoint + '/rest/todos?or=(first_name.eq.John,last_name.eq.Smith)');
const response = await client.get('/rest/todos?or=(first_name.eq.John,last_name.eq.Smith)');

Complex logic

Complex filtering logic can also be used (WHERE cond1 AND (cond2 OR cond3) ...) by combining the reserved query parameters or= and and=

curl "http://localhost:8080/rest/people?and=(id.gte.1000,or(first_name.eq.John,last_name.eq.Smith))"
const response = await fetch(endpoint + '/rest/todos?and=(id.gte.1000,or(first_name.eq.John,last_name.eq.Smith))');

``` js const response = await client.get('/rest/todos?and=(id.gte.1000,or(first_name.eq.John,last_name.eq.Smith))');

Operators

Operators

These operators are available:

Abbreviation In PostgreSQL Meaning
eq = equals
gt > greater than
gte >= greater than or equal
lt < less than
lte <= less than or equal
neq <> or != not equal
like LIKE LIKE operator (use * in place of %)
ilike ILIKE ILIKE operator (use * in place of %)
in IN one of a list of values, e.g. ?a=in.(1,2,3) – also supports commas in quoted strings like ?a=in.("hi,there","yes,you")
is IS checking for exact equality (null,true,false)
fts @@ Full-Text Search using to_tsquery
plfts @@ Full-Text Search using plainto_tsquery
phfts @@ Full-Text Search using phraseto_tsquery
wfts @@ Full-Text Search using websearch_to_tsquery
cs @> contains e.g. ?tags=cs.{example, new}
cd <@ contained in e.g. ?values=cd.{1,2,3}
ov && overlap (have points in common), e.g. ?period=ov.[2015-01-01,2021-02-30] – also supports array types, use curly braces instead of square brackets e.g.
sl << strictly left of, e.g. ?range=sl.(1,10)
sr >> strictly right of
nxr &< does not extend to the right of, e.g. ?range=nxr.(1,10)
nxl &> does not extend to the left of
adj - -
not NOT negates another operator, see below

To negate any operator, prefix it with not

?id=not.eq.2 or ?not.and=(id.gte.10,id.lte.100)

The fts filter operator has a number of options to support flexible textual queries. It supports the choice of plain vs phrase search and the language used for stemming.

The following examples illustrate the possibilities where the column body from the table articles is of type tsvector.

/articles?body=fts(german).danke

/articles?body=plfts.The%20Brown%20Fox

/articles?body=not.phfts(english).The%20Brown%20Fox

/articles?body=not.wfts(german).danke

Note

The availability of phrase search mode and websearch_to_tsquery depends on your PostgreSQL version

Filtering embedded relations

When the result contains rows from the tables related to the current table (that were requested using the select= query parameter), it's possible to filter the returned related rows similar to their top level counterparts. To accomplish that, one needs to prefix the column name from the related table with the relation name.

curl "http://localhost:8080/rest/projects?select=name,tasks(name)&tasks.completed=eq.false"
const response = await fetch(endpoint + '/rest/projects?select=name,tasks(name)&tasks.completed=eq.false');
const response = await client.get('/rest/projects?select=name,tasks(name)&tasks.completed=eq.false');
const response = await client.query({ 
    query: gql`
        query {
            projects{
                name
                tasks(order: {column: "name", direction: asc}){ name }
            }
        }
    `
});

Note

The filters on embedded relations act only on their level, they have no effect on the level above, i.e. in the example above, the response will contain the projects that have no "active" tasks.