Skip to main content
Version: v2.x

Postgres: Filter Using Comparisons

Introduction

Comparison operators are used to compare values of the same type. For example, to compare two numbers, two strings, two dates, etc.

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:

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

Example: String or Text

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

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

Example: Boolean

Fetch a list of articles that have not been published (is_published is a boolean field):

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

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

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

Example: Integer (works with Integer, Float, Double, etc.)

Fetch a list of users whose age is not 30 (age is an Integer field):

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available
Caveat for "null" values

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:

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

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, Numeric, etc.)

This query retrieves all users whose age is less than 30. The _lt operator is a comparison operator that means "less than". It is used to filter records based on a specified value.

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

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

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

Example: Integer (works with Double, Float, etc.)

Fetch a list of all products with a price less than or equal to 10.

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

Example: Integer (works with Double, Float, etc.)

Fetch a list of articles rated 4 or more (rating is an integer field):

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

Example: Date (works with Time, Timezone, etc.)

Fetch a list of articles that were published on or after date "01/01/2018":

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

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:

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

Example: String or Text

Fetch a list of those authors whose names are NOT part of a list:

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

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:

GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available