ruk·si

🛤️ Ruby on Rails
PostgreSQL jsonb

Updated at 2015-09-06 20:12

jsonb is a json-like data type in PostgreSQL databases that can contain a hash of strings, numbers, booleans and nulls.

Don't use hstore type. hstore is jsonb's older hobo cousin', but columns don't allow nested structure and have only string type. jsonb is better for the most of cases.

Usage:

rails g migration AddPreferencesToUsers preferences:jsonb
class AddPreferencesToUsers < ActiveRecord::Migration
  def change
    add_column :users, :preferences, :jsonb, null: false, default: '{}'

    # use GIN index if you want to search these columns more efficiently
    # takes quite a lot of space if your JSON objects are large
    #add_index  :users, :preferences, using: :gin
  end
end
user = User.find(1)
user.preferences = {}                   # => {}
user.preferences = {auto_sign_in: true} # => {"auto_signin"=>true}
user.preferences['auto_sign_in']        # => true

# searching for contained value
User.where('preferences @> ?', {auto_sign_in: true}.to_json).count  # => 1
User.where('preferences @> ?', {auto_sign_in: false}.to_json).count # => 0

# searching for full match
User.where('preferences = ?', {}.to_json).count                     # empty json
User.where('preferences = ?', nil).count                            # nil

Sources