JSONb Accessor

Created by    

Adds typed jsonb backed fields as first class citizens to your ActiveRecord models. This gem is similar in spirit to HstoreAccessor, but the jsonb column in PostgreSQL has a few distinct advantages, mostly around nested documents and support for collections.

It also adds generic scopes for querying jsonb columns.

Table of Contents

• Installation
• Usage
• Scopes
• Single-Table Inheritance
• Dependencies
• Validations
• Upgrading
• Development
• Contributing

Installation

Add this line to your application's Gemfile:

gem "jsonb_accessor", "~> 1.0.0"

And then execute:

$ bundle install

Usage

First we must create a model which has a jsonb column available to store data into it:

class CreateProducts < ActiveRecord::Migration
  def change
    create_table :products do |t|
      t.jsonb :data
    end
  end
end

We can then declare the jsonb fields we wish to expose via the accessor:

class Product < ActiveRecord::Base
  jsonb_accessor :data,
    title: :string,
    external_id: :integer,
    reviewed_at: :datetime
end

Any type the attribute API supports. You can also implement your own type by following the example in the attribute documentation.

To pass through options like default and array to the attribute API, just put them in an array.

class Product < ActiveRecord::Base
  jsonb_accessor :data,
    title: [:string, default: "Untitled"],
    previous_titles: [:string, array: true, default: []]
end

The default option works pretty much as you would expect in practice; if no values are set for the attributes, a hash of the specified default values is saved to the jsonb column.

You can also pass in a store_key option.

class Product < ActiveRecord::Base
  jsonb_accessor :data, title: [:string, store_key: :t]
end

This allows you to use title for your getters and setters, but use t as the key in the jsonb column.

product = Product.new(title: "Foo")
product.title #=> "Foo"
product.data #=> { "t" => "Foo" }

Scopes

Jsonb Accessor provides several scopes to make it easier to query jsonb columns. jsonb_contains, jsonb_number_where, jsonb_time_where, and jsonb_where are available on all ActiveRecord::Base subclasses and don't require that you make use of the jsonb_accessor declaration.

If a class does have a jsonb_accessor declaration, then we define one custom scope. So, let's say we have a class that looks like this:

class Product < ActiveRecord::Base
  jsonb_accessor :data,
    name: :string,
    price: [:integer, store_key: :p],
    price_in_cents: :integer,
    reviewed_at: :datetime
end

Jsonb Accessor will add a scope to Product called like the json column with _where suffix, in our case data_where.

Product.all.data_where(name: "Granite Towel", price: 17)

Similarly, it will also add a data_where_not scope to Product.

Product.all.data_where_not(name: "Plasma Fork")

For number fields you can query using < or >or use plain english if that's what you prefer.

Product.all.data_where(price: { <: 15 })
Product.all.data_where(price: { <=: 15 })
Product.all.data_where(price: { less_than: 15 })
Product.all.data_where(price: { less_than_or_equal_to: 15 })

Product.all.data_where(price: { >: 15 })
Product.all.data_where(price: { >=: 15 })
Product.all.data_where(price: { greater_than: 15 })
Product.all.data_where(price: { greater_than_or_equal_to: 15 })

Product.all.data_where(price: { greater_than: 15, less_than: 30 })

For time related fields you can query using before and after.

Product.all.data_where(reviewed_at: { before: Time.current.beginning_of_week, after: 4.weeks.ago })

If you want to search for records within a certain time, date, or number range, just pass in the range (Note: this is just shorthand for the above mentioned before/after/less_than/less_than_or_equal_to/greater_than_or_equal_to/etc options).

Product.all.data_where(price: 10..20)
Product.all.data_where(price: 10...20)
Product.all.data_where(reviewed_at: Time.current..3.days.from_now)

This scope is a convenient wrapper around the jsonb_where scope that saves you from having to convert the given keys to the store keys and from specifying the column.

jsonb_where

Works just like the scope above except that it does not convert the given keys to store keys and you must specify the column name. For example:

Product.all.jsonb_where(:data, reviewed_at: { before: Time.current }, p: { greater_than: 5 })

# instead of

Product.all.data_where(reviewed_at: { before: Time.current }, price: { greater_than: 5 })

This scope makes use of the jsonb_contains, jsonb_number_where, and jsonb_time_where scopes.

jsonb_where_not

Just the opposite of jsonb_where. Note that this will automatically exclude all records that contain null in their jsonb column (the data column, in the example below).

