Postgres JSON and JSONB type support on GraphQL
JSONBtypes enable storing schema-less (NoSQL) on Postgres
- Hasura GraphQL Engine supports
JSONBoperators are supported
- Postgres views created using derived data from JSONB columns can be queried through GraphQL, with filter operators like
Table of contents
- JSONB Boolean Operators
- Insert Mutations
- Update Mutations
- Derived data using views
You can explore this Heroku App to play around with the data/queries referred to in this blog post.
Postgres supports storing schema-less (NoSQL) data as JSON columns through dedicated
JSONB column types. Even though type safety is a major push for moving to GraphQL, there are real-world use-cases where you’d still need some columns which can store data for which schema is not decided already.
- An analytics system that logs events from various sources:
The payload from each source could be different and multiple clients might be writing queries to analyze this data.
- An e-commerce website’s database where each
producthas a strict set of basic parameters, like
price, but a varying range of specifications (or
specs). It is nearly impossible to create columns (schema) to capture all possible spec-fields for all kinds of products out there.
Let’s take a look at how we can use
JSONB to model this
product table and make some queries through Hasura GraphQL Engine.
Here is the schema for
product table on Postgres:
spec column can either be
JSONB . Both will make sure only a valid JSON can be inserted here, but with
JSONB columns, we can query based on the JSON keys. For more insight into hstore, JSON and JSONB columns, and notes on when to use them, checkout this blog post from Citus Data.
Once we deploy Hasura GraphQL Engine on this database, a GraphQL query and it’s response looks like this with some sample data:
specis resolved as the underlying JSON object. This gives clients flexibility to render these fields, without knowing the schema at all.
Query with JSONB Boolean Operators
v1.0.0-alpha17, we have added support for JSONB Boolean operators. You no longer need to query data inside these JSON documents using Postgres views. These operators are available in GraphQL queries directly.
For example, let’s say you want to query all laptops with 8GB RAM. This is what you can do to to make that query in GraphQL:
_contains operator is applied over the
spec variable to filter out all rows which contains that particular key-value pair at the top-level in the JSONB column. For more complex operations not supported by these operators, you can still use a view.
For more details about these operators, check Postgres docs.
Inserting arbitrary JSON into the table is also straightforward using Mutations. Here is an example:
Since GraphQL input arguments cannot be extended beyond
String types, the JSON object can only be sent as a variable. GraphiQL might show some errors since it does not know how to handle the
jsonb type. Nevertheless the queries works fine on the GraphQL Engine.
Hasura also supports Postgres JSONB operators. Available JSONB operators are
You can read more about JSONB operators here.
_append and _prepend
Let’s say there is an admin interface for our e-commerce application, where product owners can add or remove new specs.
Here’s how a new key can be added to spec:
_prepend can be used instead of
_append to add the new key at the beginning the object.
variables can be nested JSON objects too:
Here’s how arrays can be appended:
Removing an existing top-level key using
_delete_elem can be used to delete elements from arrays (only if the top object is an array in the
JSONB column) by their index. Read more at docs.
We can delete a key specified by a JSON path using this operation. Here’s how we can delete
Derived data using Postgres views
There could be use cases where the data stored in
JSONB columns need to be queried using some known keys. We can create views for these purposes.
For example, you need to show a separate listing for laptops for which you know that
disk will always be present in the
We create a view called
laptop_listing extracting certain field from the JSONB columns:
When this view is tracked by Hasura GraphQL Engine, it can be queried using GraphQL, making use of all powerful filter operations:
Let’s say we want show laptops with 8GB RAM only. A
where clause can be used to express the particular condition:
JSONB columns are useful for storing arbitrary data on Postgres. Using Hasura GraphQL Engine, you can access these columns via GraphQL, including
JSONB operator support through mutations. Further inspection on the contents of the column can be carried out by creating views and these views can be queried though GraphQL as well.
*Product data taken from dell.com and flipkart.com