Data Access
Filtering
Basic usage
You can filter the returned rows by specifying conditions on columns. Each condition is a separate query string parameter.
curl http://localhost:3000/rest/todos?id=gt.10
Logical AND
Multiple conditions can be logically conjoined (WHERE cond1 AND cond2 ...)
curl "http://localhost:3000/rest/todos?id=gt.10&completed=eq.false"
Logical OR
Multiple conditions can also be disjoined (WHERE cond1 OR cond2 ...) by using the reserved query parameter or=
curl "http://localhost:3000/rest/people?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:3000/rest/people?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)
Full-Text search
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
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:3000/rest/projects?select=name,tasks(name)&tasks.completed=eq.false"
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.