摘要：但這些不會(huì)發(fā)生列中，這種查找使用了索引，卻并沒有像使用表達(dá)式索引那樣將速度優(yōu)化的很好。這也使得數(shù)據(jù)驗(yàn)證和創(chuàng)建表單更加簡單。在每一個(gè)新版本中，使用和都比過去更加容易，變得更加出色。因此，嘗試使用最新的版本，付出總是會(huì)很快得到回報(bào)的。

轉(zhuǎn)載翻譯自http://nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails

PostgreSQL 9.4 引入了jsonb，一個(gè)新的列類型用于存儲(chǔ)文檔到你的關(guān)系數(shù)據(jù)庫中。jsonb和json在更高的層面上看起來幾乎是一樣的，但在存儲(chǔ)實(shí)現(xiàn)上是不同的。

使用jsonb的優(yōu)勢(shì)在于你可以輕易的整合關(guān)系型數(shù)據(jù)和非關(guān)系型數(shù)據(jù)，在性能方面，可以比大多數(shù)類似于MongoDB這樣的非關(guān)系數(shù)據(jù)庫更好

因此，兩種列類型之間的區(qū)別是什么？當(dāng)我們比較寫入數(shù)據(jù)速度時(shí)，由于數(shù)據(jù)存儲(chǔ)的方式的原因，jsonb會(huì)比json稍微的慢一點(diǎn)。

json存儲(chǔ)完整復(fù)制過來的文本輸入，必須一遍又一遍的解析在你調(diào)用任何函數(shù)的時(shí)候。它不支持索引，但你可以為查詢創(chuàng)建表達(dá)式索引。

jsonb存儲(chǔ)的二進(jìn)制格式，避免了重新解析數(shù)據(jù)結(jié)構(gòu)。它支持索引，這意味著你可以不使用指定的索引就能查詢?nèi)魏温窂健?/p>

其他的不同包括，json列會(huì)每次都解析存儲(chǔ)的值，這意味著鍵的順序要和輸入的時(shí)候一樣。但jsonb不同，以二進(jìn)制格式存儲(chǔ)且不保證鍵的順序。因此，如果你有軟件需要依賴鍵的順序，jsonb可能不是你的應(yīng)用的最佳選擇。

讓我們運(yùn)行一個(gè)簡單的基準(zhǔn)測(cè)試。在這個(gè)例子中，我使用下面這樣一個(gè)json數(shù)據(jù)結(jié)構(gòu)：

json{
  "twitter": "johndoe1",
  "github": "johndoe1",
  "bio": "Lorem ipsum dolor sit amet, consectetur adipisicing elit. Labore impedit 
          aliquam sapiente dolore magni aliquid ipsa ad, enim, esse ut reprehenderit 
          quaerat deleniti fugit eaque. Vero eligendi voluptatibus atque, asperiores.",
  "blog": "http://johndoe1.example.com",
  "interests": [
    "music",
    "movies",
    "programming"
  ],
  "age": 42,
  "newsletter": true
}

Rehearsal ------------------------------------------------
insert jsonb   2.690000   0.590000   3.280000 ( >12.572343)
insert json    2.690000   0.590000   3.280000 ( 12.766534)
--------------------------------------- total: 6.560000sec

-----------------------------------------user     system      total        real
insert jsonb   2.680000   0.590000   3.270000 ( 13.206602)
insert json    2.650000   0.580000   3.230000 ( 12.577138)

sql
CREATE TABLE users (
  id serial not null,
  settings jsonb not null default "{}",
  preferences json not null default "{}"
);

CREATE INDEX settings_index ON users USING gin (settings);
CREATE INDEX twitter_settings_index ON users ((settings->>"github"));
CREATE INDEX preferences_index ON users ((preferences->>"github"));

