- Hasura GraphQL engine
- Getting started
- Schema
- Queries
- Simple object queries
- Nested object queries
- Aggregation queries
- Filter query results / search queries
- Sort query results
- Distinct query results
- Paginate query results
- Using multiple arguments
- Multiple queries in a request
- Query custom SQL Functions
- Derived data in queries
- Restrict access to certain fields
- Using variables / aliases / fragments / directives in queries
- Mutations
- Subscriptions
- Remote schemas
- Event Triggers
- Authentication & Authorization
- Migrations & Metadata
- Deploying
- Hasura CLI
- API Reference
- How it works
- Troubleshooting
- Guides / Tutorials / Resources
- Vulnerability reporting / disclosure
Get instant support on our discord server
Filter query results / search queries¶
Table of contents
- Equality operators (_eq, _neq)
- Greater than or less than operators (_gt, _lt, _gte, _lte)
- List based search operators (_in, _nin)
- Text search or pattern matching operators (_like, _similar, etc.)
- JSONB operators (_contains, _has_key, etc.)
- PostGIS spatial relationship operators (_st_contains, _st_crosses, etc.)
- Filter or check for null values (_is_null)
- Filter based on failure of some criteria (_not)
- Using multiple filters in the same query (_and, _or)
- Filter nested objects
- Filter based on nested objects’ fields
- Filter based on existence of nested objects
- Cast a field to a different type before filtering (_cast)
- Intersect operators on RASTER columns
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 {
author(
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 {
author (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.
Let’s take a look at different comparision operators that can be used to filter results and other advanced use cases:
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.).
The following are examples of using the equality operators on different types.
Example: Integer (works with Double, Float, Numeric, etc.)¶
Fetch data about author whose id (an integer field) is equal to 3:
query {
author(
where: {id: {_eq: 3}}
) {
id
name
}
}
Example: String or Text¶
Fetch a list of authors with name (a text field) as “Sidney”:
query {
author(
where: {name: {_eq: "Sidney"}}
) {
id
name
}
}
Example: Boolean¶
Fetch a list of articles that have not been published (is_published is a boolean field):
query {
article(
where: {is_published: {_eq: false}}
) {
id
title
is_published
}
}
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):
query {
article(
where: {published_on: {_eq: "2017-05-26"}}
) {
id
title
published_on
}
}
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.).
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):
query {
article(
where: {rating: {_gte: 4}}
) {
id
title
rating
}
}
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):
query {
author(
where: {name: {_gt: "M"}}
) {
id
name
}
}
Example: Date (works with Time, Timezone, etc.)¶
Fetch a list of articles that were published on or after date “01/01/2018”:
query {
article(
where: {published_on: {_gte: "2018-01-01"}}
) {
id
title
published_on
}
}
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.).
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:
query {
article(
where: {rating: {_in: [1,3,5]}}
) {
id
title
rating
}
}
Example: String or Text¶
Fetch a list of those authors whose names are NOT part of a list:
query {
author(
where: {name: {_nin: ["Justin","Sidney","April"]}}
) {
id
name
}
}
Text search or pattern matching operators (_like, _similar, etc.)¶
The _like, _nlike, _ilike, _nilike, _similar, _nsimilar operators are used for
pattern matching on string/text fields.
These operators behave exactly like their SQL counterparts
Example: _like¶
Fetch a list of articles whose titles contain the word “amet”:
query {
article(
where: {title: {_like: "%amet%"}}
) {
id
title
}
}
Note
_like is case-sensitive. Use _ilike for case-insensitive search.
Example: _similar¶
Fetch a list of authors whose names begin with A or C:
query {
author(
where: {name: {_similar: "(A|C)%"}}
) {
id
name
}
}
Note
_similar is case-sensitive
JSONB operators (_contains, _has_key, etc.)¶
The _contains, _contained_in, _has_key, _has_key_any and _has_key_all operators are used to filter
based on JSONB columns.
For more details on what these operators do, refer to Postgres docs.
Example: _contains¶
Fetch all authors living within a particular pincode (present in address JSONB column):
query get_authors_in_pincode ($jsonFilter: jsonb){
author(
where: {
address: {_contains: $jsonFilter }
}
) {
id
name
address
}
}
with variables:
{
"jsonFilter": {
"pincode": 560095
}
}
Example: _has_key¶
Fetch authors if the phone key is present in their JSONB address column:
query get_authors_if_phone {
author(
where: {
address: {_has_key: "phone" }
}
) {
id
name
address
}
}
PostGIS spatial relationship operators (_st_contains, _st_crosses, etc.)¶
The _st_contains, _st_crosses, _st_equals, _st_intersects, _st_overlaps, _st_touches,
_st_within and _st_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.
For more details on what these operators do, refer to PostGIS spatial relationship docs.
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:
query geom_table($polygon: geometry){
geom_table(
where: {geom_col: {_st_within: $polygon}}
){
id
geom_col
}
}
with variables:
{
"polygon": {
"type": "Polygon",
"coordinates": [
[
[ 0, 0 ],
[ 0, 2 ],
[ 2, 2 ],
[ 2, 0 ],
[ 0, 0 ]
]
]
}
}
Example: _st_d_within¶
Fetch a list of geometry values which are 3 units from given point value:
query geom_table($point: geometry){
geom_table(
where: {geom_col: {_st_d_within: {distance: 3, from: $point}}}
){
id
geom_col
}
}
with variables:
{
"point": {
"type": "Point",
"coordinates": [ 0, 0 ]
}
}
Filter or check for null values (_is_null)¶
Checking for null values can be achieved using the _is_null operator.
Example: Filter null values in a field¶
Fetch a list of articles that have a value in the published_on field:
query {
article(
where: {published_on: {_is_null: false}}
) {
id
title
published_on
}
}
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:
{
author(
where: {
_not: {
articles: { is_published: {_eq: true} }
}
}) {
id
name
articles {
title
is_published
}
}
}
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.
Note
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):
query {
article (
where: {
_and: [
{ published_on: {_gte: "2017-01-01"}},
{ published_on: {_lte: "2017-12-31"}}
]
}
)
{
id
title
published_on
}
}
Example: _or¶
Fetch a list of articles rated more than 4 or published after “01/01/2018”:
query {
article (
where: {
_or: [
{rating: {_gte: 4}},
{published_on: {_gte: "2018-01-01"}}
]
}
)
{
id
title
rating
published_on
}
}
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:
{
author {
id
name
articles(where: {rating: {_eq: 5}}) {
title
rating
}
}
}
Filter based on nested objects’ fields¶
You can use the fields of nested objects as well to filter your query results.
For example:
query {
article (where: {author: {name: {_eq: "Sidney"}}}) {
id
title
}
}
The behaviour of the comparision 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”:
{
article (
where: {
author: {
name: { _similar: "A%"}
}
}
) {
id
title
author {
name
}
}
}
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:
{
author(
where: {
articles: {rating: {_eq: 1}}
}
) {
id
name
articles {
title
rating
}
}
}
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}.
{
author (
where: {
_not: {
articles: {is_published: {_neq: true}}
}
}
) {
id
name
articles {
title
is_published
}
}
}
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}:
{
author (
where: {
_not: {
articles: {is_published: {_eq: true}}
}
}
) {
id
name
articles {
title
is_published
}
}
}
Filter based on existence of nested objects¶
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:¶
Fetch all authors which have at least one article written by them:
{
author (
where: {
articles: {}
}
) {
id
name
articles_aggregate {
aggregate {
count
}
}
}
}
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 casting
between PostGIS geometry and geography types is supported.
Casting using _cast corresponds directly to
SQL type casts.
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:
query cities_near($point: geography!, $distance: Float!) {
cities(
where: {location: {
_cast: {geography: {
_st_d_within: {from: $point, distance: $distance}
}}
}}
) {
name
}
}
with variables:
{
"point": {
"type": "Point",
"coordinates": [1, 50]
},
"distance": 1000000
}
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:
query cities_inside($polygon: geometry) {
cities(
where: {location: {
_cast: {geometry: {
_st_within: $polygon
}}
}}
) {
name
}
}
with variables:
{
"polygon": {
"type": "Polygon",
"crs": {
"type": "name",
"properties": { "name": "EPSG:4326" }
},
"coordinates": [
[
[-75, 40],
[-74, 40],
[-74, 41],
[-75, 41],
[-75, 40]
]
]
}
}
Note
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));
Intersect operators on RASTER columns¶
Intersect operators on columns with raster type are supported.
Refer to Postgis docs to know more about intersect functions on raster columns.
Please submit a feature request via github if you want support for more functions.
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> );
query getIntersectingValues ($rast: raster){
dummy_rast(where: {rast: {_st_intersects_rast: $rast}}){
rid
rast
}
}
with variables:
{
"rast": "0100000100000000000000004000000000000000C00000000000000000000000000000084000000000000000000000000000000000E610000001000100440001"
}
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 );
query getIntersectingValues ($point: geometry!){
dummy_rast(where: {rast: {_st_intersects_geom_nband: {geommin: $point}}}){
rid
rast
}
}
with variables:
{
"point": {
"type": "Point",
"coordinates": [
1,
2
],
"crs": {
"type": "name",
"properties": {
"name": "urn:ogc:def:crs:EPSG::4326"
}
}
}
}
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 );
query getIntersectingValues ($point: geometry!){
dummy_rast(where: {rast: {_st_intersects_nband_geom: {nband: 5 geommin: $point}}}){
rid
rast
}
}
with variables:
{
"point": {
"type": "Point",
"coordinates": [
1,
2
],
"crs": {
"type": "name",
"properties": {
"name": "urn:ogc:def:crs:EPSG::4326"
}
}
}
}
