sqlite_json

The sqlite_json_ops extension adds support to Sequel’s DSL to make it easier to call SQLite JSON functions and operators (added first in SQLite 3.38.0). It also supports the SQLite JSONB functions added in SQLite 3.45.0.

To load the extension:

Sequel.extension :sqlite_json_ops

This extension works by calling methods on Sequel::SQLite::JSONOp and Sequel::SQLite::JSONBOp objects, which you can create using Sequel.sqlite_json_op and Sequel.sqlite_jsonb_op:

j = Sequel.sqlite_json_op(:json_column)
jb = Sequel.sqlite_jsonb_op(:jsonb_column)

Also, on most Sequel expression objects, you can call the sqlite_json_op or sqlite_jsonb_op method to create a Sequel::SQLite::JSONOp or Sequel::SQLite::JSONBOp object:

j = Sequel[:json_column].sqlite_json_op
jb = Sequel[:jsonb_column].sqlite_jsonb_op

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#sqlite_json_op: or Symbol#sqlite_jsonb_op:

j = :json_column.sqlite_json_op
jb = :json_column.sqlite_jsonb_op

The following methods are available for Sequel::SQLite::JSONOp instances:

j[1]                     # (json_column ->> 1)
j.get(1)                 # (json_column ->> 1)
j.get_json(1)            # (json_column -> 1)
j.extract('$.a')         # json_extract(json_column, '$.a')
jb.extract('$.a')        # jsonb_extract(jsonb_column, '$.a')

j.array_length           # json_array_length(json_column)
j.type                   # json_type(json_column)
j.valid                  # json_valid(json_column)
jb.json                  # json(jsonb_column)
j.jsonb                  # jsonb(json_column)

j.insert('$.a', 1)       # json_insert(json_column, '$.a', 1)
j.set('$.a', 1)          # json_set(json_column, '$.a', 1)
j.replace('$.a', 1)      # json_replace(json_column, '$.a', 1)
j.remove('$.a')          # json_remove(json_column, '$.a')
j.patch('{"a":2}')       # json_patch(json_column, '{"a":2}')

jb.insert('$.a', 1)      # jsonb_insert(jsonb_column, '$.a', 1)
jb.set('$.a', 1)         # jsonb_set(jsonb_column, '$.a', 1)
jb.replace('$.a', 1)     # jsonb_replace(jsonb_column, '$.a', 1)
jb.remove('$.a')         # jsonb_remove(jsonb_column, '$.a')
jb.patch('{"a":2}')      # jsonb_patch(jsonb_column, '{"a":2}')

j.each                   # json_each(json_column)
j.tree                   # json_tree(json_column)

sqlite_json_ops.rb