From 5e45211a64149b3c659b90ff2de6fa982a5a93ed Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:17:33 +0200 Subject: Adding upstream version 15.5. Signed-off-by: Daniel Baumann --- doc/src/sgml/json.sgml | 1013 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 1013 insertions(+) create mode 100644 doc/src/sgml/json.sgml (limited to 'doc/src/sgml/json.sgml') diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml new file mode 100644 index 0000000..4182d69 --- /dev/null +++ b/doc/src/sgml/json.sgml @@ -0,0 +1,1013 @@ + + + + <acronym>JSON</acronym> Types + + + JSON + + + + JSONB + + + + 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 . + + + + 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 + . + + + + 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. + + + + + Many of the JSON processing functions described + in 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 . + 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. + + + + JSON Primitive Types and Corresponding <productname>PostgreSQL</productname> 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 + + + +
+ + + 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 . + + + + + 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. + + + + + <type>jsonb</type> Containment and Existence + + jsonb + containment + + + jsonb + 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 @> '{"version": 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 false + +-- 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. + + + + + 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 . + + + + + <type>jsonb</type> Indexing + + jsonb + indexes on + + + + 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 .) + 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 .) + + + + 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. + + + For this purpose, the term value includes array elements, + though JSON terminology sometimes considers array elements distinct + from values within objects. + + + 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 + + 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. + + + + + <type>jsonb</type> 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'; + + + + + + + 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. + + + + + jsonpath Type + + + jsonpath + + + + 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. + + + + + + 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 ). + 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 JSON strings: + \b, + \f, + \n, + \r, + \t, + \v + for various ASCII control characters, and + \uNNNN for a Unicode + character identified by its 4-hex-digit code point. The backslash + syntax also includes two cases not allowed by JSON: + \xNN for a character code + written with only two hex digits, and + \u{N...} for a character + code 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 . + + + + + Accessor operators listed in . + + + + + jsonpath operators and methods listed + in . + + + + + 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 . + + + + <type>jsonpath</type> 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 for details. + + + + + @ + A variable representing the result of path evaluation + in filter expressions. + + + + +
+ + + <type>jsonpath</type> 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. + + + + + +
+ + + -- cgit v1.2.3