JSONB with PostgreSQL and Rails

Primespot Engineering - May 20, 2020

Engineering

PostgreSQL supports some excellent JSON features. With these features, we are able to replace some of what a NoSQL database brings to the table.

It’s worth noting that these features are somewhat basic and don’t entirely replace a document storage database such as MongoDB. However, if your data is mostly relational, and you only need some sprinkles of JSON, these features might cover your needs.

First of all, PostgreSQL offers two JSON data types: json and jsonb.

The json data type stores a JSON object as a string in whichever way it was fed to PostgreSQL. Since the data is stored as a string, the order of the keys of the objects will be preserved.

The jsonb data type is stored as a binary representation of the data fed to PostgreSQL. This allows for faster querying and more advanced indexing. It comes at the expense of slower data build time and additional space requirements, though.

Furthermore, data stored as jsonb will be stripped of leading and trailing whitespace. The order of the keys won’t be preserved. And duplicate keys will not be stored.

Once data is stored in a jsonb column, special PostgreSQL operators are used to perform queries.

PostgreSQL jsonb operators

Some of the most commonly used operators can be found in the table below.

Operator	Function	Example
->	Query	data->'books'
->>	Query as text	data->'books'->>0
?	Existence	data ? 'books'
?\|	Any exists	data ?\| array['main', 'other']
?&	All exists	data ?& array['main', 'other']
@>	Containment	data->'books' @> '["The Call of the Wild"]'
#>	Path	data->'settings' #> '{top,nested}'
#>>	Path as text	data->'settings' #>> '{top,nested}'
\|\|	Concatenation	data \|\| { "a": 2 }'

Examples

Here are some examples of these operators in action.

SELECT data->'title' AS title FROM books;

SELECT * FROM books WHERE data->'published' = 'false';

SELECT * FROM books WHERE data->’books’ ? ‘readers’;

SELECT * FROM books WHERE data->’books’->’genre’ ?| array[drama, horror];

SELECT * FROM books WHERE data->’books’->’tags’ ?& array[new, top];

SELECT * FROM books WHERE data->’books’->’genre’ ?| array[drama, horror];

SELECT * FROM books WHERE data->’specials’ @> ‘[“discounts”]’::jsonb

Usage with Rails

Rails provides some support for using jsonb with PostgreSQL. The jsonb data type exists while creating migrations. Rails doesn’t provide any ActiveRecord helpers for querying jsonb columns, though. You have to execute the queries with SQL.

Here are a few examples.

Model.where("data @> ?", [{name: "To Kill a Mockingbird"}].to_json)

Model.where("data @> ?", {"interests": ["programming"]}.to_json)

Model.where("data -> 'customers' @> ?", [{name: "david"}].to_json)

Notice here we are using the to_json method to make the value compatible with PostgreSQL’s jsonb querying capabilities.

Raw SQL queries in Rails

For more advanced jsonb queries, you can execute raw SQL instead of going through Active Record. Rails provides the following method for executing a raw SQL query.

ActiveRecord::Base.connection.execute()

This method will return an array of results.

JSONB with PostgreSQL and Rails

PostgreSQL jsonb operators

Examples

Usage with Rails

Raw SQL queries in Rails

Further reading and references