Skip to main content
Version: v2.x

Postgres: Filter Based on Fields of Nested Objects

Introduction

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 behavior 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.
Limitations

This is only supported for local relationships, such as relationships between two local database tables. This is not supported for remote relationships, such as remote database relationships or Remote Schema relationships.

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

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

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:

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

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:

conditionwhere 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}.

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

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 satisfy the condition.

For example,

conditionwhere 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}:

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

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:

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

Example where nested object(s) do not exist:

Fetch all authors which have not written any articles:

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

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.

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available