Rails + PostgreSQL JSONB (Part 2)
Tags: rails, postgres
Published: 2019-12-30 04:19:52

| Rails + PostgreSQL JSONB Series |
| --------------------------- |
| [Part 1: Migration and CRUD](https://dev.to/kputra/rails-postgresql-jsonb-part-1-4ibg) |
| **Part 2: store_accessor, Query, and Index** |
| [Part 3: Paginate JSONB Data](https://dev.to/kputra/rails-postgresql-jsonb-part-3-708) |
*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:
```ruby
# 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
```
```ruby
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](#chapter-1)
[2. Query](#chapter-2)
[3. Index](#chapter-3)
[4. Final Word](#chapter-4)
<a name="chapter-1"></a>
#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.
```ruby
# app/models/book.rb
class Book < ApplicationRecord
  belongs_to :user
  serialize :payload, JsonbSerializers
  store_accessor :payload, :title, :publisher, :authors
end
```
```ruby
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:
```ruby
# 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.
```ruby
# 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`:
```ruby
{
  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:
```ruby
# 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:
```ruby
# 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`.
```ruby
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](https://dev.to/kputra/ruby-metaprogramming-part-1-1gjb), so you don't have to create getter and setter methods one by one:
```ruby
# 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**.
<a name="chapter-2"></a>
##2. Query
I'll just explain queries that I frequently used. **Please refer to [official documentation of PostgreSQL](https://www.postgresql.org/docs/9.5/functions-json.html) 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.

```ruby
# 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)
```
```ruby
# 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.
```ruby
# 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)
```
```ruby
# 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:
```ruby
{
  this: {
    very: {
      deep: {
        nested: 'yeah'
      }
    }
  }
}
```
You do:
```ruby
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:
```ruby
Book.where("(payload #>> '{authors,0,id}')::int = :val", val: 1)

City.where("(payload -> 'geolocation' ->> 'latitude')::float = :val", val: 48.856613)
```
<a name="chapter-3"></a>
##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](https://www.postgresql.org/docs/9.5/textsearch-indexes.html) 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:
```ruby
# 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
```
<a name="chapter-6"></a>
##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