Class: Sequel::Postgres::JSONBaseOp

Inherits:
SQL::Wrapper show all
Defined in:
lib/sequel/extensions/pg_json_ops.rb

Overview

The JSONBaseOp class is a simple container for a single object that defines methods that yield Sequel expression objects representing PostgreSQL json operators and functions.

In the method documentation examples, assume that:

json_op = Sequel.pg_json(:json)

Direct Known Subclasses

JSONBOp, JSONOp

Constant Summary collapse

GET = 
["(".freeze, " -> ".freeze, ")".freeze].freeze
GET_TEXT = 
["(".freeze, " ->> ".freeze, ")".freeze].freeze
GET_PATH = 
["(".freeze, " #> ".freeze, ")".freeze].freeze
GET_PATH_TEXT = 
["(".freeze, " #>> ".freeze, ")".freeze].freeze
IS_JSON = 
["(".freeze, " IS JSON".freeze, "".freeze, ")".freeze].freeze
IS_NOT_JSON = 
["(".freeze, " IS NOT JSON".freeze, "".freeze, ")".freeze].freeze
EMPTY_STRING = 
Sequel::LiteralString.new('').freeze
WITH_UNIQUE = 
Sequel::LiteralString.new(' WITH UNIQUE').freeze
IS_JSON_MAP = 
{
  nil => EMPTY_STRING,
  :value => Sequel::LiteralString.new(' VALUE').freeze,
  :scalar => Sequel::LiteralString.new(' SCALAR').freeze,
  :object => Sequel::LiteralString.new(' OBJECT').freeze,
  :array => Sequel::LiteralString.new(' ARRAY').freeze
}.freeze

Instance Method Summary collapse

Methods inherited from SQL::Wrapper

#initialize

Methods included from SQL::IsDistinctFrom::Methods

#is_distinct_from

Methods included from SQLite::JSONOpMethods

#sqlite_json_op, #sqlite_jsonb_op

Methods included from HStoreOpMethods

#hstore

Methods included from RangeOpMethods

#pg_range

Methods included from ArrayOpMethods

#pg_array

Methods included from JSONOpMethods

#pg_json, #pg_jsonb

Methods included from InetOpMethods

#pg_inet

Methods included from PGRowOp::ExpressionMethods

#pg_row

Methods included from SQL::SubscriptMethods

#sql_subscript

Methods included from SQL::StringMethods

#escaped_ilike, #escaped_like, #ilike, #like

Methods included from SQL::PatternMatchMethods

#!~, #=~

Methods included from SQL::OrderMethods

#asc, #desc

Methods included from SQL::NumericMethods

#+, #coerce

Methods included from SQL::ComplexExpressionMethods

#sql_boolean, #sql_number, #sql_string

Methods included from SQL::CastMethods

#cast, #cast_numeric, #cast_string

Methods included from SQL::BooleanMethods

#~

Methods included from SQL::AliasMethods

#as

Methods inherited from SQL::Expression

#==, attr_reader, #clone, #eql?, #hash, inherited, #inspect

Constructor Details

This class inherits a constructor from Sequel::SQL::Wrapper

Instance Method Details

#[](key) ⇒ Object Also known as: get

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'])



220
221
222
223
224
225
226
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 220

def [](key)
  if is_array?(key)
    json_op(GET_PATH, wrap_array(key))
  else
    json_op(GET, key)
  end
end

#array_elements ⇒ Object

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

json_op.array_elements # json_array_elements(json)



232
233
234
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 232

def array_elements
  function(:array_elements)
end

#array_elements_text ⇒ Object

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

json_op.array_elements_text # json_array_elements_text(json)



239
240
241
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 239

def array_elements_text
  function(:array_elements_text)
end

#array_length ⇒ Object

Get the length of the outermost json array.

json_op.array_length # json_array_length(json)



246
247
248
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 246

def array_length
  Sequel::SQL::NumericExpression.new(:NOOP, function(:array_length))
end

#each ⇒ Object

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

json_op.each # json_each(json)



254
255
256
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 254

def each
  function(:each)
end

#each_text ⇒ Object

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

json_op.each_text # json_each_text(json)



262
263
264
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 262

def each_text
  function(:each_text)
end

#exists(path, opts = OPTS) ⇒ Object

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

:on_error

How to handle errors when evaluating the JSON path expression.

true

Return true

false

Return false (default behavior)

:null

Return nil

:error

raise a DatabaseError

:passing

Variables to pass to the JSON path expression. Keys are variable names, values are the values of the variable.

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)



280
281
282
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 280

def exists(path, opts=OPTS)
  Sequel::SQL::BooleanExpression.new(:NOOP, JSONExistsOp.new(self, path, opts))
end

#extract(*a) ⇒ Object

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')



288
289
290
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 288

def extract(*a)
  self.class.new(function(:extract_path, *a))
end

#extract_text(*a) ⇒ Object

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')



296
297
298
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 296

def extract_text(*a)
  Sequel::SQL::StringExpression.new(:NOOP, function(:extract_path_text, *a))
end

#get_text(key) ⇒ Object

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'])



306
307
308
309
310
311
312
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 306

def get_text(key)
  if is_array?(key)
    json_op(GET_PATH_TEXT, wrap_array(key))
  else
    json_op(GET_TEXT, key)
  end
end

#is_json(opts = OPTS) ⇒ Object

Return whether the json object can be parsed as JSON.

Options:

:type

Check whether the json object can be parsed as a specific type of JSON (:value, :scalar, :object, :array).

