From a083657896c739909a25190ebd0032c01f6c8109 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 26 Sep 2019 09:44:22 +0200 Subject: [PATCH] doc: Reorder JSON functions documentation Put the description of the SQL/JSON path language after the description of the general JSON functions and operators, instead of before. Discussion: https://www.postgresql.org/message-id/16968.1569189812@sss.pgh.pa.us --- doc/src/sgml/func.sgml | 2706 ++++++++++++++++++++-------------------- 1 file changed, 1351 insertions(+), 1355 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 67f1a828a8..cc3041f637 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -11690,1144 +11690,463 @@ table2-mapping - JSON Functions, Operators, and Expressions - - - The functions, operators, and expressions described in this section - operate on JSON data: - - - - - - SQL/JSON path expressions - (see ). - - - - - PostgreSQL-specific functions and operators for JSON - data types (see ). - - - - - - To learn more about the SQL/JSON standard, see - . For details on JSON types - supported in PostgreSQL, - see . - + JSON Functions and Operators - - SQL/JSON Path Expressions - SQL/JSON - path expressions + JSON + functions and operators - SQL/JSON path expressions specify the items to be retrieved - from the JSON data, similar to XPath expressions used - for SQL access to XML. In PostgreSQL, - path expressions are implemented as the jsonpath - data type and can use any elements described in - . - - - JSON query functions and operators - pass the provided path expression to the path engine - for evaluation. If the expression matches the queried JSON data, - the corresponding SQL/JSON item is returned. - Path expressions are written in the SQL/JSON path language - and can also include arithmetic expressions and functions. - Query functions treat the provided expression as a - text string, so it must be enclosed in single quotes. - - - - A path expression consists of a sequence of elements allowed - by the jsonpath data type. - The path expression is evaluated from left to right, but - you can use parentheses to change the order of operations. - If the evaluation is successful, a sequence of SQL/JSON items - (SQL/JSON sequence) is produced, - and the evaluation result is returned to the JSON query function - that completes the specified computation. - - - - To refer to the JSON data to be queried (the - context item), use the $ sign - in the path expression. It can be followed by one or more - accessor operators, - which go down the JSON structure level by level to retrieve the - content of context item. Each operator that follows deals with the - result of the previous evaluation step. - - - - For example, suppose you have some JSON data from a GPS tracker that you - would like to parse, such as: - -{ "track" : - { - "segments" : [ - { "location": [ 47.763, 13.4034 ], - "start time": "2018-10-14 10:05:14", - "HR": 73 - }, - { "location": [ 47.706, 13.2635 ], - "start time": "2018-10-14 10:39:21", - "HR": 135 - } ] - } -} - - - - - To retrieve the available track segments, you need to use the - .key accessor - operator for all the preceding JSON objects: - -'$.track.segments' - - + This section describes: - - If the item to retrieve is an element of an array, you have - to unnest this array using the [*] operator. For example, - the following path will return location coordinates for all - the available track segments: - -'$.track.segments[*].location' - + + + + functions and operators for processing and creating JSON data + + + + + the SQL/JSON path language + + + - To return the coordinates of the first segment only, you can - specify the corresponding subscript in the [] - accessor operator. Note that the SQL/JSON arrays are 0-relative: - -'$.track.segments[0].location' - + To learn more about the SQL/JSON standard, see + . For details on JSON types + supported in PostgreSQL, + see . - - The result of each path evaluation step can be processed - by one or more jsonpath operators and methods - listed in . - Each method name must be preceded by a dot. For example, - you can get an array size: - -'$.track.segments.size()' - - For more examples of using jsonpath operators - and methods within path expressions, see - . - + + Processing and Creating JSON Data - When defining the path, you can also use one or more - filter expressions that work similar to the - WHERE clause in SQL. A filter expression begins with - a question mark and provides a condition in parentheses: - - -? (condition) - + shows the operators that + are available for use with JSON data types (see ). - - Filter expressions must be specified right after the path evaluation step - to which they are applied. The result of this step is filtered to include - only those items that satisfy the provided condition. SQL/JSON defines - three-valued logic, so the condition can be true, false, - or unknown. The unknown value - plays the same role as SQL NULL and can be tested - for with the is unknown predicate. Further path - evaluation steps use only those items for which filter expressions - return true. - + + <type>json</type> and <type>jsonb</type> Operators + + + + Operator + Right Operand Type + Return type + Description + Example + Example Result + + + + + -> + int + json or jsonb + Get JSON array element (indexed from zero, negative + integers count from the end) + '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 + {"c":"baz"} + + + -> + text + json or jsonb + Get JSON object field by key + '{"a": {"b":"foo"}}'::json->'a' + {"b":"foo"} + + + ->> + int + text + Get JSON array element as text + '[1,2,3]'::json->>2 + 3 + + + ->> + text + text + Get JSON object field as text + '{"a":1,"b":2}'::json->>'b' + 2 + + + #> + text[] + json or jsonb + Get JSON object at the specified path + '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' + {"c": "foo"} + + + #>> + text[] + text + Get JSON object at the specified path as text + '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' + 3 + + + +
+ + + There are parallel variants of these operators for both the + json and jsonb types. + The field/element/path extraction operators + return the same type as their left-hand input (either json + or jsonb), except for those specified as + returning text, 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. + + - Functions and operators that can be used in filter expressions are listed - in . The path - evaluation result to be filtered is denoted by the @ - variable. To refer to a JSON element stored at a lower nesting level, - add one or more accessor operators after @. + The standard comparison operators shown in are available for + jsonb, but not for json. They follow the + ordering rules for B-tree operations outlined at . - - Suppose you would like to retrieve all heart rate values higher - than 130. You can achieve this using the following expression: - -'$.track.segments[*].HR ? (@ > 130)' - + Some further operators also exist only for jsonb, as shown + in . + Many of these operators can be indexed by + jsonb operator classes. For a full description of + jsonb containment and existence semantics, see . + describes how these operators can be used to effectively index + jsonb data. - - - To get the start time of segments with such values instead, you have to - filter out irrelevant segments before returning the start time, so the - filter expression is applied to the previous step, and the path used - in the condition is different: - -'$.track.segments[*] ? (@.HR > 130)."start time"' - - - - - You can use several filter expressions on the same nesting level, if - required. For example, the following expression selects all segments - that contain locations with relevant coordinates and high heart rate values: - -'$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"' - - - - - Using filter expressions at different nesting levels is also allowed. - The following example first filters all segments by location, and then - returns high heart rate values for these segments, if available: - -'$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)' - - - - - You can also nest filter expressions within each other: - -'$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()' - - This expression returns the size of the track if it contains any - segments with high heart rate values, or an empty sequence otherwise. - - - - PostgreSQL's implementation of SQL/JSON path - language has the following deviations from the SQL/JSON standard: - - - - - - 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 following jsonpath expression is valid in - PostgreSQL: - -'$.track.segments[*].HR < 70' - - - - - - - There are minor differences in the interpretation of regular - expression patterns used in like_regex filters, as - described in . - - - - - - Strict and Lax Modes - - 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: - - - - - - lax (default) — the path engine implicitly adapts - the queried data to the specified path. - Any remaining structural errors are suppressed and converted - to empty SQL/JSON sequences. - - - - - strict — if a structural error occurs, an error is raised. - - - - - - 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: - - - - The path expression contains type() or - size() methods that return the type - and the number of elements in the array, respectively. - - - - - The queried JSON data contain nested arrays. In this case, only - the outermost array is unwrapped, while all the inner arrays - remain unchanged. Thus, implicit unwrapping can only go one - level down within each path evaluation step. - - - - - - - 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 the - segments array: - -'strict $.track.segments[*].location' - - - - - - - Regular Expressions - - - LIKE_REGEX - in SQL/JSON - - - - SQL/JSON path expressions allow matching text to a regular expression - with the like_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 optional flag string may include one or more of - the characters - i for case-insensitive match, - m to allow ^ - and $ to match at newlines, - s to allow . to match a newline, - and q to quote the whole pattern (reducing the - behavior to a simple substring match). - - - - The SQL/JSON standard borrows its definition for regular expressions - from the LIKE_REGEX operator, which in turn uses the - XQuery standard. PostgreSQL does not currently support the - LIKE_REGEX operator. Therefore, - the like_regex filter is implemented using the - POSIX regular expression engine described in - . This leads to various minor - discrepancies from standard SQL/JSON behavior, which are cataloged in - . - 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 of like_regex - is a JSON path string literal, written according to the rules given in - . This means in particular that any - backslashes you want to use in the regular expression must be doubled. - For example, to match strings that contain only digits: - -'$ ? (@ like_regex "^\\d+$")' - - - - - - - SQL/JSON Path Operators and Methods - - - shows the operators and - methods available in jsonpath. shows the available filter - expression elements. - - - - <type>jsonpath</type> Operators and Methods - +
+ Additional <type>jsonb</type> Operators + - Operator/Method + Operator + Right Operand Type Description - Example JSON - Example Query - Result + Example - + (unary) - Plus operator that iterates over the SQL/JSON sequence - {"x": [2.85, -14.7, -9.4]} - + $.x.floor() - 2, -15, -10 - - - - (unary) - Minus operator that iterates over the SQL/JSON sequence - {"x": [2.85, -14.7, -9.4]} - - $.x.floor() - -2, 15, 10 + @> + jsonb + Does the left JSON value contain the right JSON + path/value entries at the top level? + '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb - + (binary) - Addition - [2] - 2 + $[0] - 4 + <@ + jsonb + Are the left JSON path/value entries contained at the top level within + the right JSON value? + '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb - - (binary) - Subtraction - [2] - 4 - $[0] - 2 + ? + text + Does the string exist as a top-level + key within the JSON value? + '{"a":1, "b":2}'::jsonb ? 'b' - * - Multiplication - [4] - 2 * $[0] - 8 - - - / - Division - [8] - $[0] / 2 - 4 - - - % - Modulus - [32] - $[0] % 10 - 2 - - - type() - Type of the SQL/JSON item - [1, "2", {}] - $[*].type() - "number", "string", "object" + ?| + text[] + Do any of these array strings + exist as top-level keys? + '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] - size() - Size of the SQL/JSON item - {"m": [11, 15]} - $.m.size() - 2 + ?& + text[] + Do all of these array strings exist + as top-level keys? + '["a", "b"]'::jsonb ?& array['a', 'b'] - double() - Approximate floating-point number converted from an SQL/JSON number or a string - {"len": "1.9"} - $.len.double() * 2 - 3.8 + || + jsonb + Concatenate two jsonb values into a new jsonb value + '["a", "b"]'::jsonb || '["c", "d"]'::jsonb - ceiling() - Nearest integer greater than or equal to the SQL/JSON number - {"h": 1.3} - $.h.ceiling() - 2 + - + text + Delete key/value pair or string + element from left operand. Key/value pairs are matched based + on their key value. + '{"a": "b"}'::jsonb - 'a' - floor() - Nearest integer less than or equal to the SQL/JSON number - {"h": 1.3} - $.h.floor() - 1 + - + text[] + Delete multiple key/value pairs or string + elements from left operand. Key/value pairs are matched based + on their key value. + '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] - abs() - Absolute value of the SQL/JSON number - {"z": -0.3} - $.z.abs() - 0.3 + - + integer + Delete the array element with specified index (Negative + integers count from the end). Throws an error if top level + container is not an array. + '["a", "b"]'::jsonb - 1 - datetime() - Date/time value converted from a string - ["2015-8-1", "2015-08-12"] - $[*] ? (@.datetime() < "2015-08-2". datetime()) - 2015-8-1 + #- + text[] + Delete the field or element with specified path (for + JSON arrays, negative integers count from the end) + '["a", {"b":1}]'::jsonb #- '{1,b}' - datetime(template) - Date/time value converted from a string using the specified template - ["12:30", "18:40"] - $[*].datetime("HH24:MI") - "12:30:00", "18:40:00" + @? + jsonpath + Does JSON path return any item for the specified JSON value? + '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' - keyvalue() - - Sequence of object's key-value pairs represented as array of items - containing three fields ("key", - "value", and "id"). - "id" is a unique identifier of the object - key-value pair belongs to. - - {"x": "20", "y": 32} - $.keyvalue() - {"key": "x", "value": "20", "id": 0}, {"key": "y", "value": 32, "id": 0} + @@ + jsonpath + Returns the result of JSON path predicate check for the specified JSON value. + Only the first item of the result is taken into account. If the + result is not Boolean, then null is returned. + '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' -
+ - - - The result type of datetime() and - datetime(template) - methods can be date, timetz, time, - timestamptz, or timestamp. - Both methods determine the result type dynamically. - - - The datetime() method sequentially tries ISO formats - for date, timetz, time, - timestamptz, and timestamp. It stops on - the first matching format and the corresponding data type. - - - The datetime(template) - method determines the result type by the provided template string. - - - The datetime() and - datetime(template) methods - use the same parsing rules as to_timestamp SQL - function does (see ) with three - exceptions. At first, these methods doesn't allow unmatched template - patterns. At second, only following separators are allowed in the - template string: minus sign, period, solidus, comma, apostrophe, - semicolon, colon and space. At third, separators in the template string - must exactly match the input string. - - + + + The || operator concatenates the elements at the top level of + each of its operands. It does not operate recursively. For example, if + both operands are objects with a common key field name, the value of the + field in the result will just be the value from the right hand operand. + + - - <type>jsonpath</type> Filter Expression Elements - - - - Value/Predicate - Description - Example JSON - Example Query - Result - - - - - == - Equality operator - [1, 2, 1, 3] - $[*] ? (@ == 1) - 1, 1 - - - != - Non-equality operator - [1, 2, 1, 3] - $[*] ? (@ != 1) - 2, 3 - - - <> - Non-equality operator (same as !=) - [1, 2, 1, 3] - $[*] ? (@ <> 1) - 2, 3 - - - < - Less-than operator - [1, 2, 3] - $[*] ? (@ < 2) - 1 - - - <= - Less-than-or-equal-to operator - [1, 2, 3] - $[*] ? (@ <= 2) - 1, 2 - - - > - Greater-than operator - [1, 2, 3] - $[*] ? (@ > 2) - 3 - - - >= - Greater-than-or-equal-to operator - [1, 2, 3] - $[*] ? (@ >= 2) - 2, 3 - - - true - Value used to perform comparison with JSON true literal - [{"name": "John", "parent": false}, - {"name": "Chris", "parent": true}] - $[*] ? (@.parent == true) - {"name": "Chris", "parent": true} - - - false - Value used to perform comparison with JSON false literal - [{"name": "John", "parent": false}, - {"name": "Chris", "parent": true}] - $[*] ? (@.parent == false) - {"name": "John", "parent": false} - - - null - Value used to perform comparison with JSON null value - [{"name": "Mary", "job": null}, - {"name": "Michael", "job": "driver"}] - $[*] ? (@.job == null) .name - "Mary" - - - && - Boolean AND - [1, 3, 7] - $[*] ? (@ > 1 && @ < 5) - 3 - - - || - Boolean OR - [1, 3, 7] - $[*] ? (@ < 1 || @ > 5) - 7 - - - ! - Boolean NOT - [1, 3, 7] - $[*] ? (!(@ < 5)) - 7 - - - like_regex - - Tests whether the first operand matches the regular expression - given by the second operand, optionally with modifications - described by a string of flag characters (see - ) - - ["abc", "abd", "aBdC", "abdacb", "babc"] - $[*] ? (@ like_regex "^ab.*c" flag "i") - "abc", "aBdC", "abdacb" - - - starts with - Tests whether the second operand is an initial substring of the first operand - ["John Smith", "Mary Stone", "Bob Johnson"] - $[*] ? (@ starts with "John") - "John Smith" - - - exists - Tests whether a path expression matches at least one SQL/JSON item - {"x": [1, 2], "y": [2, 4]} - strict $.* ? (exists (@ ? (@[*] > 2))) - 2, 4 - - - is unknown - Tests whether a Boolean condition is unknown - [-1, 2, 7, "infinity"] - $[*] ? ((@ > 0) is unknown) - "infinity" - - - -
- - - - When different date/time values are compared, an implicit cast is - applied. A date value can be cast to timestamp - or timestamptz, timestamp can be cast to - timestamptz, and time — to timetz. - - - + + + The @? and @@ operators suppress + the following errors: lacking object field or array element, unexpected + JSON item type, datetime and numeric errors. + This behavior might be helpful while searching over JSON document + collections of varying structure. + + - + + shows the functions that are + available for creating json and jsonb values. + (There are no equivalent functions for jsonb, of the row_to_json + and array_to_json functions. However, the to_jsonb + function supplies much the same functionality as these functions would.) + - - JSON Functions and Operators - - JSON - functions and operators + + to_json + + + array_to_json + + + row_to_json + + + json_build_array + + + json_build_object + + + json_object + + + to_jsonb + + + jsonb_build_array + + + jsonb_build_object + + + jsonb_object + + JSON Creation Functions + + + + Function + Description + Example + Example Result + + + + + to_json(anyelement) + to_jsonb(anyelement) + + + Returns the value as json or jsonb. + Arrays and composites are converted + (recursively) to arrays and objects; otherwise, if there is a cast + from the type to json, the cast function will be used to + perform the conversion; otherwise, a scalar value is produced. + For any scalar type other than a number, a Boolean, or a null value, + the text representation will be used, in such a fashion that it is a + valid json or jsonb value. + + to_json('Fred said "Hi."'::text) + "Fred said \"Hi.\"" + + + + array_to_json(anyarray [, pretty_bool]) + + + Returns the array as a JSON array. A PostgreSQL multidimensional array + becomes a JSON array of arrays. Line feeds will be added between + dimension-1 elements if pretty_bool is true. + + array_to_json('{{1,5},{99,100}}'::int[]) + [[1,5],[99,100]] + + + + row_to_json(record [, pretty_bool]) + + + Returns the row as a JSON object. Line feeds will be added between + level-1 elements if pretty_bool is true. + + row_to_json(row(1,'foo')) + {"f1":1,"f2":"foo"} + + + json_build_array(VARIADIC "any") + jsonb_build_array(VARIADIC "any") + + + Builds a possibly-heterogeneously-typed JSON array out of a variadic + argument list. + + json_build_array(1,2,'3',4,5) + [1, 2, "3", 4, 5] + + + json_build_object(VARIADIC "any") + jsonb_build_object(VARIADIC "any") + + + Builds a JSON object out of a variadic argument list. By + convention, the argument list consists of alternating + keys and values. + + json_build_object('foo',1,'bar',2) + {"foo": 1, "bar": 2} + + + json_object(text[]) + jsonb_object(text[]) + + + Builds a JSON object out of a text array. The array must have either + exactly one dimension with an even number of members, in which case + they are taken as alternating key/value pairs, or two dimensions + such that each inner array has exactly two elements, which + are taken as a key/value pair. + + json_object('{a, 1, b, "def", c, 3.5}') + json_object('{{a, 1},{b, "def"},{c, 3.5}}') + {"a": "1", "b": "def", "c": "3.5"} + + + json_object(keys text[], values text[]) + jsonb_object(keys text[], values text[]) + + + This form of json_object takes keys and values pairwise from two separate + arrays. In all other respects it is identical to the one-argument form. + + json_object('{a, b}', '{1,2}') + {"a": "1", "b": "2"} + + + +
+ + + + array_to_json and row_to_json have the same + behavior as to_json except for offering a pretty-printing + option. The behavior described for to_json likewise applies + to each individual value converted by the other JSON creation functions. + + + + + + The extension has a cast + from hstore to json, so that + hstore values converted via the JSON creation functions + will be represented as JSON objects, not as primitive string values. + + + - shows the operators that - are available for use with JSON data types (see ). - - - - <type>json</type> and <type>jsonb</type> Operators - - - - Operator - Right Operand Type - Return type - Description - Example - Example Result - - - - - -> - int - json or jsonb - Get JSON array element (indexed from zero, negative - integers count from the end) - '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 - {"c":"baz"} - - - -> - text - json or jsonb - Get JSON object field by key - '{"a": {"b":"foo"}}'::json->'a' - {"b":"foo"} - - - ->> - int - text - Get JSON array element as text - '[1,2,3]'::json->>2 - 3 - - - ->> - text - text - Get JSON object field as text - '{"a":1,"b":2}'::json->>'b' - 2 - - - #> - text[] - json or jsonb - Get JSON object at the specified path - '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' - {"c": "foo"} - - - #>> - text[] - text - Get JSON object at the specified path as text - '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' - 3 - - - -
- - - - There are parallel variants of these operators for both the - json and jsonb types. - The field/element/path extraction operators - return the same type as their left-hand input (either json - or jsonb), except for those specified as - returning text, 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. - - - - The standard comparison operators shown in are available for - jsonb, but not for json. They follow the - ordering rules for B-tree operations outlined at . - - - Some further operators also exist only for jsonb, as shown - in . - Many of these operators can be indexed by - jsonb operator classes. For a full description of - jsonb containment and existence semantics, see . - describes how these operators can be used to effectively index - jsonb data. - - - Additional <type>jsonb</type> Operators - - - - Operator - Right Operand Type - Description - Example - - - - - @> - jsonb - Does the left JSON value contain the right JSON - path/value entries at the top level? - '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb - - - <@ - jsonb - Are the left JSON path/value entries contained at the top level within - the right JSON value? - '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb - - - ? - text - Does the string exist as a top-level - key within the JSON value? - '{"a":1, "b":2}'::jsonb ? 'b' - - - ?| - text[] - Do any of these array strings - exist as top-level keys? - '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] - - - ?& - text[] - Do all of these array strings exist - as top-level keys? - '["a", "b"]'::jsonb ?& array['a', 'b'] - - - || - jsonb - Concatenate two jsonb values into a new jsonb value - '["a", "b"]'::jsonb || '["c", "d"]'::jsonb - - - - - text - Delete key/value pair or string - element from left operand. Key/value pairs are matched based - on their key value. - '{"a": "b"}'::jsonb - 'a' - - - - - text[] - Delete multiple key/value pairs or string - elements from left operand. Key/value pairs are matched based - on their key value. - '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] - - - - - integer - Delete the array element with specified index (Negative - integers count from the end). Throws an error if top level - container is not an array. - '["a", "b"]'::jsonb - 1 - - - #- - text[] - Delete the field or element with specified path (for - JSON arrays, negative integers count from the end) - '["a", {"b":1}]'::jsonb #- '{1,b}' - - - @? - jsonpath - Does JSON path return any item for the specified JSON value? - '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' - - - @@ - jsonpath - Returns the result of JSON path predicate check for the specified JSON value. - Only the first item of the result is taken into account. If the - result is not Boolean, then null is returned. - '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' - - - -
- - - - The || operator concatenates the elements at the top level of - each of its operands. It does not operate recursively. For example, if - both operands are objects with a common key field name, the value of the - field in the result will just be the value from the right hand operand. - - - - - - The @? and @@ operators suppress - the following errors: lacking object field or array element, unexpected - JSON item type, datetime and numeric errors. - This behavior might be helpful while searching over JSON document - collections of varying structure. - - - - - shows the functions that are - available for creating json and jsonb values. - (There are no equivalent functions for jsonb, of the row_to_json - and array_to_json functions. However, the to_jsonb - function supplies much the same functionality as these functions would.) + shows the functions that + are available for processing json and jsonb values. - to_json + json_array_length - array_to_json + jsonb_array_length - row_to_json + json_each - json_build_array + jsonb_each - json_build_object + json_each_text - json_object + jsonb_each_text - to_jsonb + json_extract_path - jsonb_build_array + jsonb_extract_path - jsonb_build_object + json_extract_path_text - jsonb_object - - - - JSON Creation Functions - - - - Function - Description - Example - Example Result - - - - - to_json(anyelement) - to_jsonb(anyelement) - - - Returns the value as json or jsonb. - Arrays and composites are converted - (recursively) to arrays and objects; otherwise, if there is a cast - from the type to json, the cast function will be used to - perform the conversion; otherwise, a scalar value is produced. - For any scalar type other than a number, a Boolean, or a null value, - the text representation will be used, in such a fashion that it is a - valid json or jsonb value. - - to_json('Fred said "Hi."'::text) - "Fred said \"Hi.\"" - - - - array_to_json(anyarray [, pretty_bool]) - - - Returns the array as a JSON array. A PostgreSQL multidimensional array - becomes a JSON array of arrays. Line feeds will be added between - dimension-1 elements if pretty_bool is true. - - array_to_json('{{1,5},{99,100}}'::int[]) - [[1,5],[99,100]] - - - - row_to_json(record [, pretty_bool]) - - - Returns the row as a JSON object. Line feeds will be added between - level-1 elements if pretty_bool is true. - - row_to_json(row(1,'foo')) - {"f1":1,"f2":"foo"} - - - json_build_array(VARIADIC "any") - jsonb_build_array(VARIADIC "any") - - - Builds a possibly-heterogeneously-typed JSON array out of a variadic - argument list. - - json_build_array(1,2,'3',4,5) - [1, 2, "3", 4, 5] - - - json_build_object(VARIADIC "any") - jsonb_build_object(VARIADIC "any") - - - Builds a JSON object out of a variadic argument list. By - convention, the argument list consists of alternating - keys and values. - - json_build_object('foo',1,'bar',2) - {"foo": 1, "bar": 2} - - - json_object(text[]) - jsonb_object(text[]) - - - Builds a JSON object out of a text array. The array must have either - exactly one dimension with an even number of members, in which case - they are taken as alternating key/value pairs, or two dimensions - such that each inner array has exactly two elements, which - are taken as a key/value pair. - - json_object('{a, 1, b, "def", c, 3.5}') - json_object('{{a, 1},{b, "def"},{c, 3.5}}') - {"a": "1", "b": "def", "c": "3.5"} - - - json_object(keys text[], values text[]) - jsonb_object(keys text[], values text[]) - - - This form of json_object takes keys and values pairwise from two separate - arrays. In all other respects it is identical to the one-argument form. - - json_object('{a, b}', '{1,2}') - {"a": "1", "b": "2"} - - - -
- - - - array_to_json and row_to_json have the same - behavior as to_json except for offering a pretty-printing - option. The behavior described for to_json likewise applies - to each individual value converted by the other JSON creation functions. - - - - - - The extension has a cast - from hstore to json, so that - hstore values converted via the JSON creation functions - will be represented as JSON objects, not as primitive string values. - - - - - shows the functions that - are available for processing json and jsonb values. - - - - json_array_length - - - jsonb_array_length - - - json_each - - - jsonb_each - - - json_each_text - - - jsonb_each_text - - - json_extract_path - - - jsonb_extract_path - - - json_extract_path_text - - - jsonb_extract_path_text + jsonb_extract_path_text json_object_keys @@ -13233,319 +12552,996 @@ table2-mapping {"a": [0, 1, "new_value", 2]} - - jsonb_pretty(from_json jsonb) - - text - - Returns from_json - as indented JSON text. - - jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') - - -[ - { - "f1": 1, - "f2": null - }, - 2, - null, - 3 -] - - + + jsonb_pretty(from_json jsonb) + + text + + Returns from_json + as indented JSON text. + + jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') + + +[ + { + "f1": 1, + "f2": null + }, + 2, + null, + 3 +] + + + + + + + jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb [, silent bool]]) + + + jsonb_path_exists_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + + boolean + + Checks whether JSON path returns any item for the specified JSON + value. + + + + jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') + + + + true + + + + + + jsonb_path_match(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + + jsonb_path_match_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + + boolean + + Returns the result of JSON path predicate check for the specified JSON value. + Only the first item of the result is taken into account. If the + result is not Boolean, then null is returned. + + + + jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2,"max":4}') + + + + true + + + + + + jsonb_path_query(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + + jsonb_path_query_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + + setof jsonb + + Gets all JSON items returned by JSON path for the specified JSON + value. + + + + select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}'); + + + + + + jsonb_path_query +------------------ + 2 + 3 + 4 + + + + + + + + jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + + jsonb_path_query_array_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + + jsonb + + Gets all JSON items returned by JSON path for the specified JSON + value and wraps result into an array. + + + + jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') + + + + [2, 3, 4] + + + + + + jsonb_path_query_first(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + + jsonb_path_query_first_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) + + + jsonb + + Gets the first JSON item returned by JSON path for the specified JSON + value. Returns NULL on no results. + + + + jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') + + + + 2 + + + + + + + + + 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 type jsonb, because the conversion was already + done; but for json input, this may result in throwing an error, + as noted in . + + + + + + The functions + json[b]_populate_record, + json[b]_populate_recordset, + json[b]_to_record and + json[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: + + + + A JSON null value is converted to a SQL null in all cases. + + + + + If the output column is of type json + or jsonb, the JSON value is just reproduced exactly. + + + + + If the output column is a composite (row) type, and the JSON value is + a JSON object, the fields of the object are converted to columns of + the output row type by recursive application of these rules. + + + + + Likewise, if the output column is an array type and the JSON value is + a JSON array, the elements of the JSON array are converted to elements + of the output array by recursive application of these rules. + + + + + Otherwise, if the JSON value is a string literal, the contents of the + string are fed to the input conversion function for the column's data + type. + + + + + Otherwise, the ordinary text representation of the JSON value is fed + to the input conversion function for the column's data type. + + + + + + + While the examples for these functions use constants, the typical use + would be to reference a table in the FROM clause + and use one of its json or jsonb columns + as an argument to the function. Extracted key values can then be + referenced in other parts of the query, like WHERE + clauses and target lists. Extracting multiple values in this + way can improve performance over extracting them separately with + per-key operators. + + + + + + All the items of the path parameter of jsonb_set + as well as jsonb_insert except the last item must be present + in the target. If create_missing is false, all + items of the path parameter of jsonb_set must be + present. If these conditions are not met the target 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. + + + + + + The json_typeof function's null return value + should not be confused with a SQL NULL. While + calling json_typeof('null'::json) will + return null, calling json_typeof(NULL::json) + will return a SQL NULL. + + + + + + If the argument to json_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 for jsonb_strip_nulls since jsonb values never have + duplicate object field names. + + + + + + The jsonb_path_* functions have optional + vars and silent arguments. + + + If the vars argument is specified, it provides an + object containing named variables to be substituted into a + jsonpath expression. + + + If the silent argument is specified and has the + true value, these functions suppress the same errors + as the @? and @@ operators. + + + + + + Some of the jsonb_path_* functions have the + _tz suffix. These functions have been implemented to + support comparison of date/time values that involves implicit + timezone-aware casts. Since operations with time zones are not immutable, + these functions are qualified as stable. Their counterparts without the + suffix do not support such casts, so they are immutable and can be used for + such use-cases as expression indexes + (see ). There is no difference + between these functions for other jsonpath operations. + + + + + See also for the aggregate + function json_agg which aggregates record + values as JSON, and the aggregate function + json_object_agg which aggregates pairs of values + into a JSON object, and their jsonb equivalents, + jsonb_agg and jsonb_object_agg. + + + + + The SQL/JSON Path Language + + + SQL/JSON path language + + + + SQL/JSON path expressions specify the items to be retrieved + from the JSON data, similar to XPath expressions used + for SQL access to XML. In PostgreSQL, + path expressions are implemented as the jsonpath + data type and can use any elements described in + . + + + JSON query functions and operators + pass the provided path expression to the path engine + for evaluation. If the expression matches the queried JSON data, + the corresponding SQL/JSON item is returned. + Path expressions are written in the SQL/JSON path language + and can also include arithmetic expressions and functions. + Query functions treat the provided expression as a + text string, so it must be enclosed in single quotes. + + + + A path expression consists of a sequence of elements allowed + by the jsonpath data type. + The path expression is evaluated from left to right, but + you can use parentheses to change the order of operations. + If the evaluation is successful, a sequence of SQL/JSON items + (SQL/JSON sequence) is produced, + and the evaluation result is returned to the JSON query function + that completes the specified computation. + + + + To refer to the JSON data to be queried (the + context item), use the $ sign + in the path expression. It can be followed by one or more + accessor operators, + which go down the JSON structure level by level to retrieve the + content of context item. Each operator that follows deals with the + result of the previous evaluation step. + + + + For example, suppose you have some JSON data from a GPS tracker that you + would like to parse, such as: + +{ "track" : + { + "segments" : [ + { "location": [ 47.763, 13.4034 ], + "start time": "2018-10-14 10:05:14", + "HR": 73 + }, + { "location": [ 47.706, 13.2635 ], + "start time": "2018-10-14 10:39:21", + "HR": 135 + } ] + } +} + + + + + To retrieve the available track segments, you need to use the + .key accessor + operator for all the preceding JSON objects: + +'$.track.segments' + + + + + If the item to retrieve is an element of an array, you have + to unnest this array using the [*] operator. For example, + the following path will return location coordinates for all + the available track segments: + +'$.track.segments[*].location' + + + + + To return the coordinates of the first segment only, you can + specify the corresponding subscript in the [] + accessor operator. Note that the SQL/JSON arrays are 0-relative: + +'$.track.segments[0].location' + + + + + The result of each path evaluation step can be processed + by one or more jsonpath operators and methods + listed in . + Each method name must be preceded by a dot. For example, + you can get an array size: + +'$.track.segments.size()' + + For more examples of using jsonpath operators + and methods within path expressions, see + . + + + + When defining the path, you can also use one or more + filter expressions that work similar to the + WHERE clause in SQL. A filter expression begins with + a question mark and provides a condition in parentheses: + + +? (condition) + + + + + Filter expressions must be specified right after the path evaluation step + to which they are applied. The result of this step is filtered to include + only those items that satisfy the provided condition. SQL/JSON defines + three-valued logic, so the condition can be true, false, + or unknown. The unknown value + plays the same role as SQL NULL and can be tested + for with the is unknown predicate. Further path + evaluation steps use only those items for which filter expressions + return true. + + + + Functions and operators that can be used in filter expressions are listed + in . The path + evaluation result to be filtered is denoted by the @ + variable. To refer to a JSON element stored at a lower nesting level, + add one or more accessor operators after @. + + + + Suppose you would like to retrieve all heart rate values higher + than 130. You can achieve this using the following expression: + +'$.track.segments[*].HR ? (@ > 130)' + + + + + To get the start time of segments with such values instead, you have to + filter out irrelevant segments before returning the start time, so the + filter expression is applied to the previous step, and the path used + in the condition is different: + +'$.track.segments[*] ? (@.HR > 130)."start time"' + + + + + You can use several filter expressions on the same nesting level, if + required. For example, the following expression selects all segments + that contain locations with relevant coordinates and high heart rate values: + +'$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"' + + + + + Using filter expressions at different nesting levels is also allowed. + The following example first filters all segments by location, and then + returns high heart rate values for these segments, if available: + +'$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)' + + + + + You can also nest filter expressions within each other: + +'$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()' + + This expression returns the size of the track if it contains any + segments with high heart rate values, or an empty sequence otherwise. + + + + PostgreSQL's implementation of SQL/JSON path + language has the following deviations from the SQL/JSON standard: + + + + + + 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 following jsonpath expression is valid in + PostgreSQL: + +'$.track.segments[*].HR < 70' + + + + + + + There are minor differences in the interpretation of regular + expression patterns used in like_regex filters, as + described in . + + + + + + Strict and Lax Modes + + 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: + + + + + + lax (default) — the path engine implicitly adapts + the queried data to the specified path. + Any remaining structural errors are suppressed and converted + to empty SQL/JSON sequences. + + + + + strict — if a structural error occurs, an error is raised. + + + + + + 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: + + + + The path expression contains type() or + size() methods that return the type + and the number of elements in the array, respectively. + + + + + The queried JSON data contain nested arrays. In this case, only + the outermost array is unwrapped, while all the inner arrays + remain unchanged. Thus, implicit unwrapping can only go one + level down within each path evaluation step. + + + + + + + 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 the + segments array: + +'strict $.track.segments[*].location' + + + + + + + Regular Expressions + + + LIKE_REGEX + in SQL/JSON + + + + SQL/JSON path expressions allow matching text to a regular expression + with the like_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 optional flag string may include one or more of + the characters + i for case-insensitive match, + m to allow ^ + and $ to match at newlines, + s to allow . to match a newline, + and q to quote the whole pattern (reducing the + behavior to a simple substring match). + + + + The SQL/JSON standard borrows its definition for regular expressions + from the LIKE_REGEX operator, which in turn uses the + XQuery standard. PostgreSQL does not currently support the + LIKE_REGEX operator. Therefore, + the like_regex filter is implemented using the + POSIX regular expression engine described in + . This leads to various minor + discrepancies from standard SQL/JSON behavior, which are cataloged in + . + 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 of like_regex + is a JSON path string literal, written according to the rules given in + . This means in particular that any + backslashes you want to use in the regular expression must be doubled. + For example, to match strings that contain only digits: + +'$ ? (@ like_regex "^\\d+$")' + + + + + + + SQL/JSON Path Operators and Methods + + + shows the operators and + methods available in jsonpath. shows the available filter + expression elements. + + + + <type>jsonpath</type> Operators and Methods + + + + Operator/Method + Description + Example JSON + Example Query + Result + + + + + + (unary) + Plus operator that iterates over the SQL/JSON sequence + {"x": [2.85, -14.7, -9.4]} + + $.x.floor() + 2, -15, -10 + + + - (unary) + Minus operator that iterates over the SQL/JSON sequence + {"x": [2.85, -14.7, -9.4]} + - $.x.floor() + -2, 15, 10 + + + + (binary) + Addition + [2] + 2 + $[0] + 4 + + + - (binary) + Subtraction + [2] + 4 - $[0] + 2 + + + * + Multiplication + [4] + 2 * $[0] + 8 + + + / + Division + [8] + $[0] / 2 + 4 + + + % + Modulus + [32] + $[0] % 10 + 2 + + + type() + Type of the SQL/JSON item + [1, "2", {}] + $[*].type() + "number", "string", "object" + + + size() + Size of the SQL/JSON item + {"m": [11, 15]} + $.m.size() + 2 + + + double() + Approximate floating-point number converted from an SQL/JSON number or a string + {"len": "1.9"} + $.len.double() * 2 + 3.8 + + + ceiling() + Nearest integer greater than or equal to the SQL/JSON number + {"h": 1.3} + $.h.ceiling() + 2 + + + floor() + Nearest integer less than or equal to the SQL/JSON number + {"h": 1.3} + $.h.floor() + 1 - - - jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb [, silent bool]]) - - - jsonb_path_exists_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) - - - boolean - - Checks whether JSON path returns any item for the specified JSON - value. - - - - jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') - - - - true - + abs() + Absolute value of the SQL/JSON number + {"z": -0.3} + $.z.abs() + 0.3 - - - jsonb_path_match(target jsonb, path jsonpath [, vars jsonb, silent bool]) - - - jsonb_path_match_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) - - - boolean - - Returns the result of JSON path predicate check for the specified JSON value. - Only the first item of the result is taken into account. If the - result is not Boolean, then null is returned. - - - - jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2,"max":4}') - - - - true - + datetime() + Date/time value converted from a string + ["2015-8-1", "2015-08-12"] + $[*] ? (@.datetime() < "2015-08-2". datetime()) + 2015-8-1 + datetime(template) + Date/time value converted from a string using the specified template + ["12:30", "18:40"] + $[*].datetime("HH24:MI") + "12:30:00", "18:40:00" + + + keyvalue() - - jsonb_path_query(target jsonb, path jsonpath [, vars jsonb, silent bool]) - - - jsonb_path_query_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) - - - setof jsonb - - Gets all JSON items returned by JSON path for the specified JSON - value. - - - - select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}'); - - - - - - jsonb_path_query ------------------- - 2 - 3 - 4 - - + Sequence of object's key-value pairs represented as array of items + containing three fields ("key", + "value", and "id"). + "id" is a unique identifier of the object + key-value pair belongs to. + {"x": "20", "y": 32} + $.keyvalue() + {"key": "x", "value": "20", "id": 0}, {"key": "y", "value": 32, "id": 0} + + + +
+ + + + The result type of datetime() and + datetime(template) + methods can be date, timetz, time, + timestamptz, or timestamp. + Both methods determine the result type dynamically. + + + The datetime() method sequentially tries ISO formats + for date, timetz, time, + timestamptz, and timestamp. It stops on + the first matching format and the corresponding data type. + + + The datetime(template) + method determines the result type by the provided template string. + + + The datetime() and + datetime(template) methods + use the same parsing rules as to_timestamp SQL + function does (see ) with three + exceptions. At first, these methods doesn't allow unmatched template + patterns. At second, only following separators are allowed in the + template string: minus sign, period, solidus, comma, apostrophe, + semicolon, colon and space. At third, separators in the template string + must exactly match the input string. + + + + + <type>jsonpath</type> Filter Expression Elements + + + + Value/Predicate + Description + Example JSON + Example Query + Result + + + + + == + Equality operator + [1, 2, 1, 3] + $[*] ? (@ == 1) + 1, 1 + + + != + Non-equality operator + [1, 2, 1, 3] + $[*] ? (@ != 1) + 2, 3 + + + <> + Non-equality operator (same as !=) + [1, 2, 1, 3] + $[*] ? (@ <> 1) + 2, 3 + + + < + Less-than operator + [1, 2, 3] + $[*] ? (@ < 2) + 1 + + + <= + Less-than-or-equal-to operator + [1, 2, 3] + $[*] ? (@ <= 2) + 1, 2 + + + > + Greater-than operator + [1, 2, 3] + $[*] ? (@ > 2) + 3 + + + >= + Greater-than-or-equal-to operator + [1, 2, 3] + $[*] ? (@ >= 2) + 2, 3 + + + true + Value used to perform comparison with JSON true literal + [{"name": "John", "parent": false}, + {"name": "Chris", "parent": true}] + $[*] ? (@.parent == true) + {"name": "Chris", "parent": true} + + + false + Value used to perform comparison with JSON false literal + [{"name": "John", "parent": false}, + {"name": "Chris", "parent": true}] + $[*] ? (@.parent == false) + {"name": "John", "parent": false} + + + null + Value used to perform comparison with JSON null value + [{"name": "Mary", "job": null}, + {"name": "Michael", "job": "driver"}] + $[*] ? (@.job == null) .name + "Mary" + + + && + Boolean AND + [1, 3, 7] + $[*] ? (@ > 1 && @ < 5) + 3 + + + || + Boolean OR + [1, 3, 7] + $[*] ? (@ < 1 || @ > 5) + 7 - - - jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb, silent bool]) - - - jsonb_path_query_array_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) - - - jsonb - - Gets all JSON items returned by JSON path for the specified JSON - value and wraps result into an array. - - - - jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') - - - - [2, 3, 4] - + ! + Boolean NOT + [1, 3, 7] + $[*] ? (!(@ < 5)) + 7 + like_regex - - jsonb_path_query_first(target jsonb, path jsonpath [, vars jsonb, silent bool]) - - - jsonb_path_query_first_tz(target jsonb, path jsonpath [, vars jsonb, silent bool]) - - - jsonb - - Gets the first JSON item returned by JSON path for the specified JSON - value. Returns NULL on no results. - - - - jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') - - - - 2 + Tests whether the first operand matches the regular expression + given by the second operand, optionally with modifications + described by a string of flag characters (see + ) + ["abc", "abd", "aBdC", "abdacb", "babc"] + $[*] ? (@ like_regex "^ab.*c" flag "i") + "abc", "aBdC", "abdacb" - - -
- - - - 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 type jsonb, because the conversion was already - done; but for json input, this may result in throwing an error, - as noted in . - - - - - - The functions - json[b]_populate_record, - json[b]_populate_recordset, - json[b]_to_record and - json[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: - - - - A JSON null value is converted to a SQL null in all cases. - - - - - If the output column is of type json - or jsonb, the JSON value is just reproduced exactly. - - - - - If the output column is a composite (row) type, and the JSON value is - a JSON object, the fields of the object are converted to columns of - the output row type by recursive application of these rules. - - - - - Likewise, if the output column is an array type and the JSON value is - a JSON array, the elements of the JSON array are converted to elements - of the output array by recursive application of these rules. - - - - - Otherwise, if the JSON value is a string literal, the contents of the - string are fed to the input conversion function for the column's data - type. - - - - - Otherwise, the ordinary text representation of the JSON value is fed - to the input conversion function for the column's data type. - - - - - - - While the examples for these functions use constants, the typical use - would be to reference a table in the FROM clause - and use one of its json or jsonb columns - as an argument to the function. Extracted key values can then be - referenced in other parts of the query, like WHERE - clauses and target lists. Extracting multiple values in this - way can improve performance over extracting them separately with - per-key operators. - - - - - - All the items of the path parameter of jsonb_set - as well as jsonb_insert except the last item must be present - in the target. If create_missing is false, all - items of the path parameter of jsonb_set must be - present. If these conditions are not met the target 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. - - - - - - The json_typeof function's null return value - should not be confused with a SQL NULL. While - calling json_typeof('null'::json) will - return null, calling json_typeof(NULL::json) - will return a SQL NULL. - - - - - - If the argument to json_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 for jsonb_strip_nulls since jsonb values never have - duplicate object field names. - - - - - - The jsonb_path_* functions have optional - vars and silent arguments. - - - If the vars argument is specified, it provides an - object containing named variables to be substituted into a - jsonpath expression. - - - If the silent argument is specified and has the - true value, these functions suppress the same errors - as the @? and @@ operators. - - - - - - Some of the jsonb_path_* functions have the - _tz suffix. These functions have been implemented to - support comparison of date/time values that involves implicit - timezone-aware casts. Since operations with time zones are not immutable, - these functions are qualified as stable. Their counterparts without the - suffix do not support such casts, so they are immutable and can be used for - such use-cases as expression indexes - (see ). There is no difference - between these functions for other jsonpath operations. - - - - - See also for the aggregate - function json_agg which aggregates record - values as JSON, and the aggregate function - json_object_agg which aggregates pairs of values - into a JSON object, and their jsonb equivalents, - jsonb_agg and jsonb_object_agg. - + + starts with + Tests whether the second operand is an initial substring of the first operand + ["John Smith", "Mary Stone", "Bob Johnson"] + $[*] ? (@ starts with "John") + "John Smith" + + + exists + Tests whether a path expression matches at least one SQL/JSON item + {"x": [1, 2], "y": [2, 4]} + strict $.* ? (exists (@ ? (@[*] > 2))) + 2, 4 + + + is unknown + Tests whether a Boolean condition is unknown + [-1, 2, 7, "infinity"] + $[*] ? ((@ > 0) is unknown) + "infinity" + + + + - + + + When different date/time values are compared, an implicit cast is + applied. A date value can be cast to timestamp + or timestamptz, timestamp can be cast to + timestamptz, and time — to timetz. + + + + -- 2.40.0