BigQuery: 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 {
bigquery_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 {
bigquery_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 BigQuery type other than Geography
or Json (like Int, Numeric, String, Timestamp etc).
The following are examples of using the equality operators on different types.
Example: Integer (works with Numeric, BigDecimal, Float64, etc.)
Fetch data about an author whose id (an integer field) is equal to 3:
Example: String
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 DateTime etc.)
Fetch a list of articles that were published on a certain date (published_on is a datetime 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 BigQuery type other than Json or Geography (like Int, Numeric, String, Timestamp
etc).
The following are examples of using these operators on different types:
Example: Integer (works with Numeric, BigDecimal, Float64, etc.)
Fetch a list of articles rated 4 or more (rating is an integer field):
Example: String
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 DateTime 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 BigQuery type other than Geography or Json (like Int, Numeric, String, Timestamp etc).
The following are examples of using these operators on different types:
Example: Integer (works with Numeric, BigDecimal, Float64, etc.)
Fetch a list of articles rated 1, 3 or 5:
Example: String
Fetch a list of those authors whose names are NOT part of a list:
Text search or pattern matching operators (_like, _ilike, etc.)
The _like, _nlike, _ilike, _nilike operators are used for pattern matching on string/text fields.
Example: _like
Fetch a list of articles whose titles contain the word “amet”:
_like is case-sensitive. Use _ilike for case-insensitive search.
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:
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 {
bigquery_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:
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.
