Last Update: 2016-09-13 13:21:30 -0700

The pg_json_ops extension adds support to Sequel's DSL to make it easier to call PostgreSQL JSON functions and operators (added first in PostgreSQL 9.3). It also supports the JSONB functions and operators added in PostgreSQL 9.4).

To load the extension:

Sequel.extension :pg_json_ops

The most common usage is passing an expression to Sequel.pg_json_op or Sequel.pg_jsonb_op:

j = Sequel.pg_json_op(:json_column)
jb = Sequel.pg_jsonb_op(:jsonb_column)

If you have also loaded the pg_json extension, you can use Sequel.pg_json or Sequel.pg_jsonb as well:

j = Sequel.pg_json(:json_column)
jb = Sequel.pg_jsonb(:jsonb_column)

Also, on most Sequel expression objects, you can call the pg_json or pg_jsonb # method:

j = Sequel[:json_column].pg_json
jb = Sequel[:jsonb_column].pg_jsonb

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 Sequel::Postgres::JSONOpMethods#pg_json or Sequel::Postgres::JSONOpMethods#pg_jsonb:

j = :json_column.pg_json
jb = :jsonb_column.pg_jsonb

This creates a Sequel::Postgres::JSONOp or Sequel::Postgres::JSONBOp object that can be used for easier querying. The following methods are available for both JSONOp and JSONBOp instances:

j[1]                     # (json_column -> 1)
j[%w'a b']               # (json_column #> ARRAY['a','b'])
j.get_text(1)            # (json_column ->> 1)
j.get_text(%w'a b')      # (json_column #>> ARRAY['a','b'])
j.extract('a', 'b')      # json_extract_path(json_column, 'a', 'b')
j.extract_text('a', 'b') # json_extract_path_text(json_column, 'a', 'b')

j.array_length           # json_array_length(json_column)
j.array_elements         # json_array_elements(json_column)
j.array_elements_text    # json_array_elements_text(json_column)
j.each                   # json_each(json_column)
j.each_text              # json_each_text(json_column)
j.keys                   # json_object_keys(json_column)
j.typeof                 # json_typeof(json_column)
j.strip_nulls            # json_strip_nulls(json_column)

j.populate(:a)           # json_populate_record(:a, json_column)
j.populate_set(:a)       # json_populate_recordset(:a, json_column)
j.to_record              # json_to_record(json_column)
j.to_recordset           # json_to_recordset(json_column)

There are additional methods are are only supported on JSONBOp instances:

j - 1                     # (jsonb_column - 1)
j.concat(:h)              # (jsonb_column || h)
j.contain_all(:a)         # (jsonb_column ?& a)
j.contain_any(:a)         # (jsonb_column ?| a)
j.contains(:h)            # (jsonb_column @> h)
j.contained_by(:h)        # (jsonb_column <@ h)
j.delete_path(%w'0 a')    # (jsonb_column #- ARRAY['0','a'])
j.has_key?('a')           # (jsonb_column ? 'a')
j.insert(%w'0 a', 'a'=>1) # jsonb_insert(jsonb_column, ARRAY[0, 'a'], '{"a":1}'::jsonb, false)
j.pretty                  # jsonb_pretty(jsonb_column)
j.set(%w'0 a', :h)        # jsonb_set(jsonb_column, ARRAY['0','a'], h, true)

If you are also using the pg_json extension, you should load it before loading this extension. Doing so will allow you to use the op method on JSONHash, JSONHarray, JSONBHash, and JSONBArray, allowing you to perform json/jsonb operations on json/jsonb literals.

In order to get the automatic conversion from a ruby array to a PostgreSQL array (as shown in the [] and get_text examples above), you need to load the pg_array extension.

Related modules: Sequel::Postgres::JSONBaseOp, Sequel::Postgres::JSONOp, Sequel::Postgres::JSONBOp