Rehearsal -----------------------------------------------------------------
read jsonb (index column)       0.030000   0.030000   0.060000 (  3.673465)
read jsonb (expression index)   0.010000   0.010000   0.020000 (  0.087105)
read json (expression index)    0.010000   0.020000   0.030000 (  0.080121)
read json (no index)            0.060000   0.030000   0.090000 (113.206747)
-------------------------------------------------------- total: 0.200000sec

-----------------------------------------user     system      total        real
read jsonb (index column)       0.010000   0.020000   0.030000 (  0.092476)
read jsonb (expression index)   0.010000   0.010000   0.020000 (  0.078916)
read json (expression index)    0.010000   0.010000   0.020000 (  0.081908)
read json (no index)            0.050000   0.040000   0.090000 (110.761944)

sql
EXPLAIN
SELECT *
FROM users
WHERE settings @> "{"twitter": "john30000"}" LIMIT 1;

--                                      QUERY PLAN
-- -------------------------------------------------------------------------------------
--  Limit  (cost=28.23..31.96 rows=1 width=468)
--    ->  Bitmap Heap Scan on users  (cost=28.23..140.07 rows=30 width=468)
--          Recheck Cond: (settings @> "{"twitter": "john30000"}"::jsonb)
--          ->  Bitmap Index Scan on settings_index  (cost=0.00..28.23 rows=30 width=0)
--                Index Cond: (settings @> "{"twitter": "john30000"}"::jsonb)

EXPLAIN
SELECT *
FROM users
WHERE preferences->>"twitter" = "john30000" LIMIT 1;

--                                QUERY PLAN
-- -------------------------------------------------------------------------
--  Limit  (cost=0.00..25.23 rows=1 width=468)
--    ->  Seq Scan on users  (cost=0.00..3784.00 rows=150 width=468)
--          Filter: ((preferences ->> "twitter"::text) = "john30000"::text)

sql
EXPLAIN
SELECT *
FROM users
WHERE settings->>"twitter" = "johndoe30000" LIMIT 1;

--                                QUERY PLAN
-- -------------------------------------------------------------------------
--  Limit  (cost=0.00..25.23 rows=1 width=468)
--    ->  Seq Scan on users  (cost=0.00..3784.00 rows=150 width=468)
--          Filter: ((settings ->> "twitter"::text) = "johndoe30000"::text)
-- (3 rows)

sql
BEGIN;
ALTER TABLE users ADD COLUMN preferences_jsonb jsonb DEFAULT "{}";
UPDATE users set preferences_jsonb = preferences::jsonb;
ALTER TABLE users ALTER COLUMN preferences_jsonb SET NOT NULL;
ALTER TABLE users RENAME COLUMN preferences TO preferences_json;
ALTER TABLE users RENAME COLUMN preferences_jsonb TO preferences;

-- Don"t remove the column until you"re sure everything is working.
-- ALTER TABLE users DROP COLUMN preferences_json;

COMMIT;

ruby
# 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

ruby
# 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 user_prefs_newsletter_index ON users ((preferences->>"newsletter"))
    SQL
  end
end

ruby
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

ruby
# 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

ruby
class User < ActiveRecord::Base
  serialize :preferences, HashSerializer
  store_accessor :preferences, :blog, :github, :twitter
end

ruby
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

ruby
# preferences->newsletter = true
User.where("preferences @> ?", {newsletter: true}.to_json)

ruby
# preferences->interests = ["ruby", "javascript", "python"]
User.where("preferences -> "interests" ? :language", language: "ruby")

ruby
CREATE INDEX preferences_interests_on_users ON users USING GIN ((preferences->"interests"))

ruby
# preferences->twitter AND preferences->github
User.where("preferences ?& array[:keys]", keys: ["twitter", "github"])

ruby
# preferences->twitter OR preferences->github
User.where("preferences ?| array[:keys]", keys: ["twitter", "github"])

ruby
# preferences->state = "SP" AND preferences->city = "S?o Paulo"
User.where("preferences @> ?", {city: "San Francisco", state: "CA"}.to_json)

ruby
# This doesn"t exist, but it would be nice to have it!
add_index :users, "(settings->>"github")", raw: true