Postgres JSON and JSONB type support on GraphQL

• hstore, JSON and JSONB types enable storing schema-less (NoSQL) on Postgres
• Hasura GraphQL Engine supports JSON/JSONB columns
• JSONB operators are supported
• Postgres views created using derived data from JSONB columns can be queried through GraphQL, with filter operators like where

• Schema
• Queries
• JSONB Boolean Operators
• Insert Mutations
• Update Mutations
• _append [||]
• _prepend [||]
• _delete_key [-]
• _delete_elem [-]
• _delete_at_path [#-]
• Derived data using views
• Conclusion

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 hstore, JSON or 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.

For example,

• 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 product has a strict set of basic parameters, like name, category, 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:

The spec column can either be JSON or 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:

The JSONB column spec is 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

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

The _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.

Insert

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.

Update

Hasura also supports Postgres JSONB operators. Available JSONB operators are _append (||), _prepend (||), _delete_key (-), _delete_elem (-) and _delete_at_path (#-).

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.

The variables can be nested JSON objects too:

Here’s how arrays can be appended:

_delete_key

Removing an existing top-level key using _delete_key:

_delete_elem

_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.

_delete_at_path

We can delete a key specified by a JSON path using this operation. Here’s how we can delete spec.frequency_response.min :

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 processor, ram and disk will always be present in the spec.

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