class Sequel::Postgres::JSONBaseOp

Get JSON array element or object field as json. If an array is given, gets the object at the specified path.

json_op[1] # (json -> 1)
json_op['a'] # (json -> 'a')
json_op[%w'a b'] # (json #> ARRAY['a', 'b'])

Returns a set of json values for the elements in the json array.

json_op.array_elements # json_array_elements(json)

Returns a set of text values for the elements in the json array.

json_op.array_elements_text # json_array_elements_text(json)

Get the length of the outermost json array.

json_op.array_length # json_array_length(json)

Returns a set of key and value pairs, where the keys are text and the values are JSON.

json_op.each # json_each(json)

Returns a set of key and value pairs, where the keys and values are both text.

json_op.each_text # json_each_text(json)

Return whether the given JSON path yields any items in the receiver. Options:

json_op.exists("$.a") # json_exists(json, '$.a')
json_op.exists("$.a", passing: {a: 1}) # json_exists(json, '$.a' PASSING 1 AS a)
json_op.exists("$.a", on_error: :error) # json_exists(json, '$.a' ERROR ON ERROR)

Returns a JSON value for the object at the given path.

json_op.extract('a') # json_extract_path(json, 'a')
json_op.extract('a', 'b') # json_extract_path(json, 'a', 'b')

Returns a text value for the object at the given path.

json_op.extract_text('a') # json_extract_path_text(json, 'a')
json_op.extract_text('a', 'b') # json_extract_path_text(json, 'a', 'b')

Get JSON array element or object field as text. If an array is given, gets the object at the specified path.

json_op.get_text(1) # (json ->> 1)
json_op.get_text('a') # (json ->> 'a')
json_op.get_text(%w'a b') # (json #>> ARRAY['a', 'b'])

Return whether the json object can be parsed as JSON.

Options:

json_op.is_json                 # json IS JSON
json_op.is_json(type: :object)  # json IS JSON OBJECT
json_op.is_json(unique: true)   # json IS JSON WITH UNIQUE

Return whether the json object cannot be parsed as JSON. The opposite of is_json. See is_json for options.

json_op.is_not_json                 # json IS NOT JSON
json_op.is_not_json(type: :object)  # json IS NOT JSON OBJECT
json_op.is_not_json(unique: true)   # json IS NOT JSON WITH UNIQUE

Returns a set of keys AS text in the json object.

json_op.keys # json_object_keys(json)

Expands the given argument using the columns in the json.

json_op.populate(arg) # json_populate_record(arg, json)

Expands the given argument using the columns in the json.

json_op.populate_set(arg) # json_populate_recordset(arg, json)

Return the result of applying the JSON path expression to the receiver, by default returning results as jsonb. Options:

json_op.query("$.a") # json_query(json, '$.a')
json_op.query("$.a", passing: {a: 1}) # json_query(json, '$.a' PASSING 1 AS a)
json_op.query("$.a", on_error: :empty_array) # json_query(json, '$.a' EMPTY ARRAY ON ERROR)
json_op.query("$.a", returning: Time) # json_query(json, '$.a' RETURNING timestamp)
json_op.query("$.a", on_empty: 2) # json_query(json, '$.a' DEFAULT 2 ON EMPTY)
json_op.query("$.a", wrapper: true) # json_query(json, '$.a' WITH WRAPPER)

Returns a json value stripped of all internal null values.

json_op.strip_nulls # json_strip_nulls(json)

Returns json_table SQL function expression, querying JSON data and returning the results as a relational view, which can be accessed similarly to a regular SQL table. This accepts a block that is handled in a similar manner to Database#create_table, though it operates differently.

Table level options:

Inside the block, the following methods can be used:

The column method supports the following options:

The exists method supports the following options:

Inside the block, methods for Ruby class names are also supported, allowing you to use syntax such as:

json_op.table('$.a') do
  String :b
  Integer :c, path: '$.d'
end

One difference between this method and Database#create_table is that method_missing is not supported inside the block. Use the column method for PostgreSQL types that are not mapped to Ruby classes.

Builds arbitrary record from json object. You need to define the structure of the record using as on the resulting object:

json_op.to_record.as(:x, [Sequel.lit('a integer'), Sequel.lit('b text')]) # json_to_record(json) AS x(a integer, b text)

Builds arbitrary set of records from json array of objects. You need to define the structure of the records using as on the resulting object:

json_op.to_recordset.as(:x, [Sequel.lit('a integer'), Sequel.lit('b text')]) # json_to_recordset(json) AS x(a integer, b text)

Returns the type of the outermost json value as text.

json_op.typeof # json_typeof(json)

If called without arguments, operates as SQL::Wrapper#value. Otherwise, return the result of applying the JSON path expression to the receiver, by default returning results as text. Options:

json_op.value("$.a") # json_value(json, '$.a')
json_op.value("$.a", passing: {a: 1}) # json_value(json, '$.a' PASSING 1 AS a)
json_op.value("$.a", on_error: :error) # json_value(json, '$.a' ERROR ON ERROR)
json_op.value("$.a", returning: Time) # json_value(json, '$.a' RETURNING timestamp)
json_op.value("$.a", on_empty: 2) # json_value(json, '$.a' DEFAULT 2 ON EMPTY)