Filter query results / search queries¶

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:

query {
  authors(
    where: {id: {_eq: 3}}
  ) {
    id
    name
  }
}

Example: String or Text

Fetch a list of authors with name (a text field) as “Sidney”:

query {
  authors(
    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 {
  articles(
    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 {
  articles(
    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.).

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):

query {
  articles(
    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 {
  authors(
    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 {
  articles(
    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.).

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:

query {
  articles(
    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 {
  authors(
    where: {name: {_nin: ["Justin","Sidney","April"]}}
  ) {
    id
    name
  }
}

Text search or pattern matching operators (_like, _similar, 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”:

query {
  articles(
    where: {title: {_like: "%amet%"}}
  ) {
    id
    title
  }
}

Example: _similar

Fetch a list of authors whose names begin with A or C:

query {
  authors(
    where: {name: {_similar: "(A|C)%"}}
  ) {
    id
    name
  }
}

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):

query get_authors_in_pincode ($jsonFilter: jsonb){
  authors(
    where: {
      address: {_contains: $jsonFilter }
    }
  ) {
    id
    name
    address
  }
}

{
  "jsonFilter": {
    "pincode": 560095
  }
}

Example: _has_key

Fetch authors if the phone key is present in their JSONB address column:

query get_authors_if_phone {
  authors(
    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 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:

query geom_table($polygon: geometry){
  geom_table(
    where: {geom_col: {_st_within: $polygon}}
  ){
    id
    geom_col
  }
}

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

{
  "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.

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:

query {
  articles(
    where: {published_on: {_is_null: false}}
  ) {
    id
    title
    published_on
  }
}

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> );

query getIntersectingValues ($rast: raster){
  dummy_rast(where: {rast: {_st_intersects_rast: $rast}}){
    rid
    rast
  }
}

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

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

{
  "point": {
    "type": "Point",
    "coordinates": [
      1,
      2
    ],
    "crs": {
      "type": "name",
      "properties": {
        "name": "urn:ogc:def:crs:EPSG::4326"
      }
    }
  }
}

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”:

{
  articles (
    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:

{
  authors(
    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:

Example:

Fetch all authors which have all of their articles published i.e. have {is_published {_eq: true}.

{
  authors (
    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,

Example:

Fetch all authors which have none of their articles published i.e. have {is_published {_eq: true}:

{
  authors(
    where: {
      _not: {
        articles: {is_published: {_eq: true}}
      }
    }
  ) {
    id
    name
    articles {
      title
      is_published
    }
  }
}

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:

{
  authors (
    where: {
      articles: {}
    }
  ) {
    id
    name
    articles_aggregate {
      aggregate {
        count
      }
    }
  }
}

Example where nested object(s) do not exist:

Fetch all authors which have not written any articles:

{
  authors (
    where: {
      _not: {
        articles: {}
      }
    }
  ) {
    id
    name
    articles_aggregate {
      aggregate {
        count
      }
    }
  }
}