Postgres: Filter Using JSONB Objects

Introduction

The _contains, _contained_in, _has_key, _has_keys_any, _has_keys_all, _jsonb_path_exists and _jsonb_path_match operators are used to filter based on JSONB columns.

For more details on JSONB operators and Postgres equivalents, refer to the API reference.

_contains

Fetch all authors living within a particular pincode (present in address JSONB column):

GraphiQL

Query Variables

_contained_in

This example fetches all items where the "Tags" property contains the value "tag1" and is also contained in the list ["tag1", "tag2"].

GraphiQL

Query Variables

_has_key

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

GraphiQL

Query Variables

_has_keys_any

Fetch users who have either "admin" or "editor" permissions:

GraphiQL

Query Variables

_has_keys_all

Fetch user, where their id is "123", and check if the address has all the fields "street", "city", "state", "zip":

GraphiQL

Query Variables

_jsonb_path_exists

Fetch all orders where the items array contains at least one item with a quantity greater than 5. Uses the PostgreSQL @? operator with a SQL/JSON path expression:

GraphiQL

Query Variables

_jsonb_path_match

Fetch all products where the nested price value is less than 50. Uses the PostgreSQL @@ operator with a SQL/JSON path predicate expression:

GraphiQL

Query Variables