:unique

Check JSON objects for unique keys.

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



324
325
326
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 324

def is_json(opts=OPTS)
  _is_json(IS_JSON, opts)
end

#is_not_json(opts = OPTS) ⇒ Object

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



334
335
336
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 334

def is_not_json(opts=OPTS)
  _is_json(IS_NOT_JSON, opts)
end

#keys ⇒ Object

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

json_op.keys # json_object_keys(json)



341
342
343
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 341

def keys
  function(:object_keys)
end

#populate(arg) ⇒ Object

Expands the given argument using the columns in the json.

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



348
349
350
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 348

def populate(arg)
  SQL::Function.new(function_name(:populate_record), arg, self)
end

#populate_set(arg) ⇒ Object

Expands the given argument using the columns in the json.

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



355
356
357
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 355

def populate_set(arg)
  SQL::Function.new(function_name(:populate_recordset), arg, self)
end

#query(path, opts = OPTS) ⇒ Object

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

:on_empty

How to handle case where path expression yields an empty set. Uses same values as :on_error option.

:on_error

How to handle errors when evaluating the JSON path expression:

:null

Return nil (default)

:empty_array

Return an empty array

:empty_object

Return an empty object

:error

raise a DatabaseError

any other value

used as default value

:passing

Variables to pass to the JSON path expression. Keys are variable names, values are the values of the variable.

:returning

The data type to return (jsonb by default)

:wrapper

How to wrap returned values:

true, :unconditional

Always wrap returning values in an array

:conditional

Only wrap multiple return values in an array

:omit_quotes

Do not wrap scalar strings in quotes

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)



384
385
386
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 384

def query(path, opts=OPTS)
  self.class.new(JSONQueryOp.new(self, path, opts))
end

#strip_nulls(opts = OPTS) ⇒ Object

Returns a json value stripped of all internal null values. Options:

:in_arrays

Whether to strip null values in JSON arrays

json_op.strip_nulls                   # json_strip_nulls(json)
json_op.strip_nulls(in_arrays: true)  # json_strip_nulls(json, true)
json_op.strip_nulls(in_arrays: false) # json_strip_nulls(json, false)



395
396
397
398
399
400
401
402
403
404
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 395

def strip_nulls(opts=OPTS)
  in_arrays = opts[:in_arrays]
  f = if in_arrays.nil?
    function(:strip_nulls)
  else
    function(:strip_nulls, in_arrays)
  end

  self.class.new(f)
end

#table(path, opts = OPTS, &block) ⇒ Object

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:

:on_error

How to handle errors when evaluating the JSON path expression.

:empty_array

Return an empty array/result set

:error

raise a DatabaseError

:passing

Variables to pass to the JSON path expression. Keys are variable names, values are the values of the variable.

Inside the block, the following methods can be used:

ordinality(name)

Include a FOR ORDINALITY column, which operates similar to an autoincrementing primary key.

column(name, type, opts={})

Return a normal column that uses the given type.

exists(name, type, opts={})

Return a boolean column for whether the JSON path yields any values.

nested(path, &block)

Extract nested data from the result set at the given path. This block is treated the same as a json_table block, and arbitrary levels of nesting are supported.

The column method supports the following options:

:path

JSON path to the object (the default is $.NAME, where NAME is the name of the column).

:format

Set to :json to use FORMAT JSON, when you expect the value to be a valid JSON object.

:on_empty, :on_error

How to handle case where JSON path evaluation is empty or results in an error. Values supported are:

:empty_array

Return empty array (requires format: :json)

:empty_object

Return empty object (requires format: :json)

:error

Raise a DatabaseError

:null

Return nil (NULL)

:wrapper

How to wrap returned values:

true, :unconditional

Always wrap returning values in an array

:conditional

Only wrap multiple return values in an array

:keep_quotes

Wrap scalar strings in quotes

:omit_quotes

Do not wrap scalar strings in quotes

The exists method supports the following options:

:path

JSON path to the object (same as column option)

:on_error

How to handle case where JSON path evaluation results in an error. Values supported are:

:error

Raise a DatabaseError

true

Return true

false

Return false

:null

Return nil (NULL)

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.



468
469
470
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 468

def table(path, opts=OPTS, &block)
  JSONTableOp.new(self, path, opts, &block)
end

#to_record ⇒ Object

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)



476
477
478
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 476

def to_record
  function(:to_record)
end

#to_recordset ⇒ Object

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)



484
485
486
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 484

def to_recordset
  function(:to_recordset)
end

#typeof ⇒ Object

Returns the type of the outermost json value as text.

json_op.typeof # json_typeof(json)



491
492
493
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 491

def typeof
  function(:typeof)
end

#value(path = (no_args_given = true), opts = OPTS) ⇒ Object

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:

:on_empty

How to handle case where path expression yields an empty set. Uses same values as :on_error option.

:on_error

How to handle errors when evaluating the JSON path expression.

:null

Return nil (default)

:error

raise a DatabaseError

any other value

used as default value

:passing

Variables to pass to the JSON path expression. Keys are variable names, values are the values of the variable.

:returning

The data type to return (text by default)

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)



514
515
516
517
518
519
520
521
 
# File 'lib/sequel/extensions/pg_json_ops.rb', line 514

def value(path=(no_args_given = true), opts=OPTS)
  if no_args_given
    # Act as SQL::Wrapper#value
    super()
  else
    Sequel::SQL::StringExpression.new(:NOOP, JSONValueOp.new(self, path, opts))
  end
end