Postgres: Filter query results / search queries
The where argument
You can use the where argument in your queries to filter results based
on some field’s values (even nested objects' fields). You can even use
multiple filters in the same where clause using the _and or the
_or operators.
For example, to fetch data for an author whose name is "Sidney":
query {
authors(
where: {name: {_eq: "Sidney"}}
) {
id
name
}
}
You can also use nested objects` fields to filter rows from a table and also filter the nested objects as well.
For example, to fetch a list of authors who have articles with a rating greater than 4 along with those articles:
query {
authors (where: {articles: {rating: {_gt: 4}}}) {
id
name
articles (where: {rating: {_gt: 4}}) {
id
title
rating
}
}
}
Here _eq and _gt are examples of comparison operators that can be
used in the where argument to filter on equality.
You can see the complete specification of the where argument in the
API reference.
Comparison operators
Let’s take a look at different comparison operators that can be used to filter results.
Equality operators (_eq, _neq)
The _eq (equal to) or the _neq (not equal to) operators are
compatible with any Postgres type other than json or jsonB (like
Integer, Float, Double, Text, Boolean,
Date/Time/Timestamp, etc.).
For more details on equality operators and Postgres equivalents, refer to the API reference.
The following are examples of using the equality operators on different types.
Example: Integer (works with Double, Float, Numeric, etc.)
Fetch data about an author whose id (an integer field) is equal to
3:
Example: String or Text
Fetch a list of authors with name (a text field) as "Sidney":
Example: Boolean
Fetch a list of articles that have not been published (is_published is
a boolean field):
Example: Date (works with Time, Timezone, etc.)
Fetch a list of articles that were published on a certain date
(published_on is a Date field):
By design, the _eq or _neq operators will not return rows with
null values.
To also return rows with null values, the _is_null operator needs to
be used along with these joined by the _or operator.
For example, to fetch a list of articles where the is_published column
is either false or null:
Greater than or less than operators (_gt, _lt, _gte, _lte)
The _gt (greater than), _lt (less than), _gte (greater than or
equal to), _lte (less than or equal to) operators are compatible with
any Postgres type other than json or jsonB (like Integer, Float,
Double, Text, Boolean, Date/Time/Timestamp, etc.).
For more details on greater than or less than operators and Postgres equivalents, refer to the API reference.
The following are examples of using these operators on different types:
Example: Integer (works with Double, Float, etc.)
Fetch a list of articles rated 4 or more (rating is an integer field):
Example: String or Text
Fetch a list of authors whose names begin with M or any letter that follows M (essentially, a filter based on a dictionary sort):
Example: Date (works with Time, Timezone, etc.)
Fetch a list of articles that were published on or after date "01/01/2018":
List based search operators (_in, _nin)
The _in (in a list) and _nin (not in list) operators are used to
compare field values to a list of values. They are compatible with any
Postgres type other than json or jsonB (like Integer, Float,
Double, Text, Boolean, Date/Time/Timestamp, etc.).
For more details on list based search operators and Postgres equivalents, refer to the API reference.
The following are examples of using these operators on different types:
Example: Integer (works with Double, Float, etc.)
Fetch a list of articles rated 1, 3 or 5:
Example: String or Text
Fetch a list of those authors whose names are NOT part of a list:
Text search or pattern matching operators (_like, _similar, _regex, etc.)
The _like, _nlike, _ilike, _nilike, _similar, _nsimilar,
_regex, _nregex, _iregex, _niregex operators are used for
pattern matching on string/text fields.
For more details on text search operators and Postgres equivalents, refer to the API reference.
Example: _like
Fetch a list of articles whose titles contain the word “amet”:
_like is case-sensitive. Use _ilike for case-insensitive search.
Example: _similar
Fetch a list of authors whose names begin with A or C:
_similar is case-sensitive
Example: _regex
Fetch a list of articles whose titles match the regex “[ae]met”:
_regex is case-sensitive. Use _iregex for case-insensitive search.
regex operators are supported in in v2.0.0 and above
JSONB operators (_contains, _has_key, etc.)
The _contains, _contained_in, _has_key, _has_keys_any and
_has_keys_all operators are used to filter based on JSONB columns.
For more details on JSONB operators and Postgres equivalents, refer to the API reference.
Example: _contains
Fetch all authors living within a particular pincode (present in address JSONB column):
Example: _has_key
Fetch authors if the phone key is present in their JSONB address
column:
PostGIS spatial relationship operators (_st_contains, _st_crosses, etc.)
The _st_contains, _st_crosses, _st_equals, _st_intersects,
_st_3d_intersects, _st_overlaps, _st_touches, _st_within,
_st_d_within, and _st_3d_d_within operators are used to filter based
on geometry like columns.
_st_d_within and _st_intersects can be used on geography columns
also (but their 3D variations are for geometry only).
For more details on spatial relationship operators and Postgres equivalents, refer to the API reference.
Use JSON representation (see GeoJSON) of geometry and
geography values in variables as shown in the following examples:
Example: _st_within
Fetch a list of geometry values which are within the given polygon
value:
Example: _st_d_within
Fetch a list of geometry values which are 3 units from a given point
value:
Example: _st_3d_d_within
This is completely analogous to the _st_d_within example above, the
only difference being that our coordinates now have three components
instead of two.
Example: _st_3d_intersects
Fetch a list of (3D) geometry values which intersect a given polygon
value:
Filter or check for null values (_is_null)
Checking for null values can be achieved using the _is_null operator.
For more details on the _is_null operator and Postgres equivalent,
refer to the API reference.
Example: Filter null values in a field
Fetch a list of articles that have a value in the published_on field:
Intersect operators on RASTER columns (_st_intersects_rast, etc)
Intersect operators on columns with raster type are supported. Please
submit a feature request via GitHub if you want support
for more functions.
For more details on intersect operators on raster columns and Postgres equivalents, refer to the API reference.
Example: _st_intersects_rast
Filter the raster values which intersect the input raster value.
Executes the following SQL function:
boolean ST_Intersects( raster <raster-col> , raster <raster-value> );
Example: _st_intersects_geom_nband
Filter the raster values which intersect the input geometry value and optional band number.
Executes the following SQL function:
boolean ST_Intersects( raster <raster-col> , geometry geommin , integer nband=NULL );
Example: _st_intersects_nband_geom
Filter the raster values (with specified band number) which intersect the input geometry value.
Executes the following SQL function:
boolean ST_Intersects( raster <raster-col> , integer nband , geometry geommin );
ltree operators (_ancestor, _matches, etc.)
Comparison operators on columns with ltree, lquery or ltxtquery
types are supported.
Please submit a feature request via GitHub if you want support for more functions.
For more details on ltree operators and Postgres equivalents, refer to
the API reference.
Example: _ancestor
Select ancestors of an ltree argument
Example: _matches_any
Select ltree paths matching any lquery regex in an array
Filter based on failure of some criteria (_not)
The _not operator can be used to fetch results for which some
condition does not hold true. i.e. to invert the filter set for a
condition.
Example: _not
Fetch all authors who don't have any published articles:
Using multiple filters in the same query (_and, _or)
You can group multiple parameters in the same where argument using the
_and or the _or operators to filter results based on more than one
criteria.
You can use the _or and _and operators along with the _not
operator to create arbitrarily complex boolean expressions involving
multiple filtering criteria.
Example: _and
Fetch a list of articles published in a specific time-frame (for example: in year 2017):
Certain _and expressions can be expressed in a simpler format using
some syntactic sugar. See the API reference for more details.
Example: _or
Fetch a list of articles rated more than 4 or published after "01/01/2018":
The _or operator expects an array of expressions as input. If an
object is passed as input it will behave like the _and operator as
explained in the API reference
Filter nested objects
The where argument can be used in array relationships as well to
filter the nested objects. Object relationships have only one nested
object and hence they do not expose the where argument.
Example:
Fetch all authors with only their 5 rated articles:
Filter based on nested objects' fields
You can use the fields of nested objects as well to filter your query results.
For example:
query {
articles (where: {author: {name: {_eq: "Sidney"}}}) {
id
title
}
}
The behaviour of the comparison operators depends on whether the nested objects are a single object related via an object relationship or an array of objects related via an array relationship.
- In case of an object relationship, a row will be returned if the single nested object satisfies the defined condition.
- In case of an array relationship, a row will be returned if any of the nested objects satisfy the defined condition.
Let's look at a few use cases based on the above:
Fetch if the single nested object defined via an object relationship satisfies a condition
Example:
Fetch all articles whose author's name starts with "A":
Fetch if any of the nested objects defined via an array relationship satisfy a condition
Example:
Fetch all authors which have written at least one article which is rated 1:
Fetch if all of the nested objects defined via an array relationship satisfy a condition
By default a row is returned if any of the nested objects satisfy a
condition. To achieve the above, we need to frame the where expression
as {_not: {inverse-of-condition}}. This reads as: fetch if not (any of
the nested objects satisfy the inverted condition) i.e. all of the
nested objects satisfy the condition.
For example:
| condition | where expression |
|---|---|
{object: {field: {_eq: "value"}}} | {_not: {object: {field: {_neq: "value"}}} |
{object: {field: {_gt: "value"}}} | {_not: {object: {field: {_lte: "value"}}} |
Example:
Fetch all authors which have all of their articles published i.e. have
{is_published {_eq: true}.
Fetch if none of the nested objects defined via an array relationship satisfy a condition
By default a row is returned if any of the nested objects satisfy a
condition. To achieve the above, we need to frame the where expression
as {_not: {condition}}. This reads as: fetch if not (any of the nested
objects satisfy the condition) i.e. none of the nested objects satisy
the condition.
For example,
| condition | where expression |
|---|---|
{object: {field: {_eq: "value"}}} | {_not: {object: {field: {_eq: "value"}}} |
{object: {field: {_gt: "value"}}} | {_not: {object: {field: {_gt: "value"}}} |
Example:
Fetch all authors which have none of their articles published i.e. have
{is_published {_eq: true}:
Fetch if nested object(s) exist/do not exist
You can filter results based on if they have nested objects by checking
if any nested objects exist. This can be achieved by using the
expression {} which evaluates to true if any object exists.
Example where nested object(s) exist:
Fetch all authors which have at least one article written by them:
Example where nested object(s) do not exist:
Fetch all authors which have not written any articles:
Filter based on computed fields
You can use computed fields to filter your query results.
For example:
query {
author (where: {full_name: {_ilike: "%bob%"}}){
id
first_name
last_name
}
}
The behaviour of the comparison operators depends on whether the computed fields return scalar type values or set of table rows.
- In case of scalar type, a row will be returned if the computed field returned scalar value satisfied the defined condition.
- In case of table row type, a row will be returned if any of the returned rows sastisfy the defined condition.
Let's look at a few use cases based on the above:
Fetch if the scalar value returned by the computed field satisfies a condition
Example:
A computed field total_marks defined to a student table which
computes the total sum of marks obtained from each subject. Fetch all
students whose total marks is above "80":
Fetch if any of the returned table rows by the computed field satisfy a condition
Example:
A computed field get_published_articles defined to a author table
which returns set of article rows published. Fetch all authors who
have atleast a published article in medicine field:
Fetch if aggregate value of the returned table rows by the computed field satisfies a condition
Example:
A computed field get_published_articles defined to a author table
which returns set of article rows published. Fetch all authors whose
count of published articles is more than 10:
Filter based on aggregations of nested array fields
You can filter based on aggregations over the elements of an array relationship field.
The aggregation functions supported are:
avg(number): Computes the average (arithmetic mean) of all the non-null input values.bool_and(bool): Returns true if all non-null input values are true, otherwise false.bool_or(bool): Returns true if any non-null input value is true, otherwise false.count(*),count(col1, col2, ..): Computes the number of input rows in which the input value is not null.max(number): Computes the maximum of the non-null input values.min(number): Computes the minimum of the non-null input values.sum(number): Computes the sum of the non-null input values.corr(Y number, X number): Computes the correlation coefficient.covar_samp(Y number, X number): Computes the sample covariance.stddev_samp(number): Computes the sample standard deviation of the input values.var_samp(number): Computes the sample variance of the input values (square of the sample standard deviation).
The semantics of the above functions is documented in the PostgreSQL documentation.
For the complete specification of the schema of aggregation predicates, see the API reference: AggregationExp.
Example:
Assume we have a dataset of restaurants with user-submitted reviews that contain a rating.
Suppose we want to query only the best restaurants. In that case, we can filter by the average over the ratings a restaurant has received and only include restaurants that have at least some number of reviews from distinct users.
Cast a field to a different type before filtering (_cast)
The _cast operator can be used to cast a field to a different type, which allows type-specific
operators to be used on fields that otherwise would not support them.
Currently, only the following type casts are supported:
- between PostGIS
geometryandgeographytypes - from Postgres
jsonbtype tostringtype.
Casting using _cast corresponds directly to SQL type casts.
Example: cast jsonb to string
Columns of type jsonb can be cast to String to use text operators on a
jsonb field:
Example: cast geometry to geography
Filtering using _st_d_within over large distances can be inaccurate
for location data stored in geometry columns. For accurate queries,
cast the field to geography before comparing:
Example: cast geography to geometry
Columns of type geography are more accurate, but they don’t support as
many operations as geometry. Cast to geometry to use those
operations in a filter:
For performant queries that filter on casted fields, create an
expression index
on the casted column. For example, if you frequently perform queries on
a field location of type geometry casted to type geography, you
should create an index like the following:
CREATE INDEX cities_location_geography ON cities USING GIST ((location::geography));
The TRUE expression ( { } )
The expression {} evaluates to true if an object exists (even if
it's null).
For example:
- any query with the condition
{ where: {} }will return all objects without applying any filter. - any query with the condition
{ where: { nested_object: {} } }will return all objects for which atleast onenested_objectexists.
Evaluation of null values in comparison expressions
In versions v2.0.0 and above, if in any comparison expression a
null value is passed, a type mismatch error will be thrown.
For example, the expression { where: {id: { _eq: null }}} will throw
an error.
In versions v1.3.3 and below, if in any comparison expression a
null value is passed, the expression gets reduced to {}, the
TRUE expression.
For example, the expression { where: { id: {_eq: null }}} will be
reduced to { where: {id: {}} } which will return all objects for which
an id is set, i.e. all objects will be returned.
This behaviour can be preserved in versions v2.0.0 and above by setting
the HASURA_GRAPHQL_V1_BOOLEAN_NULL_COLLAPSE env var to true.
