public inbox for [email protected]
help / color / mirror / Atom feedFrom: David E. Wheeler <[email protected]>
To: [email protected]
Subject: Patch: Improve Boolean Predicate JSON Path Docs
Date: Sat, 14 Oct 2023 16:40:05 -0400
Message-ID: <[email protected]> (raw)
Hackers,
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. 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.
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 @@.
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().
Best,
David
[1] https://www.postgresql.org/message-id/1229727.1680535592%40sss.pgh.pa.us
Attachments:
[application/octet-stream] jsonpath-pred-docs.patch (10.2K, ../[email protected]/2-jsonpath-pred-docs.patch)
download | inline diff:
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 '{
"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>
</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 ? (@ > 130)
+select jsonb_path_query(:'json'::jsonb, '$.track.segments[*].HR ? (@ > 130)');
+ jsonb_path_query
+------------------
+ 135
</programlisting>
</para>
@@ -17312,7 +17328,10 @@ $.track.segments[*].HR ? (@ > 130)
filter expression is applied to the previous step, and the path used
in the condition is different:
<programlisting>
-$.track.segments[*] ? (@.HR > 130)."start time"
+ select jsonb_path_query(:'json'::jsonb, '$.track.segments[*] ? (@.HR > 130)."start time"');
+ jsonb_path_query
+-----------------------
+ "2018-10-14 10:39:21"
</programlisting>
</para>
@@ -17321,7 +17340,10 @@ $.track.segments[*] ? (@.HR > 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] < 13.4) ? (@.HR > 130)."start time"
+select jsonb_path_query(:'json'::jsonb, '$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"');
+ jsonb_path_query
+-----------------------
+ "2018-10-14 10:39:21"
</programlisting>
</para>
@@ -17330,46 +17352,81 @@ $.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 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] < 13.4).HR ? (@ > 130)
+select jsonb_path_query(:'json'::jsonb, $.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)');
+ jsonb_path_query
+------------------
+ 135
</programlisting>
</para>
<para>
You can also nest filter expressions within each other:
<programlisting>
-$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()
+select jsonb_path_query(:'json'::jsonb, $.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()');
+ 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>
+ <para>
+ <productname>PostgreSQL</productname>'s implementation of the SQL/JSON path
+ language has the following deviations from the SQL/JSON standard:
+ </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:
+ <literal>true</literal>, <literal>false</literal>, or
+ <literal>unknown</literal>. Compare this filter <type>jsonpath</type>
+ exression:
<programlisting>
-$.track.segments[*].HR < 70
+select jsonb_path_query(:'json'::jsonb, '$.track.segments ?(@[*].HR > 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 > 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>
+
+ <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="strict-and-lax-modes">
<title>Strict and Lax Modes</title>
@@ -17431,18 +17488,30 @@ $.track.segments[*].HR < 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
+-------------------
+ [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>
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Patch: Improve Boolean Predicate JSON Path Docs
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox