pg_json_ops.rb

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, as well as additional functions and operators added in later versions.

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 Symbol#pg_json or Symbol#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)

j.set_lax(%w'0 a', :h, false, 'raise_exception')
# jsonb_set_lax(jsonb_column, ARRAY['0','a'], h, false, 'raise_exception')

On PostgreSQL 12+ SQL/JSON path functions and operators are supported:

j.path_exists('$.foo')      # (jsonb_column @? '$.foo')
j.path_match('$.foo')       # (jsonb_column @@ '$.foo')

j.path_exists!('$.foo')     # jsonb_path_exists(jsonb_column, '$.foo')
j.path_match!('$.foo')      # jsonb_path_match(jsonb_column, '$.foo')
j.path_query('$.foo')       # jsonb_path_query(jsonb_column, '$.foo')
j.path_query_array('$.foo') # jsonb_path_query_array(jsonb_column, '$.foo')
j.path_query_first('$.foo') # jsonb_path_query_first(jsonb_column, '$.foo')

For the PostgreSQL 12+ SQL/JSON path functions, one argument is required (path) and two more arguments are optional (vars and silent). path specifies the JSON path. vars specifies a hash or a string in JSON format of named variables to be substituted in path. silent specifies whether errors are suppressed. By default, errors are not suppressed.

On PostgreSQL 13+ timezone-aware SQL/JSON path functions and operators are supported:

j.path_exists_tz!('$.foo')     # jsonb_path_exists_tz(jsonb_column, '$.foo')
j.path_match_tz!('$.foo')      # jsonb_path_match_tz(jsonb_column, '$.foo')
j.path_query_tz('$.foo')       # jsonb_path_query_tz(jsonb_column, '$.foo')
j.path_query_array_tz('$.foo') # jsonb_path_query_array_tz(jsonb_column, '$.foo')
j.path_query_first_tz('$.foo') # jsonb_path_query_first_tz(jsonb_column, '$.foo')

On PostgreSQL 14+, The JSONB [] method will use subscripts instead of being the same as get, if the value being wrapped is an identifer:

Sequel.pg_jsonb_op(:jsonb_column)[1]       # jsonb_column[1]
Sequel.pg_jsonb_op(:jsonb_column)[1][2]    # jsonb_column[1][2]
Sequel.pg_jsonb_op(Sequel[:j][:b])[1]      # j.b[1]

This support allows you to use JSONB subscripts in UPDATE statements to update only part of a column:

c = Sequel.pg_jsonb_op(:c)
DB[:t].update(c['key1'] => '1', c['key2'] => '"a"')
#  UPDATE "t" SET "c"['key1'] = '1', "c"['key2'] = '"a"'

Note that you have to provide the value of a JSONB subscript as a JSONB value, so this will update key1 to use the number 1, and key2 to use the string a. For this reason it may be simpler to use to_json:

c = Sequel.pg_jsonb_op(:c)
DB[:t].update(c['key1'] => 1.to_json, c['key2'] => "a".to_json)

On PostgreSQL 16+, the IS [NOT] JSON operator is supported:

j.is_json                              # j IS JSON
j.is_json(type: :object)               # j IS JSON OBJECT
j.is_json(type: :object, unique: true) # j IS JSON OBJECT WITH UNIQUE
j.is_not_json                          # j IS NOT JSON
j.is_not_json(type: :array)            # j IS NOT JSON ARRAY
j.is_not_json(unique: true)            # j IS NOT JSON WITH UNIQUE

On PostgreSQL 17+, the additional JSON functions are supported (see method documentation for additional options):

j.exists('$.foo')     # json_exists(jsonb_column, '$.foo')
j.value('$.foo')      # json_value(jsonb_column, '$.foo')
j.query('$.foo')      # json_query(jsonb_column, '$.foo')

j.exists('$.foo', passing: {a: 1}) # json_exists(jsonb_column, '$.foo' PASSING 1 AS a)
j.value('$.foo', returning: Time)  # json_value(jsonb_column, '$.foo' RETURNING timestamp)
j.query('$.foo', wrapper: true)    # json_query(jsonb_column, '$.foo' WITH WRAPPER)

j.table('$.foo') do
   String :bar
   Integer :baz
end
# json_table("jsonb_column", '$.foo' COLUMNS("bar" text, "baz" integer))

j.table('$.foo', passing: {a: 1}) do
   ordinality :id
   String :bar, format: :json, on_error: :empty_object
   nested '$.baz' do
     Integer :q, path: '$.quux', on_empty: :error
   end
   exists :x, Date, on_error: false
end
# json_table(jsonb_column, '$.foo' PASSING 1 AS a COLUMNS(
#   "id" FOR ORDINALITY,
#   "bar" text FORMAT JSON EMPTY OBJECT ON ERROR,
#   NESTED '$.baz' COLUMNS(
#     "q" integer PATH '$.quux' ERROR ON EMPTY
#   ),
#   "d" date EXISTS FALSE ON ERROR
# ))

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.