pg_json.rb

The pg_json extension adds support for Sequel to handle PostgreSQL’s json and jsonb types. By default, it wraps JSON arrays and JSON objects with ruby array-like and hash-like objects. If you would like to wrap JSON primitives (numbers, strings, null, true, and false), you need to use the wrap_json_primitives setter:

DB.extension :pg_json
DB.wrap_json_primitives = true

Note that wrapping JSON primitives changes the behavior for JSON false and null values. Because only false and nil in Ruby are considered falsey, wrapping these objects results in unexpected behavior if you use the values directly in conditionals:

if DB[:table].get(:json_column)
  # called if the value of json_column is null/false
  # if you are wrapping primitives
end

To extract the Ruby primitive object from the wrapper object, you can use __getobj__ (this comes from Ruby’s delegate library).

To wrap an existing Ruby array, hash, string, integer, float, nil, true, or false, use Sequel.pg_json_wrap or Sequel.pg_jsonb_wrap:

Sequel.pg_json_wrap(object)  # json type
Sequel.pg_jsonb_wrap(object) # jsonb type

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

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

Note that the pg_json_wrap and pg_jsonb_wrap methods only handle Ruby primitives, they do not handle already wrapped objects.

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 the pg_json and pg_jsonb methods directly on Array or Hash:

array.pg_json  # json type
array.pg_jsonb # jsonb type

hash.pg_json   # json type
hash.pg_jsonb  # jsonb type

Model classes that use json or jsonb columns will have typecasting automatically setup, so you can assign Ruby primitives to model columns and have the wrapped objects automatically created. However, for backwards compatibility, passing a string object will parse the string as JSON, not create a JSON string object.

obj = Model.new
obj.json_column = {'a'=>'b'}
obj.json_column.class
# => Sequel::Postgres::JSONHash
obj.json_column['a']
# => 'b'

obj.json_column = '{"a": "b"}'
obj.json_column.class
# => Sequel::Postgres::JSONHash
obj.json_column['a']
# => 'b'

You can change the handling of string typecasting by using typecast_json_strings:

DB.typecast_json_strings = true
obj.json_column = '{"a": "b"}'
obj.json_column.class
# => Sequel::Postgres::JSONString
obj.json_column
# => '{"a": "b"}'

Note that nil values are never automatically wrapped:

obj.json_column = nil
obj.json_column.class
# => NilClass
obj.json_column
# => nil

If you want to set a JSON null value when using a model, you must wrap it explicitly:

obj.json_column = Sequel.pg_json_wrap(nil)
obj.json_column.class
# => Sequel::Postgres::JSONNull
obj.json_column
# => nil

To use this extension, load it into the Database instance:

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[] or jsonb[] types, 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. However, you can override Sequel.parse_json, Sequel.object_to_json, and Sequel.json_parser_error_class to use an alternative JSON implementation.

Related modules: Sequel::Postgres::JSONDatabaseMethods