Using JSON in Postgres with Ruby and Sequel

With its fast, built-in support for JSON, it’s worth considering Postgres for storing and querying your JSON data. Instead of using a specialised document server, Postgres may be the right choice for your application.

As of Postgres 9.4 you can index data stored in a JSONB (binary JSON) column using GIN (Generalized Inverted Index) indexes. JSONB/GIN provides special operators to efficiently and rapidly query data.

And for Ruby developers, the Sequel gem offers a collection of convenient methods that make it easy to query Postgres JSONB columns.

Example usage

First, install the sequel_pg gem:

Next, create a table with a JSONB column and a GIN index and add some sample JSON data:

And then in Ruby:

Read the Sequel documentation for further methods supported by the pg_json_ops extension.

The right choice?

Postgres is a great solution for JSON in your application, especially if you are already using Postgres for structured data in other tables. It offers easy storage and, with GIN indexes and the Sequel gem, you get fast querying of data from Ruby.

Where it may not be the right choice is if your use-case requires frequent partial updates to JSON documents. You can of course retrieve the JSON blob, parse it, update the value in the hash, convert back to JSON and then update the column, but a dedicated document database like MongoDB may be a better option.

This article was first published at:
https://barryfrost.com/2017/10/postgres-json-sequel

Still scratching the itch to develop, although more meetings than mash-ups these days.

Still scratching the itch to develop, although more meetings than mash-ups these days.