Web Development

JSONB, JSON and things like HSTORE are good for when you can’t use a normalized data model, such as when the data model changes rapidly and is user defined.

Of course, the main rule is that if you can model them relationally, you should. And if you can choose between the options, JSON, JSONB and HSTORE, the priority should be JSONB, JSON and then HSTORE.

Here’s a comprehensive resource on the matter: When to use unstructured datatypes in Postgres–Hstore vs. JSON vs. JSONB

In most cases JSONB is likely what you want when looking for a NoSQL, schema-less, datatype. Hstore and JSON can have their place as well but it’s less common. More broadly, JSONB isn’t always a fit in every data model. Where you can normalize there are benefits, but if you do have a schema that has a large number of optional columns (such as with event data) or the schema differs based on tenant id then JSONB can be a great fit. In general you want:

* JSONB – In most cases
* JSON – If you’re just processing logs, don’t often need to query, and use as more of an audit trail
* hstore – Can work fine for text based key-value looks, but in general JSONB can still work great here

Here’s a migration sample for Rails for JSONB:

class CreateAccountSubscriptions < ActiveRecord::Migration
  def change
    create_table :account_subscriptions do |t|
      t.date :start_date
      t.date :renewal_date
      t.decimal :amount,precision: 10, scale: 2, default: 0
      t.string :billing_cycle
      t.string :name
      t.json :details
      t.string :payment_type
    end
  end
end
### Migration...
== 20180405011531 CreateAccountSubscriptions: migrating =======================
-- create_table(:account_subscriptions)
   -> 0.0408s
== 20180405011531 CreateAccountSubscriptions: migrated (0.0409s) ==============

Here’s a migration sample for Rails for JSON:

class CreateAccountSubscriptions < ActiveRecord::Migration
  def change
    create_table :account_subscriptions do |t|
      t.date :start_date
      t.date :renewal_date
      t.decimal :amount,precision: 10, scale: 2, default: 0
      t.string :billing_cycle
      t.string :name
      t.jsonb :details
      t.string :payment_type
    end
  end
end
### Migration...
== 20180405011531 CreateAccountSubscriptions: migrating =======================
-- create_table(:account_subscriptions)
   -> 0.0420s
== 20180405011531 CreateAccountSubscriptions: migrated (0.0421s) ==============

Here’s a migration sample for Rails for HSTORE:

class CreateAccountSubscriptions < ActiveRecord::Migration
  def change
    enable_extension 'hstore' unless extension_enabled?('hstore')
    create_table :account_subscriptions do |t|
      t.date :start_date
      t.date :renewal_date
      t.decimal :amount,precision: 10, scale: 2, default: 0
      t.string :billing_cycle
      t.string :name
      t.hstore :details
      t.string :payment_type
    end
  end
end
### Migration...
-- extension_enabled?("hstore")
   -> 0.1217s
-- enable_extension("hstore")
   -> 0.3896s
-- create_table(:account_subscriptions)
   -> 0.0880s
== 20180405011531 CreateAccountSubscriptions: migrated (0.5996s) ==============