Postgres json query how to#

We create a view called laptop_listing extracting certain field from the JSONB columns: We can create views for these purposes.įor 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. There could be use cases where the data stored in JSONB columns need to be queried using some known keys. Here’s how we can delete equency_response.min : Delete a nested key using `_delete_at_path` Derived data using Postgres views We can delete a key specified by a JSON path using this operation. _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. Removing an existing top-level key using _delete_key: Deleting the key `driver_units` from spec The variables can be nested JSON objects too:

_prepend can be used instead of _append to add the new key at the beginning the object. Here’s how a new key can be added to spec: Adding a new field to the spec using _append Let’s say there is an admin interface for our e-commerce application, where product owners can add or remove new specs. You can read more about JSONB operators here. Available JSONB operators are _append ( ||), _prepend ( ||), _delete_key ( -), _delete_elem ( -) and _delete_at_path ( #-). Hasura also supports Postgres JSONB operators. Nevertheless the queries works fine on the GraphQL Engine.

Postgres json query how to#

GraphiQL might show some errors since it does not know how to handle the jsonb type. Since GraphQL input arguments cannot be extended beyond String types, the JSON object can only be sent as a variable. Here is an example: Example for an insert mutation containing JSON Inserting arbitrary JSON into the table is also straightforward using Mutations. For more complex operations not supported by these operators, you can still use a view.įor more details about these operators, check Postgres docs. 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. This is what you can do to to make that query in GraphQL: Query all laptops with 8GB RAM These operators are available in GraphQL queries directly.įor example, let’s say you want to query all laptops with 8GB RAM. You no longer need to query data inside these JSON documents using Postgres views. Since v1.0.0-alpha17, we have added support for JSONB Boolean operators. This gives clients flexibility to render these fields, without knowing the schema at all. Once we deploy Hasura GraphQL Engine on this database, a GraphQL query and it’s response looks like this with some sample data: Example query after adding some sample data The JSONB column spec is resolved as the underlying JSON object. For more insight into hstore, JSON and JSONB columns, and notes on when to use them, checkout this blog post from Citus Data. Both will make sure only a valid JSON can be inserted here, but with JSONB columns, we can query based on the JSON keys. The spec column can either be JSON or JSONB . Here is the schema for product table on Postgres: Let’s take a look at how we can use JSONB to model this product table and make some queries through Hasura GraphQL Engine. It is nearly impossible to create columns (schema) to capture all possible spec-fields for all kinds of products out there.

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

The payload from each source could be different and multiple clients might be writing queries to analyze this data.

An analytics system that logs events from various sources:.

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. Postgres supports storing schema-less (NoSQL) data as JSON columns through dedicated hstore, JSON or JSONB column types.

You can explore this Heroku App to play around with the data/queries referred to in this blog post. Postgres views created using derived data from JSONB columns can be queried through GraphQL, with filter operators like where.Hasura GraphQL Engine supports JSON/ JSONB columns.hstore, JSON and JSONB types enable storing schema-less (NoSQL) on Postgres.