Help us understand the problem. What is going on with this article?

RailsでJSONB型を使う

More than 3 years have passed since last update.

検証環境

Service
  PostgreSQL 9.4.4

Gem
  rails (4.2.0)
  activerecord (4.2.0)
  Gem pg (0.18.1)

使い方

マイグレーション

db/migrate/create_shops.rb
class CreateShops < ActiveRecord::Migration
  def change
    create_table :shops do |t|
      t.jsonb  :metas, null: false, default: '{}'
    end
  end
end

管理画面(ActiveAdmin)

ActiveAdmin::JsonEditor / Github

Gemfile
gem 'activeadmin_json_editor', '~> 0.0.6'
app/admin/shop.rb
ActiveAdmin.register Shop do
    permit_params :metas

    json_editor

    # specify the type does not necessarily
    form do |f|
      f.inputs do
        f.input :metas, as: :jsonb
      end
      f.actions
    end
end

サンプル

image

image

検索

モデル等
  ## (meta["country"]["name"] == "Japan") なレコードを検索する
  shops = Shop.where("meta #> '{country}' ->>'name' = ?", "Japan")

  ## (meta["country"].has_key("code") && meta["country"].has_key("name")) なレコードを検索する
  shops = Shop.where("meta #> '{country}' ?| array['code', 'name']")     #「?」がプレイスホルダーと被るが、第二引数を渡さなければ大丈夫
  shops = Shop.where("meta #> '{country}' ?| array['code', 'name']","2") #「?」が"2"に置換されてしまう
  shops = Shop.where("meta #> '{country}' ?| array[:keys]",keys:['code', 'name']) #名前付きプレースホルダを使えば「?」は置換されないので大丈夫

※ 記法が独特で間違えやすそうなので、scopeあたりで定義してきちんとテストして使うようにしたい。

結果サンプル

RailsConsole
[1] pry(main)> shops = Shop.where("meta #> '{country}' ->>'name' = ?", "Japan")
=>   Shop Load (0.5ms)  SELECT "shops".* FROM "shops" WHERE (meta #> '{country}' ->>'name' = 'Japan')
[#<Shop:0x007f6c01ba7c90
  id: 13,
  meta:
   {"country"=>{"code"=>"49", "name"=>"Japan"},
    "zipcode"=>"123-4567",
    "prefecture"=>{"code"=>"1234567", "name"=>"Hokkaido"}},
  created_at: Fri, 29 Jul 2016 21:57:31 JST +09:00,
  updated_at: Tue, 09 Aug 2016 23:27:40 JST +09:00>]

参照

  • Hash型になるので「.country」等でメソッドアクセスすることはできない
  • シンボルも不可
RailsConsole
[1] pry(main)> shop.meta.country
NoMethodError: undefined method `country' for #<Hash:0x007f6c01ac7398>
from (pry):18:in `<main>'

[2] pry(main)> shop.meta["country"]
=> {"code"=>"49", "name"=>"Japan"}

[3] pry(main)> shop.meta["country"]["code"]
=> "49"

[4] pry(main)> shop.meta["country"]["none_attribute"]
=> nil

[5] pry(main)> shop.meta["none_attribute"]["code"]
NoMethodError: undefined method `[]' for nil:NilClass
from (pry):23:in `<main>'

[6] pry(main)> shop.meta[:country]
=> nil

※必要ならばモデル化して使うことも検討

参考

  • ベンチマークやインデックスの貼り方などいろいろ

Using PostgreSQL and jsonb with Ruby on Rails
http://nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails

goosys
PHP、Railsします イラスト描きます 写真撮ります
bulbcorp
札幌で開発してます。自社プロダクトでシェアリングエコノミーパッケージ、VRサービス、茶道アプリなど作ってます。
http://bulbcorp.jp
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした