Posted on Dec 30, 2019 • Edited on Jun 28, 2020

Rails + PostgreSQL JSONB (Part 2)

Rails + PostgreSQL JSONB Series
Part 1: Migration and CRUD
Part 2: store_accessor, Query, and Index
Part 3: Paginate JSONB Data

If you continue to read this article, I assume that you know Ruby, OOP in Ruby, RoR, Active Record, and PostgreSQL Query Language.

Before we start, if you haven't read Part 1, here is our schema:

# db/migrate/*_create_books.rb class CreateBooks < ActiveRecord::Migration[5.2] def change create_table :books do |t| t.integer :user_id t.jsonb :payload, null: false, default: '{}' end add_index :books, :payload, using: :gin end end

book_payload = { title: 'Hacking Growth', publisher: 'Currency', published_date: '2017-04-07', authors: [ { id: 1, name: 'Sean Ellis' }, { id: 2, name: 'Morgan Brown' } ] } Book.create(user_id: 1, payload: book_payload)

Let's start our journey!

Table of Contents:
1. store_accessor
2. Query
3. Index
4. Final Word

1. store_accessor

If you frequently access some attributes, you can use store_accessor. When you use store_accessor, you can call the column like normal column type.

# app/models/book.rb class Book < ApplicationRecord belongs_to :user serialize :payload, JsonbSerializers store_accessor :payload, :title, :publisher, :authors end

Book.last.payload['title'] # => "Hacking Growth" Book.last.title # => "Hacking Growth" Book.last.publisher # => "Currency" Book.last.authors # => [{"id"=> 1, "name"=>"Sean Ellis"}, {"id"=>2, "name"=>"Morgan Brown"}]

This is not the only usability of store_accessor. More about it:

# Create Book.create(user_id: 1, title: 'The godvader', publisher: 'Mario', published_date: '2002-03-01', authors: [{ id: 19, name: 'Mario Puzo' }]) # Update book = Book.last book.title = 'The Godfather' book.publisher = 'NAL' book.save

You can add validation too if you want! By this, you can combine JSON Schema and Active Record Validations for more powerful validation.

# app/models/book.rb class Book < ApplicationRecord belongs_to :user serialize :payload, JsonbSerializers store_accessor :payload, :title, :publisher, :authors validates :title, length: { in: 3..50 } end

In short, store_accessor is just a shortcut which defines getter and setter methods.

BUT, store_accessor does not allow you to access nested keys. You can only access the first layer. Say, you have this schema inside column payload in a model named City:

{ geolocation: { latitude: 48.856613, longitude: 2.352222 }, detail: { name: "Paris", url: "https://www.latlong.net/c/?lat=48.856613&long=2.352222" } }

You can only do this:

# app/models/city.rb class City < ApplicationRecord store_accessor :payload, :geolocation, :detail end

You can't access latitude, longitude, name, and url using store_accessor.

If you still want to do something like City.last.geolocation_latitude, then we can set our getter and setter methods manually:

# app/models/city.rb class City < ApplicationRecord store_accessor :payload, :geolocation, :detail def geolocation_latitude self.geolocation['latitude'] end def geolocation_latitude=(value) self.geolocation['latitude'] = value end # add these getter and setter each for 3 more times: # geolocation_longitude, detail_name, detail_url end

This way, you can access them as it is using store_accessor.

city = City.last city.geolocation_latitude # => 48.856613 city.detail_name # => "Paris" city.detail_name = 'Rome' city.save city.detail # => {"name"=>"Rome", "url"=>"https://www.latlong.net/c/?lat=48.856613&long=2.352222"}

Tips: You can implement Ruby Metaprogramming, so you don't have to create getter and setter methods one by one:

# app/models/city.rb class City < ApplicationRecord store_accessor :payload, :geolocation, :detail SCHEMA = { 'geolocation' => ['latitude', 'longitude'], 'detail' => ['name', 'url'] } SCHEMA.each do |key, val| val.each do |method| define_method "#{key}_#{method}" do self.send("#{key}")[method] end define_method "#{key}_#{method}=" do |arg| self.send("#{key}")[method] = arg end end end end

I've never done this before, because, as I've mentioned, we use store_accessor for some attributes that we use frequently.

2. Query

I'll just explain queries that I frequently used. Please refer to official documentation of PostgreSQL for full information about query.

Using JSONB column type, we can't rely 100% using ORM. We have to understand basic query, especially JSONB type query.

# Matches where 'Book' contains 'title': 'Hacking Growth' Book.where("payload ->> 'title' = :title", title: 'Hacking Growth') # This is just the same as above, but not save from SQL injection Book.where("payload ->> 'title' = ?", "Hacking Growth") # Same as the first example Book.where("payload @> ?", { title: 'Hacking Growth' }.to_json)

# Matches where 'Book' first author's name is Sean Ellis Book.where("payload -> 'authors' -> '0' ->> 'name' = :name", name: 'Sean Ellis') # Same as above Book.where("payload #>> '{authors,0,name}' = :name", name: 'Sean Ellis' # Same as above Book.where("payload -> 'authors' -> '0' @> :val", val: { name: 'Sean Ellis' }.to_json)

Now, go back to our City model from #1.

# Matches where 'City' contains 'detail':{'name': 'Paris'} City.where("payload -> 'detail' ->> 'name' = :name", name: 'Paris') # Same as above City.where("payload #>> '{detail,name}' = :name", name: 'Paris') # Same as above City.where("payload @> :val", val: { detail: { name: 'Paris'}}.to_json) # Same as above City.where("payload -> 'detail' @> :val", val: { name: 'Paris'}.to_json)

# Search City that has name AND url in payload['detail'] City.where("payload ->> 'detail' ?& array[:keys]", keys: ['name', 'url']) # Search City that has name OR url in payload['detail'] City.where("payload ->> 'detail' ?& array[:keys]", keys: ['name', 'url'])

Notice:

payload do not use quotation mark ('') because it is the name of the column. Whereas the attributes of the json always use quotation mark (''). Remember, you can name your column as you want, as long as ruby, rails, and postgresql allowed it.

When to use -> and ->> ? We use the -> operator to keep returning objects until we reach the final attribute where it’s ok to use ->> to return as text.

So if you have:

{ this: { very: { deep: { nested: 'yeah' } } } }

You do:

Model.where("payload -> 'this' -> 'very' -> 'deep' ->> 'nested' = :val", val: "yeah") # Btw, this is shorter: Model.where("payload #>> '{this,very,deep,nested}' = :val", val: "yeah")

If you want to search for integer/float, you need to add explicit type casts:

Book.where("(payload #>> '{authors,0,id}')::int = :val", val: 1) City.where("(payload -> 'geolocation' ->> 'latitude')::float = :val", val: 48.856613)

3. Index

Add index for JSONB column types is supported for rails >=5.0.0. I only cover for rails >=5.0.0.

Mostly, we are using GIN index. (Read this official documentation about GIN/GiST index types for more information.)

Let say we want to add partial index for title in our Book model, and we want to add partial index for name of the first author. Create it is as simple as this:

# db/migrate/*_create_books.rb class CreateBooks < ActiveRecord::Migration[5.2] def change create_table :books do |t| t.integer :user_id t.jsonb :payload, null: false, default: '{}' end add_index :books, :payload, "payload ->> 'title'", using: :gin, name: "index_pictures_on_title" add_index :books, :payload, "payload #>> '{authors,0,name}'", using: :gin, name: "index_pictures_on_first_author_name" end end