Using jsonb with Ruby on Rails
Operators
Simple Example
Migration
class CreateApp < ActiveRecord::Migration
def change
create_table :apps do |t|
t.string :app_type
t.jsonb :content
end
add_index :apps, :content, using: :gin
end
end
# Note:- this will index everything on top level (color, text, payment)
content = {
color: red,
text: white,
payment: {
method: 'paypal',
amount: 999,
subscription: true
}
}
App.create(content: content)
Index payment method (using btree)
CREATE INDEX CONCURRENTLY app_content_payment_method ON apps USING BTREE((content#>>'{"payment", "method"}'))
Index color (using btree)
CREATE INDEX CONCURRENTLY app_content_color ON apps USING BTREE((content ->> 'color'))
Index everything on payment (using GIN) (this will index payment method, amount, subscription)
Do not use GIN if you do not know what the hash (json) is going to look like and/or if you dont have to index every keys.
CREATE INDEX CONCURRENTLY app_content_payment ON apps USING GIN((content -> 'payment'))
---
More details 👇
Rails introduced jsonb support in v4.2. Is as simple as using a string or text column. You can see below how to add a jsonb column to a new table and an existing one.
db/migrate/*_create_users.rb
class CreateUsers < ActiveRecord::Migration
def change
enable_extension 'citext'
create_table :users do |t|
t.text :name, null: false
t.citext :username, null: false
t.jsonb :preferences, null: false, default: '{}'
end
add_index :users, :preferences, using: :gin
end
end
db/migrate/*_add_jsonb_column_to_users.rb
class AddJsonbColumnToUsers < ActiveRecord::Migration
def change
add_column :users, :preferences, :jsonb, null: false, default: '{}'
add_index :users, :preferences, using: :gin
end
end
Notice that we’re also defining a GIN index; if you want to create an expression index for a given path, you’ll have to use execute. In this case, Rails won’t know how to dump your index using Ruby, so you’re better off changing the dump format to SQL.
config/initializers/active_record.rb
Rails.application.config.active_record.schema_format = :sql
db/migrate/*_add_index_to_preferences_path_on_users.rb
class AddIndexToPreferencesPathOnUsers < ActiveRecord::Migration
def change
execute <<-SQL
CREATE INDEX CONCURRENTLY user_prefs_newsletter_index ON users ((preferences->>'newsletter'))
SQL
end
end
Your model won’t need any configuration. Just create your records providing an object that supports JSON serialization.
user = User.create!({
name: 'John Doe',
username: 'johndoe',
preferences: {
twitter: 'johndoe',
github: 'johndoe',
blog: 'http://example.com'
}
})
Reload record from database to enforce serialization.
user.reload
Show preferences.
user.preferences
#=> {"blog"=>"http://example.com", "github"=>"johndoe", "twitter"=>"johndoe"}
Get blog.
user.preferences['blog']
#=> http://example.com
You can see that all keys are returned as strings. You can use a custom serializer so you can access the JSON object with symbols as well.
app/models/user.rb
class User < ActiveRecord::Base
serialize :preferences, HashSerializer
end
app/serializers/hash_serializer.rb
class HashSerializer
def self.dump(hash)
hash.to_json
end
def self.load(hash)
(hash || {}).with_indifferent_access
end
end
Another interesting ActiveRecord feature is store_accessor. If you see yourself setting some attributes frequently, you can create accessors, so you can just assign the property, instead of passing through the JSON. This also makes easier to validate properties and create forms. So, if we want to create a form for storing a blog url, Github and Twitter accounts, you can create something like this:
class User < ActiveRecord::Base
serialize :preferences, HashSerializer
store_accessor :preferences, :blog, :github, :twitter
end
Now you can simply assign these properties.
user = User.new(blog: 'http://example.org', github: 'johndoe')
user.preferences
#=> {"blog"=>"http://example.org", "github"=>"johndoe"}
user.blog
#=> http://example.org
user.preferences[:github]
#=> johndoe
user.preferences['github']
#=> johndoe
With store accessors you can add validation and create forms just like any other property.
Querying your jsonb column Now it’s time to use some query operators. PostgreSQL has many others, so read the documentation for a complete list.
Also, always remember to EXPLAIN the queries you’re executing; that way you can adapt your indexes for a better performance.
Users that opt-in newsletter
preferences->newsletter = true
User.where('preferences @> ?', {newsletter: true}.to_json)
Users that are interested in Ruby
preferences->interests = ['ruby', 'javascript', 'python']
User.where("preferences -> 'interests' ? :language", language: 'ruby')
This query won’t use the column index; if you need to query arrays, make sure you create an expression index.
CREATE INDEX CONCURRENTLY preferences_interests_on_users ON users USING GIN ((preferences->'interests'))
Users that set Twitter and Github accounts
preferences->twitter AND preferences->github
User.where('preferences ?& array[:keys]', keys: ['twitter', 'github'])
Users that set Twitter or Github account
preferences->twitter OR preferences->github
User.where('preferences ?| array[:keys]', keys: ['twitter', 'github'])
Users that live in San Francisco/California
preferences->state = 'SP' AND preferences->city = 'São Paulo'
User.where('preferences @> ?', {city: 'San Francisco', state:'CA'}.to_json)
How to do a 'LIKE' statement (helpful for finding form responses with particular information or spam in them)
query = "SELECT id FROM app_form_responses JOIN LATERAL JSONB_ARRAY_ELEMENTS(response::JSONB) r on r.value->>'value' LIKE '%zunyi35%' WHERE app_id = 16863756;"
ActiveRecord::Base.connection.exec_query(query).rows to return all ids
ActiveRecord::Base.connection.execute(query).count to return just count