Convert HTML docs to more streamlined TXT

[ai-pg] / full-docs / txt / datatype-json.txt

8.14. JSON Types #

   8.14.1. JSON Input and Output Syntax
   8.14.2. Designing JSON Documents
   8.14.3. jsonb Containment and Existence
   8.14.4. jsonb Indexing
   8.14.5. jsonb Subscripting
   8.14.6. Transforms
   8.14.7. jsonpath Type

   JSON data types are for storing JSON (JavaScript Object Notation) data,
   as specified in RFC 7159. Such data can also be stored as text, but the
   JSON data types have the advantage of enforcing that each stored value
   is valid according to the JSON rules. There are also assorted
   JSON-specific functions and operators available for data stored in
   these data types; see Section 9.16.

   PostgreSQL offers two types for storing JSON data: json and jsonb. To
   implement efficient query mechanisms for these data types, PostgreSQL
   also provides the jsonpath data type described in Section 8.14.7.

   The json and jsonb data types accept almost identical sets of values as
   input. The major practical difference is one of efficiency. The json
   data type stores an exact copy of the input text, which processing
   functions must reparse on each execution; while jsonb data is stored in
   a decomposed binary format that makes it slightly slower to input due
   to added conversion overhead, but significantly faster to process,
   since no reparsing is needed. jsonb also supports indexing, which can
   be a significant advantage.

   Because the json type stores an exact copy of the input text, it will
   preserve semantically-insignificant white space between tokens, as well
   as the order of keys within JSON objects. Also, if a JSON object within
   the value contains the same key more than once, all the key/value pairs
   are kept. (The processing functions consider the last value as the
   operative one.) By contrast, jsonb does not preserve white space, does
   not preserve the order of object keys, and does not keep duplicate
   object keys. If duplicate keys are specified in the input, only the
   last value is kept.

   In general, most applications should prefer to store JSON data as
   jsonb, unless there are quite specialized needs, such as legacy
   assumptions about ordering of object keys.

   RFC 7159 specifies that JSON strings should be encoded in UTF8. It is
   therefore not possible for the JSON types to conform rigidly to the
   JSON specification unless the database encoding is UTF8. Attempts to
   directly include characters that cannot be represented in the database
   encoding will fail; conversely, characters that can be represented in
   the database encoding but not in UTF8 will be allowed.

   RFC 7159 permits JSON strings to contain Unicode escape sequences
   denoted by \uXXXX. In the input function for the json type, Unicode
   escapes are allowed regardless of the database encoding, and are
   checked only for syntactic correctness (that is, that four hex digits
   follow \u). However, the input function for jsonb is stricter: it
   disallows Unicode escapes for characters that cannot be represented in
   the database encoding. The jsonb type also rejects \u0000 (because that
   cannot be represented in PostgreSQL's text type), and it insists that
   any use of Unicode surrogate pairs to designate characters outside the
   Unicode Basic Multilingual Plane be correct. Valid Unicode escapes are
   converted to the equivalent single character for storage; this includes
   folding surrogate pairs into a single character.

Note

   Many of the JSON processing functions described in Section 9.16 will
   convert Unicode escapes to regular characters, and will therefore throw
   the same types of errors just described even if their input is of type
   json not jsonb. The fact that the json input function does not make
   these checks may be considered a historical artifact, although it does
   allow for simple storage (without processing) of JSON Unicode escapes
   in a database encoding that does not support the represented
   characters.

   When converting textual JSON input into jsonb, the primitive types
   described by RFC 7159 are effectively mapped onto native PostgreSQL
   types, as shown in Table 8.23. Therefore, there are some minor
   additional constraints on what constitutes valid jsonb data that do not
   apply to the json type, nor to JSON in the abstract, corresponding to
   limits on what can be represented by the underlying data type. Notably,
   jsonb will reject numbers that are outside the range of the PostgreSQL
   numeric data type, while json will not. Such implementation-defined
   restrictions are permitted by RFC 7159. However, in practice such
   problems are far more likely to occur in other implementations, as it
   is common to represent JSON's number primitive type as IEEE 754 double
   precision floating point (which RFC 7159 explicitly anticipates and
   allows for). When using JSON as an interchange format with such
   systems, the danger of losing numeric precision compared to data
   originally stored by PostgreSQL should be considered.

   Conversely, as noted in the table there are some minor restrictions on
   the input format of JSON primitive types that do not apply to the
   corresponding PostgreSQL types.

   Table 8.23. JSON Primitive Types and Corresponding PostgreSQL Types
   JSON primitive type PostgreSQL type Notes
   string text \u0000 is disallowed, as are Unicode escapes representing
   characters not available in the database encoding
   number numeric NaN and infinity values are disallowed
   boolean boolean Only lowercase true and false spellings are accepted
   null (none) SQL NULL is a different concept

8.14.1. JSON Input and Output Syntax #

   The input/output syntax for the JSON data types is as specified in RFC
   7159.

   The following are all valid json (or jsonb) expressions:
-- Simple scalar/primitive value
-- Primitive values can be numbers, quoted strings, true, false, or null
SELECT '5'::json;

-- Array of zero or more elements (elements need not be of same type)
SELECT '[1, 2, "foo", null]'::json;

-- Object containing pairs of keys and values
-- Note that object keys must always be quoted strings
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- Arrays and objects can be nested arbitrarily
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

   As previously stated, when a JSON value is input and then printed
   without any additional processing, json outputs the same text that was
   input, while jsonb does not preserve semantically-insignificant details
   such as whitespace. For example, note the differences here:
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

   One semantically-insignificant detail worth noting is that in jsonb,
   numbers will be printed according to the behavior of the underlying
   numeric type. In practice this means that numbers entered with E
   notation will be printed without it, for example:
SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

   However, jsonb will preserve trailing fractional zeroes, as seen in
   this example, even though those are semantically insignificant for
   purposes such as equality checks.

   For the list of built-in functions and operators available for
   constructing and processing JSON values, see Section 9.16.

8.14.2. Designing JSON Documents #

   Representing data as JSON can be considerably more flexible than the
   traditional relational data model, which is compelling in environments
   where requirements are fluid. It is quite possible for both approaches
   to co-exist and complement each other within the same application.
   However, even for applications where maximal flexibility is desired, it
   is still recommended that JSON documents have a somewhat fixed
   structure. The structure is typically unenforced (though enforcing some
   business rules declaratively is possible), but having a predictable
   structure makes it easier to write queries that usefully summarize a
   set of “documents” (datums) in a table.

   JSON data is subject to the same concurrency-control considerations as
   any other data type when stored in a table. Although storing large
   documents is practicable, keep in mind that any update acquires a
   row-level lock on the whole row. Consider limiting JSON documents to a
   manageable size in order to decrease lock contention among updating
   transactions. Ideally, JSON documents should each represent an atomic
   datum that business rules dictate cannot reasonably be further
   subdivided into smaller datums that could be modified independently.

8.14.3. jsonb Containment and Existence #

   Testing containment is an important capability of jsonb. There is no
   parallel set of facilities for the json type. Containment tests whether
   one jsonb document has contained within it another one. These examples
   return true except as noted:
-- Simple scalar/primitive values contain only the identical value:
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- The array on the right side is contained within the one on the left:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

-- Order of array elements is not significant, so this is also true:
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

-- Duplicate array elements don't matter either:
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- The object with a single pair on the right side is contained
-- within the object on the left side:
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"v
ersion": 9.4}'::jsonb;

-- The array on the right side is not considered contained within the
-- array on the left, even though a similar array is nested within it:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- yields false

-- But with a layer of nesting, it is contained:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- Similarly, containment is not reported here:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- yields f
alse

-- A top-level key and an empty object is contained:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

   The general principle is that the contained object must match the
   containing object as to structure and data contents, possibly after
   discarding some non-matching array elements or object key/value pairs
   from the containing object. But remember that the order of array
   elements is not significant when doing a containment match, and
   duplicate array elements are effectively considered only once.

   As a special exception to the general principle that the structures
   must match, an array may contain a primitive value:
-- This array contains the primitive string value:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- This exception is not reciprocal -- non-containment is reported here:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- yields false

   jsonb also has an existence operator, which is a variation on the theme
   of containment: it tests whether a string (given as a text value)
   appears as an object key or array element at the top level of the jsonb
   value. These examples return true except as noted:
-- String exists as array element:
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- String exists as object key:
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

-- Object values are not considered:
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- yields false

-- As with containment, existence must match at the top level:
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false

-- A string is considered to exist if it matches a primitive JSON string:
SELECT '"foo"'::jsonb ? 'foo';

   JSON objects are better suited than arrays for testing containment or
   existence when there are many keys or elements involved, because unlike
   arrays they are internally optimized for searching, and do not need to
   be searched linearly.

Tip

   Because JSON containment is nested, an appropriate query can skip
   explicit selection of sub-objects. As an example, suppose that we have
   a doc column containing objects at the top level, with most objects
   containing tags fields that contain arrays of sub-objects. This query
   finds entries in which sub-objects containing both "term":"paris" and
   "term":"food" appear, while ignoring any such keys outside the tags
   array:
SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

   One could accomplish the same thing with, say,
SELECT doc->'site_name' FROM websites
  WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';

   but that approach is less flexible, and often less efficient as well.

   On the other hand, the JSON existence operator is not nested: it will
   only look for the specified key or array element at top level of the
   JSON value.

   The various containment and existence operators, along with all other
   JSON operators and functions are documented in Section 9.16.

8.14.4. jsonb Indexing #

   GIN indexes can be used to efficiently search for keys or key/value
   pairs occurring within a large number of jsonb documents (datums). Two
   GIN “operator classes” are provided, offering different performance and
   flexibility trade-offs.

   The default GIN operator class for jsonb supports queries with the
   key-exists operators ?, ?| and ?&, the containment operator @>, and the
   jsonpath match operators @? and @@. (For details of the semantics that
   these operators implement, see Table 9.48.) An example of creating an
   index with this operator class is:
CREATE INDEX idxgin ON api USING GIN (jdoc);

   The non-default GIN operator class jsonb_path_ops does not support the
   key-exists operators, but it does support @>, @? and @@. An example of
   creating an index with this operator class is:
CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);

   Consider the example of a table that stores JSON documents retrieved
   from a third-party web service, with a documented schema definition. A
   typical document is:
{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

   We store these documents in a table named api, in a jsonb column named
   jdoc. If a GIN index is created on this column, queries like the
   following can make use of the index:
-- Find documents in which the key "company" has value "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone
"}';

   However, the index could not be used for queries like the following,
   because though the operator ? is indexable, it is not applied directly
   to the indexed column jdoc:
-- Find documents in which the key "tags" contains key or array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

   Still, with appropriate use of expression indexes, the above query can
   use an index. If querying for particular items within the "tags" key is
   common, defining an index like this may be worthwhile:
CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

   Now, the WHERE clause jdoc -> 'tags' ? 'qui' will be recognized as an
   application of the indexable operator ? to the indexed expression jdoc
   -> 'tags'. (More information on expression indexes can be found in
   Section 11.7.)

   Another approach to querying is to exploit containment, for example:
-- Find documents in which the key "tags" contains array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

   A simple GIN index on the jdoc column can support this query. But note
   that such an index will store copies of every key and value in the jdoc
   column, whereas the expression index of the previous example stores
   only data found under the tags key. While the simple-index approach is
   far more flexible (since it supports queries about any key), targeted
   expression indexes are likely to be smaller and faster to search than a
   simple index.

   GIN indexes also support the @? and @@ operators, which perform
   jsonpath matching. Examples are
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui
")';

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';

   For these operators, a GIN index extracts clauses of the form
   accessors_chain == constant out of the jsonpath pattern, and does the
   index search based on the keys and values mentioned in these clauses.
   The accessors chain may include .key, [*], and [index] accessors. The
   jsonb_ops operator class also supports .* and .** accessors, but the
   jsonb_path_ops operator class does not.

   Although the jsonb_path_ops operator class supports only queries with
   the @>, @? and @@ operators, it has notable performance advantages over
   the default operator class jsonb_ops. A jsonb_path_ops index is usually
   much smaller than a jsonb_ops index over the same data, and the
   specificity of searches is better, particularly when queries contain
   keys that appear frequently in the data. Therefore search operations
   typically perform better than with the default operator class.

   The technical difference between a jsonb_ops and a jsonb_path_ops GIN
   index is that the former creates independent index items for each key
   and value in the data, while the latter creates index items only for
   each value in the data. ^[7] Basically, each jsonb_path_ops index item
   is a hash of the value and the key(s) leading to it; for example to
   index {"foo": {"bar": "baz"}}, a single index item would be created
   incorporating all three of foo, bar, and baz into the hash value. Thus
   a containment query looking for this structure would result in an
   extremely specific index search; but there is no way at all to find out
   whether foo appears as a key. On the other hand, a jsonb_ops index
   would create three index items representing foo, bar, and baz
   separately; then to do the containment query, it would look for rows
   containing all three of these items. While GIN indexes can perform such
   an AND search fairly efficiently, it will still be less specific and
   slower than the equivalent jsonb_path_ops search, especially if there
   are a very large number of rows containing any single one of the three
   index items.

   A disadvantage of the jsonb_path_ops approach is that it produces no
   index entries for JSON structures not containing any values, such as
   {"a": {}}. If a search for documents containing such a structure is
   requested, it will require a full-index scan, which is quite slow.
   jsonb_path_ops is therefore ill-suited for applications that often
   perform such searches.

   jsonb also supports btree and hash indexes. These are usually useful
   only if it's important to check equality of complete JSON documents.
   The btree ordering for jsonb datums is seldom of great interest, but
   for completeness it is:
Object > Array > Boolean > Number > String > null

Object with n pairs > object with n - 1 pairs

Array with n elements > array with n - 1 elements

   with the exception that (for historical reasons) an empty top level
   array sorts less than null. Objects with equal numbers of pairs are
   compared in the order:
key-1, value-1, key-2 ...

   Note that object keys are compared in their storage order; in
   particular, since shorter keys are stored before longer keys, this can
   lead to results that might be unintuitive, such as:
{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

   Similarly, arrays with equal numbers of elements are compared in the
   order:
element-1, element-2 ...

   Primitive JSON values are compared using the same comparison rules as
   for the underlying PostgreSQL data type. Strings are compared using the
   default database collation.

8.14.5. jsonb Subscripting #

   The jsonb data type supports array-style subscripting expressions to
   extract and modify elements. Nested values can be indicated by chaining
   subscripting expressions, following the same rules as the path argument
   in the jsonb_set function. If a jsonb value is an array, numeric
   subscripts start at zero, and negative integers count backwards from
   the last element of the array. Slice expressions are not supported. The
   result of a subscripting expression is always of the jsonb data type.

   UPDATE statements may use subscripting in the SET clause to modify
   jsonb values. Subscript paths must be traversable for all affected
   values insofar as they exist. For instance, the path val['a']['b']['c']
   can be traversed all the way to c if every val, val['a'], and
   val['a']['b'] is an object. If any val['a'] or val['a']['b'] is not
   defined, it will be created as an empty object and filled as necessary.
   However, if any val itself or one of the intermediary values is defined
   as a non-object such as a string, number, or jsonb null, traversal
   cannot proceed so an error is raised and the transaction aborted.

   An example of subscripting syntax:

-- Extract object value by key
SELECT ('{"a": 1}'::jsonb)['a'];

-- Extract nested object value by key path
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];

-- Extract array element by index
SELECT ('[1, "2", null]'::jsonb)[1];

-- Update object value by key. Note the quotes around '1': the assigned
-- value must be of the jsonb type as well
UPDATE table_name SET jsonb_field['key'] = '1';

-- This will raise an error if any record's jsonb_field['a']['b'] is something
-- other than an object. For example, the value {"a": 1} has a numeric value
-- of the key 'a'.
UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';

-- Filter records using a WHERE clause with subscripting. Since the result of
-- subscripting is jsonb, the value we compare it against must also be jsonb.
-- The double quotes make "value" also a valid jsonb string.
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';

   jsonb assignment via subscripting handles a few edge cases differently
   from jsonb_set. When a source jsonb value is NULL, assignment via
   subscripting will proceed as if it was an empty JSON value of the type
   (object or array) implied by the subscript key:
-- Where jsonb_field was NULL, it is now {"a": 1}
UPDATE table_name SET jsonb_field['a'] = '1';

-- Where jsonb_field was NULL, it is now [1]
UPDATE table_name SET jsonb_field[0] = '1';

   If an index is specified for an array containing too few elements, NULL
   elements will be appended until the index is reachable and the value
   can be set.
-- Where jsonb_field was [], it is now [null, null, 2];
-- where jsonb_field was [0], it is now [0, null, 2]
UPDATE table_name SET jsonb_field[2] = '2';

   A jsonb value will accept assignments to nonexistent subscript paths as
   long as the last existing element to be traversed is an object or
   array, as implied by the corresponding subscript (the element indicated
   by the last subscript in the path is not traversed and may be
   anything). Nested array and object structures will be created, and in
   the former case null-padded, as specified by the subscript path until
   the assigned value can be placed.
-- Where jsonb_field was {}, it is now {"a": [{"b": 1}]}
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';

-- Where jsonb_field was [], it is now [null, {"a": 1}]
UPDATE table_name SET jsonb_field[1]['a'] = '1';

8.14.6. Transforms #

   Additional extensions are available that implement transforms for the
   jsonb type for different procedural languages.

   The extensions for PL/Perl are called jsonb_plperl and jsonb_plperlu.
   If you use them, jsonb values are mapped to Perl arrays, hashes, and
   scalars, as appropriate.

   The extension for PL/Python is called jsonb_plpython3u. If you use it,
   jsonb values are mapped to Python dictionaries, lists, and scalars, as
   appropriate.

   Of these extensions, jsonb_plperl is considered “trusted”, that is, it
   can be installed by non-superusers who have CREATE privilege on the
   current database. The rest require superuser privilege to install.

8.14.7. jsonpath Type #

   The jsonpath type implements support for the SQL/JSON path language in
   PostgreSQL to efficiently query JSON data. It provides a binary
   representation of the parsed SQL/JSON path expression that specifies
   the items to be retrieved by the path engine from the JSON data for
   further processing with the SQL/JSON query functions.

   The semantics of SQL/JSON path predicates and operators generally
   follow SQL. At the same time, to provide a natural way of working with
   JSON data, SQL/JSON path syntax uses some JavaScript conventions:
     * Dot (.) is used for member access.
     * Square brackets ([]) are used for array access.
     * SQL/JSON arrays are 0-relative, unlike regular SQL arrays that
       start from 1.

   Numeric literals in SQL/JSON path expressions follow JavaScript rules,
   which are different from both SQL and JSON in some minor details. For
   example, SQL/JSON path allows .1 and 1., which are invalid in JSON.
   Non-decimal integer literals and underscore separators are supported,
   for example, 1_000_000, 0x1EEE_FFFF, 0o273, 0b100101. In SQL/JSON path
   (and in JavaScript, but not in SQL proper), there must not be an
   underscore separator directly after the radix prefix.

   An SQL/JSON path expression is typically written in an SQL query as an
   SQL character string literal, so it must be enclosed in single quotes,
   and any single quotes desired within the value must be doubled (see
   Section 4.1.2.1). Some forms of path expressions require string
   literals within them. These embedded string literals follow
   JavaScript/ECMAScript conventions: they must be surrounded by double
   quotes, and backslash escapes may be used within them to represent
   otherwise-hard-to-type characters. In particular, the way to write a
   double quote within an embedded string literal is \", and to write a
   backslash itself, you must write \\. Other special backslash sequences
   include those recognized in JavaScript strings: \b, \f, \n, \r, \t, \v
   for various ASCII control characters, \xNN for a character code written
   with only two hex digits, \uNNNN for a Unicode character identified by
   its 4-hex-digit code point, and \u{N...} for a Unicode character code
   point written with 1 to 6 hex digits.

   A path expression consists of a sequence of path elements, which can be
   any of the following:
     * Path literals of JSON primitive types: Unicode text, numeric, true,
       false, or null.
     * Path variables listed in Table 8.24.
     * Accessor operators listed in Table 8.25.
     * jsonpath operators and methods listed in Section 9.16.2.3.
     * Parentheses, which can be used to provide filter expressions or
       define the order of path evaluation.

   For details on using jsonpath expressions with SQL/JSON query
   functions, see Section 9.16.2.

   Table 8.24. jsonpath Variables
   Variable Description
   $ A variable representing the JSON value being queried (the context
   item).
   $varname A named variable. Its value can be set by the parameter vars
   of several JSON processing functions; see Table 9.51 for details.
   @ A variable representing the result of path evaluation in filter
   expressions.

   Table 8.25. jsonpath Accessors
   Accessor Operator Description

   .key

   ."$varname"

   Member accessor that returns an object member with the specified key.
   If the key name matches some named variable starting with $ or does not
   meet the JavaScript rules for an identifier, it must be enclosed in
   double quotes to make it a string literal.

   .*

   Wildcard member accessor that returns the values of all members located
   at the top level of the current object.

   .**

   Recursive wildcard member accessor that processes all levels of the
   JSON hierarchy of the current object and returns all the member values,
   regardless of their nesting level. This is a PostgreSQL extension of
   the SQL/JSON standard.

   .**{level}

   .**{start_level to end_level}

   Like .**, but selects only the specified levels of the JSON hierarchy.
   Nesting levels are specified as integers. Level zero corresponds to the
   current object. To access the lowest nesting level, you can use the
   last keyword. This is a PostgreSQL extension of the SQL/JSON standard.

   [subscript, ...]

   Array element accessor. subscript can be given in two forms: index or
   start_index to end_index. The first form returns a single array element
   by its index. The second form returns an array slice by the range of
   indexes, including the elements that correspond to the provided
   start_index and end_index.

   The specified index can be an integer, as well as an expression
   returning a single numeric value, which is automatically cast to
   integer. Index zero corresponds to the first array element. You can
   also use the last keyword to denote the last array element, which is
   useful for handling arrays of unknown length.

   [*]

   Wildcard array element accessor that returns all array elements.

   ^[7] For this purpose, the term “value” includes array elements, though
   JSON terminology sometimes considers array elements distinct from
   values within objects.