from the JSON data, similar to XPath expressions used

for SQL access to XML. In <productname>PostgreSQL</productname>,

path expressions are implemented as the <type>jsonpath</type>

data type, described in <xref linkend="datatype-jsonpath"/>.

data type and can use any elements described in

<xref linkend="datatype-jsonpath"/>.

</para>

<para>JSON query functions and operators

},

{ "location": [ 47.706, 13.2635 ],

"start time": "2018-10-14 10:39:21",

"HR":130

"HR":135

} ]

}

}

<para>

When defining the path, you can also use one or more

<firstterm>filter expressions</firstterm>, which work similar to

the <literal>WHERE</literal> clause in SQL. Each filter expression

can provide one or more filtering conditions that are applied

to the result of the path evaluation. Each filter expression must

be enclosed in parentheses and preceded by a question mark.

Filter expressions are evaluated from left to right and can be nested.

The <literal>@</literal> variable denotes the current path evaluation

result to be filtered, and can be followed by one or more accessor

operators to define the JSON element by which to filter the result.

Functions and operators that can be used in the filtering condition

are listed in <xref linkend="functions-sqljson-filter-ex-table"/>.

SQL/JSON defines three-valued logic, so the result of the filter

expression may be <literal>true</literal>, <literal>false</literal>,

<firstterm>filter expressions</firstterm> that work similar to the

<literal>WHERE</literal> clause in SQL. A filter expression begins with

a question mark and provides a condition in parentheses:

<programlisting>

? (<replaceable>condition</replaceable>)

</programlisting>

</para>

<para>

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 <literal>true</literal>, <literal>false</literal>,

or <literal>unknown</literal>. The <literal>unknown</literal> value

plays the same role as SQL <literal>NULL</literal>. Further path

plays the same role as SQL <literal>NULL</literal> and can be tested

for with the <literal>is unknown</literal> predicate. Further path

evaluation steps use only those items for which filter expressions

return true.

return <literal>true</literal>.

</para>

<para>

Functions and operators that can be used in filter expressions are listed

in <xref linkend="functions-sqljson-filter-ex-table"/>. The path

evaluation result to be filtered is denoted by the <literal>@</literal>

variable. To refer to a JSON element stored at a lower nesting level,

add one or more accessor operators after <literal>@</literal>.

</para>

<para>

<para>

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 is applied to the previous step and the pathin the filtering

condition is different:

filterexpressionis applied to the previous step, and the pathused

in thecondition is different:

<programlisting>

'$.track.segments[*] ? (@.HR &gt; 130)."start time"'

</programlisting>

</para>

<para>

You can also nestfilters within each other:

You can also nestfilter expressions within each other:

<programlisting>

'$.track ? (@.segments[*] ? (@.HR &gt; 130)).segments.size()'

'$.track ? (exists(@.segments[*] ? (@.HR &gt; 130))).segments.size()'

</programlisting>

This expression returns the size of the track if it contains any

segments with high heart rate values, or an empty sequence otherwise.

<row>

<entry><literal>@?</literal></entry>

<entry><type>jsonpath</type></entry>

<entry>Does JSON pathreturns any item for the specified JSON value?</entry>

<entry>Does JSON pathreturn any item for the specified JSON value?</entry>

<entry><literal>'{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'</literal></entry>

</row>

<row>

<note>

<para>

The <literal>@?</literal> and <literal>@@</literal> operators suppress

errors including: lacking object field or array element, unexpected JSON

item type and numeric errors.

the following errors: lacking object field or array element, unexpected

JSONitem type, and numeric errors.

This behavior might be helpful while searching over JSON document

collections of varying structure.

</para>

<literal>jsonb_path_query</literal>, <literal>jsonb_path_query_array</literal> and

<literal>jsonb_path_query_first</literal>

functions have optional <literal>vars</literal> and <literal>silent</literal>

argument.

arguments.

</para>

<para>

When<literal>vars</literal> argument is specified, itconstitutes an object

containedvariables to be substituted into<literal>jsonpath</literal>

expression.

If the<literal>vars</literal> argument is specified, itprovides an

object containing namedvariables to be substituted intoa

<literal>jsonpath</literal>expression.

</para>

<para>

When<literal>silent</literal> argument is specified and has

<literal>true</literal> value,the same errors are suppressed as it is in

the <literal>@?</literal> and <literal>@@</literal> operators.

If the<literal>silent</literal> argument is specified and has the

<literal>true</literal> value,these functions suppress the same errors

asthe <literal>@?</literal> and <literal>@@</literal> operators.

</para>

</note>

<literal>.**{<replaceable>level</replaceable>}</literal>

</para>

<para>

<literal>.**{<replaceable>lower_level</replaceable> to

<replaceable>upper_level</replaceable>}</literal>

</para>

<para>

<literal>.**{<replaceable>lower_level</replaceable> to

last}</literal>

<literal>.**{<replaceable>start_level</replaceable> to

<replaceable>end_level</replaceable>}</literal>

</para>

</entry>

<entry>

<para>

Same as <literal>.**</literal>, but with filter over nesting

level of JSON hierarchy. Levels are specified as integers.

Zero level corresponds to current object. This is a

<productname>PostgreSQL</productname> extension of the SQL/JSON

standard.

Same as <literal>.**</literal>, but with a filter over nesting

levels of JSON hierarchy. Nesting levels are specified as integers.

Zero level corresponds to the current object. To access the lowest

nesting level, you can use the <literal>last</literal> keyword.

This is a <productname>PostgreSQL</productname> extension of

the SQL/JSON standard.

</para>

</entry>

</row>

</entry>

<entry>

<para>

Array element accessor. <literal><replaceable>subscript</replaceable></literal>

might be given in two forms: <literal><replaceable>expr</replaceable></literal>

or <literal><replaceable>lower_expr</replaceable> to <replaceable>upper_expr</replaceable></literal>.

The first form specifies single array element by its index. The second

form specified array slice by the range of indexes. Zero index

corresponds to the first array element.

Array element accessor.

<literal><replaceable>subscript</replaceable></literal> can be

given in two forms: <literal><replaceable>index</replaceable></literal>

or <literal><replaceable>start_index</replaceable> to <replaceable>end_index</replaceable></literal>.

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

<replaceable>start_index</replaceable> and <replaceable>end_index</replaceable>.

</para>

<para>

An expression in the subscript may be an integer,

numeric expression, or any other <literal>jsonpath</literal> expression

returning single numeric value. The <literal>last</literal> keyword

can be used in the expression denoting the last subscript in an array.

That's helpful for handling arrays of unknown length.

The specified <replaceable>index</replaceable> can be an integer, as

well as an expression returning a single numeric value, which is

automatically cast to integer. Zero index corresponds to the first

array element. You can also use the <literal>last</literal> keyword

to denote the last array element, which is useful for handling arrays

of unknown length.

</para>

</entry>

</row>