Method: Sequel::Postgres::JSONBaseOp#table

Defined in:

lib/sequel/extensions/pg_json_ops.rb

#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.

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

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