| 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
Top comments (1)
You are a pro!
Thanks.