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"
}
}
}
}