Re: Patch: Improve Boolean Predicate JSON Path Docs

On 2023-10-14 22:40 +0200, David E. Wheeler write:
> Following up from a suggestion from Tom Lane[1] to improve the
> documentation of boolean predicate JSON path expressions, please find
> enclosed a draft patch to do so.

Thanks for putting this together. See my review at the end.

> It does three things:
>
> 1. Converts all of the example path queries to use jsonb_path_query()
> and show the results, to make it clearer what the behaviors are.

Nice. This really does help to make some sense of it. I checked all
queries and they do work out except for two queries where the path
expression string is not properly quoted (but the intended output is
still correct).

> 2. Replaces the list of deviations from the standards with a new
> subsection, with each deviation in its own sub-subsection. The regex
> section is unchanged, but I’ve greatly expanded the boolean expression
> JSON path section with examples comparing standard filter expressions
> and nonstandard boolean predicates. I’ve also added an exhortation not
> use boolean expressions with @? or standard path expressions with @@.

LGTM.

> 3. While converting the modes section to use jsonb_path_query() and
> show the results, I also added an example of strict mode returning an
> error.
>
> Follow-ups I’d like to make:
>
> 1. Expand the modes section to show how the types of results can vary
> depending on the mode, thanks to the flattening. Examples:
>
> david=# select jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a ?(@[*] > 2)');
> jsonb_path_query
> ------------------
> 3
> 4
> 5
> (3 rows)
>
> david=# select jsonb_path_query('{"a":[1,2,3,4,5]}', 'strict $.a ?(@[*] > 2)');
> jsonb_path_query
> ------------------
> [1, 2, 3, 4, 5]
>
> 2. Improve the descriptions and examples for @?/jsonb_path_exists()
> and @@/jsonb_path_match().

+1

> [1] https://www.postgresql.org/message-id/1229727.1680535592%40sss.pgh.pa.us

My review:

> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
> index affd1254bb..295f8ca5c9 100644
> --- a/doc/src/sgml/func.sgml
> +++ b/doc/src/sgml/func.sgml
> @@ -17205,7 +17205,7 @@ array w/o UK? | t
> For example, suppose you have some JSON data from a GPS tracker that you
> would like to parse, such as:
> <programlisting>
> -{
> + \set json '{

Perhaps make it explicit that the reader must run this in psql in order
to use \set and :'json' in the ensuing samples? Some of the existing
examples already use psql output but they do not rely on any psql
features.

> "track": {
> "segments": [
> {
> @@ -17220,7 +17220,7 @@ array w/o UK? | t
> }
> ]
> }
> -}
> +}'
> </programlisting>
> </para>
>
> @@ -17229,7 +17229,10 @@ array w/o UK? | t
> <literal>.<replaceable>key</replaceable></literal> accessor
> operator to descend through surrounding JSON objects:
> <programlisting>
> -$.track.segments
> +select jsonb_path_query(:'json'::jsonb, '$.track.segments');
> + jsonb_path_query
> +-------------------------------------------------------------------------------------------------------------------------------------------------------------------
> + [{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}]
> </programlisting>

This should use <screen>, <userinput>, and <computeroutput> if it shows
a psql session, e.g.:

<screen>
<userinput>select jsonb_path_query(:'json', '$.track.segments');</userinput>
<computeroutput>
jsonb_path_query
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}]
</computeroutput>
</screen>

Also the cast to jsonb is not necessary and only adds clutter IMO.