Product.all.jsonb_where_not(:data, reviewed_at: { before: Time.current }, p: { greater_than: 5 })

<jsonb_attribute>_order

Orders your query according to values in the Jsonb Accessor fields similar to ActiveRecord's order.

Product.all.data_order(:price)
Product.all.data_order(:price, :reviewed_at)
Product.all.data_order(:price, reviewed_at: :desc)

It will convert your given keys into store keys if necessary.

jsonb_order

Allows you to order by a Jsonb Accessor field.

Product.all.jsonb_order(:data, :price, :asc)
Product.all.jsonb_order(:data, :price, :desc)

jsonb_contains

Returns all records that contain the given JSON paths.

Product.all.jsonb_contains(:data, title: "foo")
Product.all.jsonb_contains(:data, reviewed_at: 10.minutes.ago, p: 12) # Using the store key

Note: Under the hood, jsonb_contains uses the @> operator in Postgres so when you include an array query, the stored array and the array used for the query do not need to match exactly. For example, when queried with [1, 2], records that have arrays of [2, 1, 3] will be returned.

jsonb_excludes

Returns all records that exclude the given JSON paths. Pretty much the opposite of jsonb_contains. Note that this will automatically exclude all records that contain null in their jsonb column (the data column, in the example below).

Product.all.jsonb_excludes(:data, title: "foo")
Product.all.jsonb_excludes(:data, reviewed_at: 10.minutes.ago, p: 12) # Using the store key

jsonb_number_where

Returns all records that match the given criteria.

Product.all.jsonb_number_where(:data, :price_in_cents, :greater_than, 300)

It supports:

• >
• >=
• greater_than
• greater_than_or_equal_to
• <
• <=
• less_than
• less_than_or_equal_to

and it is indifferent to strings/symbols.

jsonb_number_where_not

Returns all records that do not match the given criteria. It's the opposite of jsonb_number_where. Note that this will automatically exclude all records that contain null in their jsonb column (the data column, in the example below).

Product.all.jsonb_number_where_not(:data, :price_in_cents, :greater_than, 300)

jsonb_time_where

Returns all records that match the given criteria.

Product.all.jsonb_time_where(:data, :reviewed_at, :before, 2.days.ago)

It supports before and after and is indifferent to strings/symbols.

jsonb_time_where_not

Returns all records that match the given criteria. The opposite of jsonb_time_where. Note that this will automatically exclude all records that contain null in their jsonb column (the data column, in the example below).

Product.all.jsonb_time_where_not(:data, :reviewed_at, :before, 2.days.ago)

Single-Table Inheritance

One of the big issues with ActiveRecord single-table inheritance (STI) is sparse columns. Essentially, as sub-types of the original table diverge further from their parent more columns are left empty in a given table. Postgres' jsonb type provides part of the solution in that the values in an jsonb column does not impose a structure - different rows can have different values.

We set up our table with an jsonb field:

# db/migration/<timestamp>_create_players.rb
class CreateVehicles < ActiveRecord::Migration
  def change
    create_table :vehicles do |t|
      t.string :make
      t.string :model
      t.integer :model_year
      t.string :type
      t.jsonb :data
    end
  end
end

And for our models:

# app/models/vehicle.rb
class Vehicle < ActiveRecord::Base
end

# app/models/vehicles/automobile.rb
class Automobile < Vehicle
  jsonb_accessor :data,
    axle_count: :integer,
    weight: :float
end

# app/models/vehicles/airplane.rb
class Airplane < Vehicle
  jsonb_accessor :data,
    engine_type: :string,
    safety_rating: :integer
end

From here any attributes specific to any sub-class can be stored in the jsonb column avoiding sparse data. Indices can also be created on individual fields in an jsonb column.

This approach was originally conceived by Joe Hirn in this blog post.

Validations

Because this gem promotes attributes nested into the JSON column to first level attributes, most validations should just work. Please leave us feedback if they're not working as expected.

Dependencies

• ActiveRecord >= 5.0
• Postgres >= 9.4 (in order to use the jsonb column type).

Upgrading

See the upgrade guide.

Development

After checking out the repo, run bin/setup to install dependencies (make sure postgres is running first).

Run bin/console for an interactive prompt that will allow you to experiment.

rake will run Rubocop and the specs.

Contributing

1. Fork it
2. Create your feature branch (git checkout -b my-new-feature)
3. Add tests and changes (run the tests with rake)
4. Commit your changes (git commit -am 'Add some feature')
5. Push to the branch (git push origin my-new-feature)
6. Create a new Pull Request