Movatterモバイル変換

Table 9.44. json andjsonb Operators

Note

There are parallel variants of these operators for both thejson andjsonb types. The field/element/path extraction operators return the same type as their left-hand input (eitherjson orjsonb), except for those specified as returningtext, which coerce the value to text. The field/element/path extraction operators return NULL, rather than failing, if the JSON input does not have the right structure to match the request; for example if no such element exists. The field/element/path extraction operators that accept integer JSON array subscripts all support negative subscripting from the end of arrays.

Table 9.45. Additionaljsonb Operators

Note

The|| operator concatenates two JSON objects by generating an object containing the union of their keys, taking the second object's value when there are duplicate keys. All other cases produce a JSON array: first, any non-array input is converted into a single-element array, and then the two arrays are concatenated. It does not operate recursively; only the top-level array or object structure is merged.

Note

The@? and@@ operators suppress the following errors: lacking object field or array element, unexpected JSON item type, and numeric errors. This behavior might be helpful while searching over JSON document collections of varying structure.

Table 9.46. JSON Creation Functions

Note

array_to_json androw_to_json have the same behavior asto_json except for offering a pretty-printing option. The behavior described forto_json likewise applies to each individual value converted by the other JSON creation functions.

Note

Thehstore extension has a cast fromhstore tojson, so thathstore values converted via the JSON creation functions will be represented as JSON objects, not as primitive string values.

Table 9.47. JSON Processing Functions

 key | value-----+------- a   | "foo" b   | "bar"

 key | value-----+------- a   | foo b   | bar

 json_object_keys------------------ f1 f2

 a |   b       |      c---+-----------+------------- 1 | {2,"a b"} | (4,"a b c")

 a | b---+--- 1 | 2 3 | 4

   value----------- 1 true [2,false]

   value----------- foo bar

 a |    b    |    c    | d |       r---+---------+---------+---+--------------- 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")

 a |  b---+----- 1 | foo 2 |

[    {        "f1": 1,        "f2": null    },    2,    null,    3]

 jsonb_path_query------------------ 2 3 4

Note

Many of these functions and operators will convert Unicode escapes in JSON strings to the appropriate single character. This is a non-issue if the input is typejsonb, because the conversion was already done; but forjson input, this may result in throwing an error, as noted inSection 8.14.

Note

The functionsjson[b]_populate_record,json[b]_populate_recordset,json[b]_to_record andjson[b]_to_recordset operate on a JSON object, or array of objects, and extract the values associated with keys whose names match column names of the output row type. Object fields that do not correspond to any output column name are ignored, and output columns that do not match any object field will be filled with nulls. To convert a JSON value to the SQL type of an output column, the following rules are applied in sequence:

While the examples for these functions use constants, the typical use would be to reference a table in theFROM clause and use one of itsjson orjsonb columns as an argument to the function. Extracted key values can then be referenced in other parts of the query, likeWHERE clauses and target lists. Extracting multiple values in this way can improve performance over extracting them separately with per-key operators.

Note

All the items of thepath parameter ofjsonb_set as well asjsonb_insert except the last item must be present in thetarget. Ifcreate_missing is false, all items of thepath parameter ofjsonb_set must be present. If these conditions are not met thetarget is returned unchanged.

If the last path item is an object key, it will be created if it is absent and given the new value. If the last path item is an array index, if it is positive the item to set is found by counting from the left, and if negative by counting from the right --1 designates the rightmost element, and so on. If the item is out of the range -array_length .. array_length -1, and create_missing is true, the new value is added at the beginning of the array if the item is negative, and at the end of the array if it is positive.

Note

Thejson_typeof function'snull return value should not be confused with a SQL NULL. While callingjson_typeof('null'::json) will returnnull, callingjson_typeof(NULL::json) will return a SQL NULL.

Note