> </para>
>
> @@ -17239,7 +17242,11 @@ $.track.segments
> the following path will return the location coordinates for all
> the available track segments:
> <programlisting>
> -$.track.segments[*].location
> +select jsonb_path_query(:'json'::jsonb, '$.track.segments[*].location');
> + jsonb_path_query
> +-------------------
> + [47.763, 13.4034]
> + [47.706, 13.2635]
> </programlisting>
> </para>
>
> @@ -17248,7 +17255,10 @@ $.track.segments[*].location
> specify the corresponding subscript in the <literal>[]</literal>
> accessor operator. Recall that JSON array indexes are 0-relative:
> <programlisting>
> -$.track.segments[0].location
> +select jsonb_path_query(:'json'::jsonb, 'strict $.track.segments[0].location');
> + jsonb_path_query
> +-------------------
> + [47.763, 13.4034]
> </programlisting>
> </para>
>
> @@ -17259,7 +17269,10 @@ $.track.segments[0].location
> Each method name must be preceded by a dot. For example,
> you can get the size of an array:
> <programlisting>
> -$.track.segments.size()
> +select jsonb_path_query(:'json'::jsonb, 'strict $.track.segments.size()');
> + jsonb_path_query
> +------------------
> + 2
> </programlisting>
> More examples of using <type>jsonpath</type> operators
> and methods within path expressions appear below in
> @@ -17302,7 +17315,10 @@ $.track.segments.size()
> For example, suppose you would like to retrieve all heart rate values higher
> than 130. You can achieve this using the following expression:
> <programlisting>
> -$.track.segments[*].HR ? (@ &gt; 130)
> +select jsonb_path_query(:'json'::jsonb, '$.track.segments[*].HR ? (@ &gt; 130)');
> + jsonb_path_query
> +------------------
> + 135
> </programlisting>
> </para>
>
> @@ -17312,7 +17328,10 @@ $.track.segments[*].HR ? (@ &gt; 130)
> filter expression is applied to the previous step, and the path used
> in the condition is different:
> <programlisting>
> -$.track.segments[*] ? (@.HR &gt; 130)."start time"
> + select jsonb_path_query(:'json'::jsonb, '$.track.segments[*] ? (@.HR &gt; 130)."start time"');
> + jsonb_path_query
> +-----------------------
> + "2018-10-14 10:39:21"
> </programlisting>
> </para>
>
> @@ -17321,7 +17340,10 @@ $.track.segments[*] ? (@.HR &gt; 130)."start time"
> example, the following expression selects start times of all segments that
> contain locations with relevant coordinates and high heart rate values:
> <programlisting>
> -$.track.segments[*] ? (@.location[1] &lt; 13.4) ? (@.HR &gt; 130)."start time"
> +select jsonb_path_query(:'json'::jsonb, '$.track.segments[*] ? (@.location[1] &lt; 13.4) ? (@.HR &gt; 130)."start time"');
> + jsonb_path_query
> +-----------------------
> + "2018-10-14 10:39:21"
> </programlisting>
> </para>
>
> @@ -17330,46 +17352,81 @@ $.track.segments[*] ? (@.location[1] &lt; 13.4) ? (@.HR &gt; 130)."start time"
> The following example first filters all segments by location, and then
> returns high heart rate values for these segments, if available:
> <programlisting>
> -$.track.segments[*] ? (@.location[1] &lt; 13.4).HR ? (@ &gt; 130)
> +select jsonb_path_query(:'json'::jsonb, $.track.segments[*] ? (@.location[1] &lt; 13.4).HR ? (@ &gt; 130)');

The opening quote is missing from the jsonpath literal.

> + jsonb_path_query
> +------------------
> + 135
> </programlisting>
> </para>
>
> <para>
> You can also nest filter expressions within each other:
> <programlisting>
> -$.track ? (exists(@.segments[*] ? (@.HR &gt; 130))).segments.size()
> +select jsonb_path_query(:'json'::jsonb, $.track ? (exists(@.segments[*] ? (@.HR &gt; 130))).segments.size()');

Missing opening quote here as well.

> + jsonb_path_query
> +------------------
> + 2
> </programlisting>
> This expression returns the size of the track if it contains any
> segments with high heart rate values, or an empty sequence otherwise.
> </para>
>
> - <para>
> - <productname>PostgreSQL</productname>'s implementation of the SQL/JSON path
> - language has the following deviations from the SQL/JSON standard:
> - </para>
> + <sect3 id="devations-from-the-standard">
> + <title>Devaiations from the SQL Standard</title>

Typo in "deviations" (section ID and title).

> + <para>
> + <productname>PostgreSQL</productname>'s implementation of the SQL/JSON path
> + language has the following deviations from the SQL/JSON standard:

The sentence should and in a period when this para is no longer followed
by an item list.

> + </para>
>
> - <itemizedlist>
> - <listitem>
> + <sect4 id="boolean-predicate-path-expressions">
> + <title>Boolean Predicate Path Expressions</title>
> <para>
> - 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 <literal>@@</literal> operator. For example,
> - the following <type>jsonpath</type> expression is valid in
> - <productname>PostgreSQL</productname>:
> + As an extension to the SQL standard, a <productname>PostgreSQL</productname>
> + path expression can be a Boolean predicate, whereas the SQL standard allows
> + predicates only in filters. Where SQL standard path expressions return the
> + relevant contents of the queried JSON value, predicate path expressions
> + return the three-value three-valued result of the predicate:

Redundant "three-value" before "three-valued result".

> + <literal>true</literal>, <literal>false</literal>, or
> + <literal>unknown</literal>. Compare this filter <type>jsonpath</type>
> + exression:
> <programlisting>
> -$.track.segments[*].HR &lt; 70
> +select jsonb_path_query(:'json'::jsonb, '$.track.segments ?(@[*].HR &gt; 130)');
> + jsonb_path_query
> +---------------------------------------------------------------------------------
> + {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}
> </programlisting>
> - </para>
> - </listitem>
> + To a predicate expression, which returns <literal>true</literal>
> +<programlisting>
> +select jsonb_path_query(:'json'::jsonb, '$.track.segments[*].HR &gt; 130');
> + jsonb_path_query
> +------------------
> + true
> +</programlisting>
> + </para>
>
> - <listitem>
> - <para>
> - There are minor differences in the interpretation of regular
> - expression patterns used in <literal>like_regex</literal> filters, as
> - described in <xref linkend="jsonpath-regular-expressions"/>.
> - </para>
> - </listitem>
> - </itemizedlist>
> + <para>
> + Predicate-only path expressions are necessary for implementation of the
> + <literal>@@</literal> operator (and the
> + <function>jsonb_path_match</function> function), and should not be used
> + with the <literal>@?</literal> operator (or
> + <function>jsonb_path_exists</function> function).
> + </para>
> +
> + <para>
> + Conversely, non-predicate <type>jsonpath</type> expressions should not be
> + used with the <literal>@@</literal> operator (or the
> + <function>jsonb_path_match</function> function).
> + </para>
> + </sect4>

Both paras should be wrapped in a single <note> so that they stand out
from the rest of the text. Maybe even <warning>, but <note> is already
used on this page for things that I'd consider warnings.

> + <sect4 id="jsonpath-regular-expression-deviation">
> + <title>Regular Expression Interpretation</title>
> + <para>
> + There are minor differences in the interpretation of regular
> + expression patterns used in <literal>like_regex</literal> filters, as
> + described in <xref linkend="jsonpath-regular-expressions"/>.
> + </para>
> + </sect4>

<sect3 id="devations-from-the-standard"> should be closed here,
otherwise the docs won't build. This can be checked with
`make -C doc/src/sgml check`.

>
> <sect3 id="strict-and-lax-modes">
> <title>Strict and Lax Modes</title>
> @@ -17431,18 +17488,30 @@ $.track.segments[*].HR &lt; 70
> abstract from the fact that it stores an array of segments
> when using the lax mode:
> <programlisting>
> -lax $.track.segments.location
> + select jsonb_path_query(:'json'::jsonb, 'lax $.track.segments.location');
> + jsonb_path_query

`git diff --check` shows a couple of lines with trailing whitespace
(mostly psql output).

> +-------------------
> + [47.763, 13.4034]
> + [47.706, 13.2635]
> </programlisting>
> </para>
>
> <para>
> - In the strict mode, the specified path must exactly match the structure of
> + In 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
> + path expression will cause an error:
> +<programlisting>
> +select jsonb_path_query(:'json'::jsonb, 'strict $.track.segments.location');
> +ERROR: jsonpath member accessor can only be applied to an object
> +</programlisting>
> + To get the same result as in the lax mode, you have to explicitly unwrap the
> <literal>segments</literal> array:
> <programlisting>
> -strict $.track.segments[*].location
> +select jsonb_path_query(:'json'::jsonb, 'strict $.track.segments[*].location');
> + jsonb_path_query
> +-------------------
> + [47.763, 13.4034]
> + [47.706, 13.2635]
> </programlisting>
> </para>
>
> @@ -17451,7 +17520,13 @@ strict $.track.segments[*].location
> when using the lax mode. For instance, the following query selects every
> <literal>HR</literal> value twice:
> <programlisting>
> -lax $.**.HR
> +select jsonb_path_query(:'json'::jsonb, 'lax $.**.HR');
> + jsonb_path_query
> +------------------
> + 73
> + 135
> + 73
> + 135
> </programlisting>
> This happens because the <literal>.**</literal> accessor selects both
> the <literal>segments</literal> array and each of its elements, while
> @@ -17460,7 +17535,11 @@ lax $.**.HR
> the <literal>.**</literal> accessor only in the strict mode. The
> following query selects each <literal>HR</literal> value just once:
> <programlisting>
> -strict $.**.HR
> +select jsonb_path_query(:'json'::jsonb, 'strict $.**.HR');
> + jsonb_path_query
> +------------------
> + 73
> + 135
> </programlisting>
> </para>
>

--
Erik