On 6/28/19 6:47 AM, Alexander Korotkov wrote:
On Tue, Jun 25, 2019 at 6:38 PM Liudmila Mantrova
<l.mantr...@postgrespro.ru> wrote:
Thank you for the catch! Please see the modified version of patch 0004
attached.
I tried to review and revise the part related to filters, but I failed
because I don't understand the notions used in the documentation.
What is the difference between filter expression and filter condition?
I can guess that filter expression contains question mark,
parentheses and filter condition inside. But this sentence is in
contradiction with my guess: "A filter expression must be enclosed in
parentheses and preceded by a question mark". So, filter expression
is inside the parentheses. Then what is filter condition? The same?
Each filter expression can provide one or more filters
that are applied to the result of the path evaluation.
So additionally to filter condition and filter expression we introduce
the notion of just filter. What is it? Could we make it without
introduction of new notion?
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Hi,
I have rechecked the standard and I agree that we should use "filter
expression" whenever possible.
"A filter expression must be enclosed in parentheses..." looks like an
oversight, so I fixed it. As for what's actually enclosed, I believe we
can still use the word "condition" here as it's easy to understand and
is already used in our docs, e.g. in description of the WHERE clause
that serves a similar purpose.
The new version of the patch fixes the terminology, tweaks the examples,
and provides some grammar and style fixes in the jsonpath-related chapters.
--
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3a8581d..b0de624 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11538,7 +11538,8 @@ table2-mapping
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
@@ -11585,7 +11586,7 @@ table2-mapping
},
{ "location": [ 47.706, 13.2635 ],
"start time": "2018-10-14 10:39:21",
- "HR": 130
+ "HR": 135
} ]
}
}
@@ -11637,23 +11638,33 @@ table2-mapping
<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. It can be followed by one or more accessor operators to define
+ the JSON element on a lower nesting level by which to filter the result.
</para>
<para>
@@ -11667,8 +11678,8 @@ table2-mapping
<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 path in the filtering
- condition is different:
+ filter expression is applied to the previous step, and the path used
+ in the condition is different:
<programlisting>
'$.track.segments[*] ? (@.HR > 130)."start time"'
</programlisting>
@@ -11693,9 +11704,9 @@ table2-mapping
</para>
<para>
- You can also nest filters within each other:
+ You can also nest filter expressions within each other:
<programlisting>
-'$.track ? (@.segments[*] ? (@.HR > 130)).segments.size()'
+'$.track ? (exists(@.segments[*] ? (@.HR > 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.
@@ -12285,7 +12296,7 @@ table2-mapping
<row>
<entry><literal>@?</literal></entry>
<entry><type>jsonpath</type></entry>
- <entry>Does JSON path returns any item for the specified JSON value?</entry>
+ <entry>Does JSON path return any item for the specified JSON value?</entry>
<entry><literal>'{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'</literal></entry>
</row>
<row>
@@ -12313,8 +12324,8 @@ table2-mapping
<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
+ JSON item type, and numeric errors.
This behavior might be helpful while searching over JSON document
collections of varying structure.
</para>
@@ -13170,17 +13181,17 @@ table2-mapping
<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, it constitutes an object
- contained variables to be substituted into <literal>jsonpath</literal>
- expression.
+ If the <literal>vars</literal> argument is specified, it provides an
+ object containing named variables to be substituted into a
+ <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
+ as the <literal>@?</literal> and <literal>@@</literal> operators.
</para>
</note>
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index daebb4f..0d8e2c6 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -815,21 +815,18 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
<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>
@@ -841,19 +838,22 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
</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>
- Expression inside subscript may consititue 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>
