--Postgres Json(b) int array contains any of array values SELECT ‘[1, 2, 3, 4]‘::jsonb @> ‘3‘::jsonb OR ‘[1, 2, 3, 4]‘::jsonb @> ‘5‘::jsonb as result; -- result -------- -- t --(1 row) with c(j) as (values(‘[1, 2, 3, 4]‘::jsonb)) , a as (select j,jsonb_array_elements(j) ja from c) select array_agg(ja), j, array_agg(ja::text::int) && array[5,4] from a group by j; -- array_agg | j | ?column? -----------+--------------+---------- -- {1,2,3,4} | [1, 2, 3, 4] | t --(1 row)
select "spon" ,("spon")::jsonb from request."Spr" where "spon"::jsonb @> ‘2105315577425011‘;
官网:9.16. JSON Functions and Operators
Operator Description Example(s) |
---|
Extracts
|
Extracts JSON object field with the given key.
|
Extracts
|
Extracts JSON object field with the given key, as
|
Extracts JSON sub-object at the specified path, where path elements can be either field keys or array indexes.
|
Extracts JSON sub-object at the specified path as
|
Operator Description Example(s) |
---|
Does the first JSON value contain the second? (See Section 8.14.3 for details about containment.)
|
Is the first JSON value contained in the second?
|
Does the text string exist as a top-level key or array element within the JSON value?
|
Do any of the strings in the text array exist as top-level keys or array elements?
|
Do all of the strings in the text array exist as top-level keys or array elements?
|
Concatenates two
To append an array to another array as a single entry, wrap it in an additional layer of array, for example:
|
Deletes a key (and its value) from a JSON object, or matching string value(s) from a JSON array.
|
Deletes all matching keys or array elements from the left operand.
|
Deletes the array element with specified index (negative integers count from the end). Throws an error if JSON value is not an array.
|
Deletes the field or array element at the specified path, where path elements can be either field keys or array indexes.
|
Does JSON path return any item for the specified JSON value?
|
Returns the result of a 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
|
Function Description Example(s) |
---|
Converts any SQL value to
|
Converts a SQL array to a JSON array. The behavior is the same as
|
Converts a SQL composite value to a JSON object. The behavior is the same as
|
Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list. Each argument is converted as per
|
Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values. Key arguments are coerced to text; value arguments are converted as per
|
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. All values are converted to JSON strings.
|
This form of
|
Function Description Example(s) |
---|
Expands the top-level JSON array into a set of JSON values.
value ----------- 1 true [2,false] |
Expands the top-level JSON array into a set of
value ----------- foo bar |
Returns the number of elements in the top-level JSON array.
|
Expands the top-level JSON object into a set of key/value pairs.
key | value -----+------- a | "foo" b | "bar" |
Expands the top-level JSON object into a set of key/value pairs. The returned
key | value -----+------- a | foo b | bar |
Extracts JSON sub-object at the specified path. (This is functionally equivalent to the
|
Extracts JSON sub-object at the specified path as
|
Returns the set of keys in the top-level JSON object.
json_object_keys ------------------ f1 f2 |
Expands the top-level JSON object to a row having the composite type of the To convert a JSON value to the SQL type of an output column, the following rules are applied in sequence:
While the example below uses a constant JSON value, typical use would be to reference a
a | b | c ---+-----------+------------- 1 | {2,"a b"} | (4,"a b c") |
Expands the top-level JSON array of objects to a set of rows having the composite type of the
a | b ---+--- 1 | 2 3 | 4 |
Expands the top-level JSON object to a row having the composite type defined by an
a | b | c | d | r ---+---------+---------+---+--------------- 1 | [1,2,3] | {1,2,3} | | (123,"a b c") |
Expands the top-level JSON array of objects to a set of rows having the composite type defined by an
a | b ---+----- 1 | foo 2 | |
Returns
|
If
|
Returns
|
Deletes all object fields that have null values from the given JSON value, recursively. Null values that are not object fields are untouched.
|
Checks whether the JSON path returns any item for the specified JSON value. If the
|
Returns the result of a 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
|
Returns all JSON items returned by the JSON path for the specified JSON value. The optional
jsonb_path_query ------------------ 2 3 4 |
Returns all JSON items returned by the JSON path for the specified JSON value, as a JSON array. The optional
|
Returns the first JSON item returned by the JSON path for the specified JSON value. Returns
|
These functions act like their counterparts described above without the
|
Converts the given JSON value to pretty-printed, indented text.
[ { "f1": 1, "f2": null }, 2 ] |
Returns the type of the top-level JSON value as a text string. Possible types are
|
{ "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 } ] } }
o retrieve the available track segments, you need to use the .
accessor operator to descend through surrounding JSON objects:key
$.track.segments
To retrieve the contents of an array, you typically use the [*]
operator. For example, the following path will return the 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. Recall that JSON array indexes 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 Section 9.16.2.2. Each method name must be preceded by a dot. For example, you can get the size of an array:
$.track.segments.size()
When defining a path, you can also use one or more filter expressions that work similarly to the WHERE
clause in SQL. A filter expression begins with a question mark and provides a condition in parentheses:
? (condition
)
For example, 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 times of segments with such values, you have to filter out irrelevant segments before returning the start times, 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 in sequence, if required. For example, the following expression selects start times of 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()
Operator/Method Description Example(s) |
---|
Addition
|
Unary plus (no operation); unlike addition, this can iterate over multiple values
|
Subtraction
|
Negation; unlike subtraction, this can iterate over multiple values
|
Multiplication
|
Division
|
Modulo (remainder)
|
Type of the JSON item (see
|
Size of the JSON item (number of array elements, or 1 if not an array)
|
Approximate floating-point number converted from a JSON number or string
|
Nearest integer greater than or equal to the given number
|
Nearest integer less than or equal to the given number
|
Absolute value of the given number
|
Date/time value converted from a string
|
Date/time value converted from a string using the specified
|
The object‘s key-value pairs, represented as an array of objects containing three fields:
|
Predicate/Value Description Example(s) |
---|
Equality comparison (this, and the other comparison operators, work on all JSON scalar values)
|
Non-equality comparison
|
Less-than comparison
|
Less-than-or-equal-to comparison
|
Greater-than comparison
|
Greater-than-or-equal-to comparison
|
JSON constant
|
JSON constant
|
JSON constant
|
Boolean AND
|
Boolean OR
|
Boolean NOT
|
Tests whether a Boolean condition is
|
Tests whether the first operand matches the regular expression given by the second operand, optionally with modifications described by a string of
|
Tests whether the second operand is an initial substring of the first operand.
|
Tests whether a path expression matches at least one SQL/JSON item. Returns
|
9.16.2.3. SQL/JSON Regular Expressions
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 Section 9.7.3. This leads to various minor discrepancies from standard SQL/JSON behavior, which are cataloged in Section 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 of like_regex
is a JSON path string literal, written according to the rules given in Section 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 strings that contain only digits:
$ ? (@ like_regex "^\\d+$")