If the argument tojson_strip_nulls contains duplicate field names in any object, the result could be semantically somewhat different, depending on the order in which they occur. This is not an issue forjsonb_strip_nulls sincejsonb values never have duplicate object field names.

Note

Thejsonb_path_exists,jsonb_path_match,jsonb_path_query,jsonb_path_query_array, andjsonb_path_query_first functions have optionalvars andsilent arguments.

If thevars argument is specified, it provides an object containing named variables to be substituted into ajsonpath expression.

If thesilent argument is specified and has thetrue value, these functions suppress the same errors as the@? and@@ operators.

• .datetime() item method is not implemented yet mainly because immutablejsonpath functions and operators cannot reference session timezone, which is used in some datetime operations. Datetime support will be added tojsonpath in future versions ofPostgreSQL.

• A path expression can be a Boolean predicate, although the SQL/JSON standard allows predicates only in filters. This is necessary for implementation of the@@ operator. For example, the followingjsonpath expression is valid inPostgreSQL:

  '$.track.segments[*].HR < 70'

• There are minor differences in the interpretation of regular expression patterns used inlike_regex filters, as described inSection 9.15.2.2.

When you query JSON data, the path expression may not match the actual JSON data structure. An attempt to access a non-existent member of an object or element of an array results in a structural error. SQL/JSON path expressions have two modes of handling structural errors:

The lax mode facilitates matching of a JSON document structure and path expression if the JSON data does not conform to the expected schema. If an operand does not match the requirements of a particular operation, it can be automatically wrapped as an SQL/JSON array or unwrapped by converting its elements into an SQL/JSON sequence before performing this operation. Besides, comparison operators automatically unwrap their operands in the lax mode, so you can compare SQL/JSON arrays out-of-the-box. An array of size 1 is considered equal to its sole element. Automatic unwrapping is not performed only when:

For example, when querying the GPS data listed above, you can abstract from the fact that it stores an array of segments when using the lax mode:

'lax $.track.segments.location'

In the strict mode, the specified path must exactly match the structure of the queried JSON document to return an SQL/JSON item, so using this path expression will cause an error. To get the same result as in the lax mode, you have to explicitly unwrap thesegments array:

'strict $.track.segments[*].location'

The.** accessor can lead to surprising results when using the lax mode. For instance, the following query selects everyHR value twice:

lax $.**.HR

This happens because the.** accessor selects both thesegments array and each of its elements, while the.HR accessor automatically unwraps arrays when using the lax mode. To avoid surprising results, we recommend using the.** accessor only in the strict mode. The following query selects eachHR value just once:

strict $.**.HR

SQL/JSON path expressions allow matching text to a regular expression with thelike_regex filter. For example, the following SQL/JSON path query would case-insensitively match all strings in an array that start with an English vowel:

'$[*] ? (@ like_regex "^[aeiou]" flag "i")'

The optionalflag string may include one or more of the charactersi for case-insensitive match,m to allow^ and$ to match at newlines,s to allow. to match a newline, andq to quote the whole pattern (reducing the behavior to a simple substring match).

The SQL/JSON standard borrows its definition for regular expressions from theLIKE_REGEX operator, which in turn uses the XQuery standard. PostgreSQL does not currently support theLIKE_REGEX operator. Therefore, thelike_regex filter is implemented using the POSIX regular expression engine described inSection 9.7.3. This leads to various minor discrepancies from standard SQL/JSON behavior, which are cataloged inSection 9.7.3.8. Note, however, that the flag-letter incompatibilities described there do not apply to SQL/JSON, as it translates the XQuery flag letters to match what the POSIX engine expects.

Keep in mind that the pattern argument oflike_regex is a JSON path string literal, written according to the rules given inSection 8.14.6. This means in particular that any backslashes you want to use in the regular expression must be doubled. For example, to match string values of the root document that contain only digits:

$.* ? (@ like_regex "^\\d+$")

Table 9.48 shows the operators and methods available injsonpath.Table 9.49 shows the available filter expression elements.