pg_json.rb

Path: lib/sequel/extensions/pg_json.rb
Last Update: Fri Jul 13 14:52:09 +0000 2012

The pg_json extension adds support for Sequel to handle PostgreSQL‘s json type. It is slightly more strict than the PostgreSQL json type in that the object returned must be an array or object (PostgreSQL‘s json type considers plain numbers and strings as valid). This is because Sequel relies completely on the ruby JSON library for parsing, and ruby‘s JSON library does not accept the values.

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. 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:

  array.pg_json
  hash.pg_json

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

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

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.extend Sequel::Postgres::JSONDatabaseMethods

If you are not using the native postgres adapter, you probably also want to use the typecast_on_load plugin in the model, and set it to typecast the json column(s) on load.

The extension is designed to be used with the json type natively supported by PostgreSQL 9.2+. There was also a PostgreSQL extension released that allowed the json type to be used on PostgreSQL 9.1. To make this extension support that type in the native adapter, do the following after loading this extension:

  Sequel::Postgres::PG_NAMED_TYPES = {} unless defined?(Sequel::Postgres::PG_NAMED_TYPES)
  Sequel::Postgres::PG_NAMED_TYPES[:json] = Sequel::Postgres::PG_TYPES[114]

This extension requires both the json and delegate libraries.

Required files

delegate   json  

[Validate]