Last Update: 2016-02-21 12:31:57 -0800

The pg_json extension adds support for Sequel to handle PostgreSQL's json and jsonb types. It is slightly more strict than the PostgreSQL json types in that the object returned should be an array or object (PostgreSQL's json type considers plain numbers strings, true, false, and null as valid). Sequel will work with PostgreSQL json values that are not arrays or objects, but support is fairly limited and the values do not roundtrip.

This extension integrates with Sequel's native postgres adapter, so that when json fields are retrieved, they are parsed and returned as instances of Sequel::Postgres::JSONArray or Sequel::Postgres::JSONHash (or JSONBArray or JSONBHash for jsonb columns). JSONArray and JSONHash are DelegateClasses of Array and Hash, so they mostly act the same, but not completely (json_array.is_a?(Array) is false). If you want the actual array for a JSONArray, call JSONArray#to_a. If you want the actual hash for a JSONHash, call JSONHash#to_hash. This is done so that Sequel does not treat JSONArray and JSONHash like Array and Hash by default, which would cause issues.

To turn an existing Array or Hash into a JSONArray or JSONHash, use Sequel.pg_json:

Sequel.pg_json(array) # or Sequel.pg_jsonb(array) for jsonb type
Sequel.pg_json(hash)  # or Sequel.pg_jsonb(hash) for jsonb type

If you have loaded the core_extensions extension, or you have loaded the core_refinements extension and have activated refinements for the file, you can also use Array#pg_json and Hash#pg_json:

array.pg_json # or array.pg_jsonb for jsonb type
hash.pg_json  # or hash.pg_jsonb for jsonb type

So if you want to insert an array or hash into an json database column:

DB[:table].insert(:column=>Sequel.pg_json([1, 2, 3]))
DB[:table].insert(:column=>Sequel.pg_json({'a'=>1, 'b'=>2}))

If you would like to use PostgreSQL json columns in your model objects, you probably want to modify the schema parsing/typecasting so that it recognizes and correctly handles the json type, which you can do by:

DB.extension :pg_json

See the schema modification guide for details on using json columns in CREATE/ALTER TABLE statements.

This extension integrates with the pg_array extension. If you plan to use the json[] type, load the pg_array extension before the pg_json extension:

DB.extension :pg_array, :pg_json

Note that when accessing json hashes, you should always use strings for keys. Attempting to use other values (such as symbols) will not work correctly.

This extension requires both the json and delegate libraries.

Related modules: Sequel::Postgres::JSONArrayBase, Sequel::Postgres::JSONArray, Sequel::Postgres::JSONArray, Sequel::Postgres::JSONBArray, Sequel::Postgres::JSONHashBase, Sequel::Postgres::JSONHash, Sequel::Postgres::JSONBHash, Sequel::Postgres::JSONDatabaseMethods

Required files

  1. delegate
  2. json
  3. adapters/utils/pg_types