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

4. Final Word

This is the end of Part 2, and also the end of Series Rails + PostgresQL JSONB. I'll update this Series if I found something useful.

source: myself and extract from many source, I don't save them, I just write what I remember

Comments

[mabras]

You are a pro!
Thanks.