Re: Patch: Improve Boolean Predicate JSON Path Docs
On Jan 25, 2024, at 11:03, Tom Lane wrote: > I changed the preceding para to say "... check expressions are > required in ...", which I thought was sufficient to cover that. > Also, the tabular description of the operator tells you not to do it. Yeah, that’s good. I was perhaps leaning into being over-explicit after it took me a while to even figure out that there was a difference, let alone where matters. >> What do you think of also dropping the article from all the references to >> “the strict mode” or “the lax mode”, to make them “strict mode” and “lax >> mode”, respectively? > > Certainly most of 'em don't need it. I'll make it so. Nice, thanks! David
Re: Patch: Improve Boolean Predicate JSON Path Docs
"David E. Wheeler" writes: > On Jan 24, 2024, at 16:32, Tom Lane wrote: >> + >> + Predicate check expressions are required in the >> + @@ operator (and the >> + jsonb_path_match function), and should not be >> used >> + with the @? operator (or the >> + jsonb_path_exists function). >> + >> + >> + > I had this bit here: > >Conversely, non-predicate jsonpath expressions should not > be >used with the @@ operator (or the >jsonb_path_match function). > I changed the preceding para to say "... check expressions are required in ...", which I thought was sufficient to cover that. Also, the tabular description of the operator tells you not to do it. > What do you think of also dropping the article from all the references to > “the strict mode” or “the lax mode”, to make them “strict mode” and “lax > mode”, respectively? Certainly most of 'em don't need it. I'll make it so. regards, tom lane
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Jan 24, 2024, at 16:32, Tom Lane wrote: > "David E. Wheeler" writes: > >> In any event, something to do with @@, perhaps to have some compatibility >> with `jsonb @> jsonb`? I don’t know why @@ was important to have. > > Yeah, that's certainly under-explained. But it seems like I'm not > getting traction for the idea of changing the behavior, so let's > go back to just documenting it. Curious about those discussions. On the one hand I find the distinction between the two behaviors to be odd, and to produce unexpected results when they’re not used in the proper context. It’s reminds me of the Perl idea of context, where functions behave differently in scalar and list context, and if you expect list behavior on scalar context you’re gonna get a surprise. This is a bit of a challenge for those new to the language, as they’re not necessarily aware of the context. > I spent some time going over your > text and also cleaning up nearby shaky English, and ended with v8 > attached. I'd be content to commit this if it looks good to you. This looks very nice, thank you. A couple of comments. > + > + Predicate check expressions are required in the > + @@ operator (and the > + jsonb_path_match function), and should not be > used > + with the @? operator (or the > + jsonb_path_exists function). > + > + > + I had this bit here: Conversely, non-predicate jsonpath expressions should not be used with the @@ operator (or the jsonb_path_match function). I think it’s important to let people know what the difference is in the behavior of the two forms, in every spot it’s likely to come up. SQL-standard JSON Path expressions should never be used in contexts (functions, operators) only designed to work with predicate check expressions, and the docs should say so IMO. > > -The lax mode facilitates matching of a JSON document structure and path > -expression if the JSON data does not conform to the expected schema. > +The lax mode facilitates matching of a JSON document and path > +expression when the JSON data does not conform to the expected schema. What do you think of also dropping the article from all the references to “the strict mode” or “the lax mode”, to make them “strict mode” and “lax mode”, respectively? Thanks for the review! Best, David
Re: Patch: Improve Boolean Predicate JSON Path Docs
"David E. Wheeler" writes:
> In any event, something to do with @@, perhaps to have some compatibility
> with `jsonb @> jsonb`? I don’t know why @@ was important to have.
Yeah, that's certainly under-explained. But it seems like I'm not
getting traction for the idea of changing the behavior, so let's
go back to just documenting it. I spent some time going over your
text and also cleaning up nearby shaky English, and ended with v8
attached. I'd be content to commit this if it looks good to you.
regards, tom lane
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5030a1045f..99616d2298 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15889,6 +15889,9 @@ table2-mapping
Does JSON path return any item for the specified JSON value?
+(This is useful only with SQL-standard JSON path expressions, not
+predicate check
+expressions, since those always return a value.)
'{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'
@@ -15903,9 +15906,12 @@ table2-mapping
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 NULL
-is returned.
+specified JSON value.
+(This is useful only
+with predicate
+check expressions, not SQL-standard JSON path expressions,
+since it will return NULL if the path result is
+not a single boolean value.)
'{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'
@@ -17029,6 +17035,9 @@ ERROR: value too long for type character(2)
Checks whether the JSON path returns any item for the specified JSON
value.
+(This is useful only with SQL-standard JSON path expressions, not
+predicate check
+expressions, since those always return a value.)
If the vars argument is specified, it must
be a JSON object, and its fields provide named values to be
substituted into the jsonpath expression.
@@ -17052,8 +17061,12 @@ ERROR: value too long for type character(2)
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 NULL is returned.
+JSON value.
+(This is useful only
+with predicate
+check expressions, not SQL-standard JSON path expressions,
+since it will either fail or return NULL if the
+path result is not a single boolean value.)
The optional vars
and silent arguments act the same as
for jsonb_path_exists.
@@ -17075,6 +17088,12 @@ ERROR: value too long for type character(2)
Returns all JSON items returned by the JSON path for the specified
JSON value.
+For SQL-standard JSON path expressions it returns the JSON
+values selected from target.
+For predicate
+check expressions it returns the result of the predicate
+check: true, false,
+or null.
The optional vars
and silent arguments act the same as
for jsonb_path_exists.
@@ -17103,9 +17122,8 @@ ERROR: value too long for type character(2)
Returns all JSON items returned by the JSON path for the specified
JSON value, as a JSON array.
-The optional vars
-and silent arguments act the same as
-for jsonb_path_exists.
+The parameters are the same as
+for jsonb_path_query.
jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min @ = $max)', '{"min":2, "max":4}')
@@ -17123,11 +17141,10 @@ ERROR: value too long for type character(2)
Returns the first JSON item returned by the JSON path for the
-specified JSON value. Returns NULL if there are no
+specified JSON value, or NULL if there are no
results.
-The optional vars
-and silent arguments act the same as
-for jsonb_path_exists.
+The parameters are the same as
+for jsonb_path_query.
jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min @ = $max)', '{"min":2, "max":4}')
@@ -17266,9 +17283,9 @@ ERROR: value too long for type character(2)
- SQL/JSON path expressions specify the items to be retrieved
- from the JSON data, similar to XPath expressions used
- for SQL access to XML. In PostgreSQL,
+ SQL/JSON path expressions specify item(s) to be retrieved
+ from a JSON value, similarly to XPath expressions used
+ for access to XML content. In PostgreSQL,
path expressions are implemented as the jsonpath
data type and can use any elements described in
.
@@ -17279,6 +17296,8 @@
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Jan 21, 2024, at 14:58, David E. Wheeler wrote: > I make this interpretation based on this bit of the docs: Sorry, that’s from my branch. Here it is in master: 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 @@ operator. For example, the following jsonpath expression is valid in PostgreSQL: $.track.segments[*].HR 70 In any event, something to do with @@, perhaps to have some compatibility with `jsonb @> jsonb`? I don’t know why @@ was important to have. David
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Jan 21, 2024, at 14:52, David E. Wheeler wrote:
> This is the only way the different behaviors make sense to me. @? expects a
> set, not a boolean, sees there is an item in the set, so returns true:
I make this interpretation based on this bit of the docs:
PostgreSQL's implementation of the SQL/JSON path
language has the following deviations from the SQL/JSON standard.
Boolean Predicate Check Expressions
As an extension to the SQL standard, a PostgreSQL
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 check expressions
return the three-valued result of the predicate: true,
false, or unknown. Compare this
filter jsonpath expression:
= select jsonb_path_query(:'json',
'$.track.segments ?(@[*].HR 130)');
jsonb_path_query
-
{"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}
To a predicate expression, which returns true
= select jsonb_path_query(:'json',
'$.track.segments[*].HR 130');
jsonb_path_query
--
true
Best,
David
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Jan 21, 2024, at 14:43, Tom Lane wrote: > I don't entirely buy this argument --- if that is the interpretation, > of what use are predicate check expressions? It seems to me that we > have to consider them as being a shorthand notation for filter > expressions, or else they simply do not make sense as jsonpath. I believe it becomes pretty apparent when using jsonb_path_query(). The filter expression returns a set (using the previous \gset example): david=# select jsonb_path_query(:'json', '$.track.segments[*].HR ? (@ > 10)'); jsonb_path_query -- 73 135 (2 rows) The predicate check returns a boolean: david=# select jsonb_path_query(:'json', '$.track.segments[*].HR > 10'); jsonb_path_query -- true (1 row) This is the only way the different behaviors make sense to me. @? expects a set, not a boolean, sees there is an item in the set, so returns true: david=# select jsonb_path_query(:'json', '$.track.segments[*].HR > 1000'); jsonb_path_query -- false (1 row) david=# select :'json'::jsonb @? '$.track.segments[*].HR > 1000'; ?column? -- t (1 row) Best, David
Re: Patch: Improve Boolean Predicate JSON Path Docs
"David E. Wheeler" writes:
> On Jan 20, 2024, at 12:34, Tom Lane wrote:
>> It will take a predicate, but seems to always return true:
>>
>> regression=# select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] < 5' ;
>> ?column?
>> --
>> t
>> (1 row)
>>
>> regression=# select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] > 5' ;
>> ?column?
>> --
>> t
>> (1 row)
> Just for the sake of clarity, this return value is “correct,” because @? and
> other functions and operators that expect SQL standard statements evaluate
> the SET returned by the JSONPath statement, but predicate check expressions
> don’t return a set, but a always a single scalar value (true, false, or
> null). From the POV of the code expecting SQL standard JSONPath results,
> that’s a set of one. @? sees that the set is not empty so returns true.
I don't entirely buy this argument --- if that is the interpretation,
of what use are predicate check expressions? It seems to me that we
have to consider them as being a shorthand notation for filter
expressions, or else they simply do not make sense as jsonpath.
regards, tom lane
Re: Patch: Improve Boolean Predicate JSON Path Docs
"David E. Wheeler" writes:
> On Jan 20, 2024, at 12:34, Tom Lane wrote:
>> Surely we're not helping anybody by leaving that behavior in place.
>> Making it do something useful, throwing an error, or returning NULL
>> all seem superior to this. I observe that @@ returns NULL for the
>> path type it doesn't like, so maybe that's what to do here.
> I agree it would be far better for the behavior to be consistent, but frankly
> would like to see them raise an error. Ideally the hit would suggest the
> proper alternative operator or function to use, and maybe link to the docs
> that describe the difference between SQL-standard JSONPath and "predicate
> check expressions”, and how they have separate operators and functions.
That ship's probably sailed. However, I spent some time poking into
the odd behavior I showed for @?, and it seems to me that it's an
oversight in appendBoolResult. That just automatically returns jperOk
in the !found short-circuit path for any boolean result, which is not
the behavior you'd get if the boolean value were actually returned
(cf. jsonb_path_match_internal). I experimented with making it do
what seems like the right thing, and found that there is only one
regression test case that changes behavior:
select jsonb '2' @? '$ == "2"';
?column?
--
- t
+ f
(1 row)
Now, JSON does not think that numeric 2 equals string "2", so
ISTM the expected output here is flat wrong. It's certainly
inconsistent with @@:
regression=# select jsonb '2' @@ '$ == "2"';
?column?
--
(1 row)
So I think we should consider a patch like the attached
(probably with some more test cases added). I don't really
understand this code however, so maybe I missed something.
regards, tom lane
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index ac16f5c85d..63c46cfab5 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -2065,7 +2065,14 @@ appendBoolResult(JsonPathExecContext *cxt, JsonPathItem *jsp,
JsonbValue jbv;
if (!jspGetNext(jsp, ) && !found)
- return jperOk; /* found singleton boolean value */
+ {
+ /*
+ * We have a predicate check expression, i.e. a path ending in a bare
+ * boolean operator, and we don't need to return the exact value(s)
+ * found. Just report success or failure of the boolean.
+ */
+ return (res == jpbTrue) ? jperOk : jperNotFound;
+ }
if (res == jpbUnknown)
{
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 6659bc9091..59e1b71051 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -1157,7 +1157,7 @@ select jsonb_path_query('2', '$ == "2"');
select jsonb '2' @? '$ == "2"';
?column?
--
- t
+ f
(1 row)
select jsonb '2' @@ '$ > 1';
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Jan 20, 2024, at 12:34, Tom Lane wrote:
> It will take a predicate, but seems to always return true:
>
> regression=# select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] < 5' ;
> ?column?
> --
> t
> (1 row)
>
> regression=# select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] > 5' ;
> ?column?
> --
> t
> (1 row)
Just for the sake of clarity, this return value is “correct,” because @? and
other functions and operators that expect SQL standard statements evaluate the
SET returned by the JSONPath statement, but predicate check expressions don’t
return a set, but a always a single scalar value (true, false, or null). From
the POV of the code expecting SQL standard JSONPath results, that’s a set of
one. @? sees that the set is not empty so returns true.
Best,
David
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Jan 20, 2024, at 11:45, Tom Lane wrote:
> You sure about that? It would surprise me if we could effectively use
> a not-equal condition with an index. If it is only == that works,
> then the preceding statement seems sufficient.
I’m not! I just assumed it in the same way creating an SQL = operator
automatically respects NOT syntax (or so I recall). In fiddling a bit, I can’t
get it to use an index:
CREATE TABLE MOVIES (id SERIAL PRIMARY KEY, movie JSONB NOT NULL);
\copy movies(movie) from PROGRAM 'curl -s
https://raw.githubusercontent.com/prust/wikipedia-movie-data/master/movies.json
| jq -c ".[]" | sed "s|||g"';
create index on movies using gin (movie);
analyze movies;
david=# explain analyze select id from movies where movie @? '$ ?(@.genre[*] !=
"Teen")';
QUERY PLAN
-
Seq Scan on movies (cost=0.00..3741.41 rows=4 width=4) (actual
time=19.222..19.223 rows=0 loops=1)
Filter: (movie @? '$?(@."genre"[*] != "Teen")'::jsonpath)
Rows Removed by Filter: 36273
Planning Time: 1.242 ms
Execution Time: 19.247 ms
(5 rows)
But that might be because the planner knows that the query is going to fetch
most records, anyway. If I set most records to a single value:
david=# update movies set movie = jsonb_set(movie, '{year}', '2020'::jsonb)
where id < 3600;
UPDATE 3599
david=# analyze movies;
ANALYZE
david=# explain analyze select id from movies where movie @? '$ ?(@.year !=
2020)';
QUERY PLAN
Seq Scan on movies (cost=0.00..3884.41 rows=32609 width=4) (actual
time=0.065..43.730 rows=32399 loops=1)
Filter: (movie @? '$?(@."year" != 2020)'::jsonpath)
Rows Removed by Filter: 3874
Planning Time: 1.759 ms
Execution Time: 45.368 ms
(5 rows)
Looks like it still doesn’t use the index with !=. Pity.
Best,
David
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Jan 20, 2024, at 12:34, Tom Lane wrote: > Surely we're not helping anybody by leaving that behavior in place. > Making it do something useful, throwing an error, or returning NULL > all seem superior to this. I observe that @@ returns NULL for the > path type it doesn't like, so maybe that's what to do here. I agree it would be far better for the behavior to be consistent, but frankly would like to see them raise an error. Ideally the hit would suggest the proper alternative operator or function to use, and maybe link to the docs that describe the difference between SQL-standard JSONPath and "predicate check expressions”, and how they have separate operators and functions. I think of them as practically different data types (and wish they were, TBH). It makes sense that passing a JSON containment expression[1] would raise an error; so should passing the wrong flavor of JSONPath. > BTW, jsonb_path_query_array and jsonb_path_query_first seem to > take both types of path, like jsonb_path_query, so ISTM they need > docs changes too. Happy to update the patch, either to add those docs or, if we change the behavior to return a NULL or raise an error, then with that information, instead. Best, David [1]: https://www.postgresql.org/docs/current/datatype-json.html#JSON-CONTAINMENT
Re: Patch: Improve Boolean Predicate JSON Path Docs
So, overall reaction to this patch: I like the approach of defining
"predicate check expressions" as being a different thing from standard
jsonpath expressions. However, I'm not so thrilled with just saying
"don't use" one type or the other with different jsonpath functions.
According to my tests, some of these functions seem to give sensible
results anyway with the path type you say not to use, while some
give less-sensible results, and others give errors. We ought to try
to document that, and maybe even clean up the less sane behaviors.
(That is, I don't feel that a docs-only patch is necessarily the
thing to do here.)
As an example, @? seems to behave sanely with a standard jsonpath:
regression=# select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ < 5)' ;
?column?
--
t
(1 row)
regression=# select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 5)' ;
?column?
--
f
(1 row)
It will take a predicate, but seems to always return true:
regression=# select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] < 5' ;
?column?
--
t
(1 row)
regression=# select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] > 5' ;
?column?
--
t
(1 row)
Surely we're not helping anybody by leaving that behavior in place.
Making it do something useful, throwing an error, or returning NULL
all seem superior to this. I observe that @@ returns NULL for the
path type it doesn't like, so maybe that's what to do here.
(Unsurprisingly, jsonb_path_exists acts similarly.)
BTW, jsonb_path_query_array and jsonb_path_query_first seem to
take both types of path, like jsonb_path_query, so ISTM they need
docs changes too.
regards, tom lane
Re: Patch: Improve Boolean Predicate JSON Path Docs
"David E. Wheeler" writes: > While you’re in there, Tom, would it make sense to fold in something like > [this patch][1] I posted last month to clarify which JSONPath comparison > operators can take advantage of a index? > --- a/doc/src/sgml/json.sgml > +++ b/doc/src/sgml/json.sgml > @@ -513,7 +513,7 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ > '$.tags[*] == "qui"'; > > For these operators, a GIN index extracts clauses of the form > accessors_chain > -= constant out of > +== constant out of > the jsonpath pattern, and does the index search based on > the keys and values mentioned in these clauses. The accessors chain > may include .key, Right, clearly a typo. > @@ -522,6 +522,9 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ > '$.tags[*] == "qui"'; > The jsonb_ops operator class also > supports .* and .** accessors, > but the jsonb_path_ops operator class does not. > +Only the == and != +linkend="functions-sqljson-path-operators">SQL/JSON Path Operators > +can use the index. > You sure about that? It would surprise me if we could effectively use a not-equal condition with an index. If it is only == that works, then the preceding statement seems sufficient. regards, tom lane
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Jan 19, 2024, at 21:46, Erik Wienhold wrote:
> Interesting... copy-pasting the entire \set command works for me with
> psql 16.1 in gnome-terminal and tmux. Typing it out manually gives me
> the "unterminated quoted string" error. Maybe has to do with my stty
> settings.
Yes, same on macOS Terminal.app and 16.1 compiled with readline. I didn’t
realize that \set didn’t support newlines, because it works fine when you paste
something with newlines. Curious.
>> I experimented with
>>
>> SELECT '
>> ... multiline json value ...
>> ' AS json
>> \gexec
>>
>> but that didn't seem to work either. Anybody have a better idea?
>
> Fine with me (the \gset variant).
Much cleaner TBH.
david=# select '{
"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
}
]
}
}'::jsonb as json;
json
{"track": {"segments": [{"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"}]}}
(1 row)
david=# \gset
david=# select :'json'::jsonb;
jsonb
{"track": {"segments": [{"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"}]}}
(1 row)
So great!
While you’re in there, Tom, would it make sense to fold in something like [this
patch][1] I posted last month to clarify which JSONPath comparison operators
can take advantage of a index?
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -513,7 +513,7 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@
'$.tags[*] == "qui"';
For these operators, a GIN index extracts clauses of the form
accessors_chain
-= constant out of
+== constant out of
the jsonpath pattern, and does the index search based on
the keys and values mentioned in these clauses. The accessors chain
may include .key,
@@ -522,6 +522,9 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@
'$.tags[*] == "qui"';
The jsonb_ops operator class also
supports .* and .** accessors,
but the jsonb_path_ops operator class does not.
+Only the == and != SQL/JSON Path Operators
+can use the index.
Best,
David
[1]:
https://www.postgresql.org/message-id/0ece6b9c-cdde-4b65-be5a-49d737204...@justatheory.com
Re: Re: Patch: Improve Boolean Predicate JSON Path Docs
On 2024-01-19 22:15 +0100, Tom Lane wrote:
> "David E. Wheeler" writes:
> > [ v7-0001-Improve-boolean-predicate-JSON-Path-docs.patch ]
>
> + \set json '{
>"track": {
> "segments": [
>{
>
> I find the textual change rather unwieldy, but the bigger problem is
> that this example doesn't actually work. If you try to copy-and-paste
> this into psql, you get "unterminated quoted string", because psql
> metacommands can't span line boundaries.
Interesting... copy-pasting the entire \set command works for me with
psql 16.1 in gnome-terminal and tmux. Typing it out manually gives me
the "unterminated quoted string" error. Maybe has to do with my stty
settings.
> I experimented with
>
> SELECT '
> ... multiline json value ...
> ' AS json
> \gexec
>
> but that didn't seem to work either. Anybody have a better idea?
Fine with me (the \gset variant).
--
Erik
Re: Patch: Improve Boolean Predicate JSON Path Docs
I wrote: > I experimented with > SELECT ' > ... multiline json value ... > ' AS json > \gexec > but that didn't seem to work either. Anybody have a better idea? Oh, never mind, \gset is what I was reaching for. We can make it work with that. regards, tom lane
Re: Patch: Improve Boolean Predicate JSON Path Docs
"David E. Wheeler" writes:
> [ v7-0001-Improve-boolean-predicate-JSON-Path-docs.patch ]
I started to review this, and got bogged down at
@@ -17203,9 +17214,12 @@ 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:
+ would like to parse, such as this JSON, set up as a
+ psql
+ \set variable for use as
:'json'
+ in the examples below:
-{
+ \set json '{
"track": {
"segments": [
{
I find the textual change rather unwieldy, but the bigger problem is
that this example doesn't actually work. If you try to copy-and-paste
this into psql, you get "unterminated quoted string", because psql
metacommands can't span line boundaries.
Perhaps we could leave the existing display alone, and then add
To follow the examples below, paste this into psql:
\set json '{ "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 } ] }}'
This will allow :'json' to be expanded into the
above JSON value, plus suitable quoting.
However, I'm not sure that's a great solution, because it's going to
line-wrap on most displays, making copy-and-paste a bit iffy.
I experimented with
SELECT '
... multiline json value ...
' AS json
\gexec
but that didn't seem to work either. Anybody have a better idea?
regards, tom lane
Re: Patch: Improve Boolean Predicate JSON Path Docs
The following review has been posted through the commitfest application: make installcheck-world: not tested Implements feature: not tested Spec compliant: not tested Documentation:tested, passed I took a look for this commit, it looks correct to me
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Oct 23, 2023, at 20:20, Erik Wienhold wrote: > I thought that you may have missed that one because I saw this change > that removes the article: > >> -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 Oh, didn’t realize. Fixed. > LGTM. Would you create a commitfest entry? I'll set the status to RfC. Done. https://commitfest.postgresql.org/45/4624/ Best, David v7-0001-Improve-boolean-predicate-JSON-Path-docs.patch Description: Binary data
Re: Patch: Improve Boolean Predicate JSON Path Docs
On 2023-10-24 00:58 +0200, David E. Wheeler wrote: > On Oct 22, 2023, at 20:36, Erik Wienhold wrote: > > > That's an AppleSingle file according to [1][2]. It only contains the > > resource fork and file name but no data fork. > > Ah, I had “Send large attachments with Mail Drop” enabled. To me 20K > is not big but whatever. Let’s see if turning it off fixes the issue. I suspected it had something to do with iCloud. Glad you solved it! > > Please change to "in strict mode" (without "the"). > > Hrm, I prefer it without the article, too, but it is consistently used > that way elsewhere, like here: > > > https://github.com/postgres/postgres/blob/5b36e8f/doc/src/sgml/func.sgml#L17401 > > I’d be happy to change them all, but was keeping it consistent for now. Right. I haven't really noticed that the article case is more common. I thought that you may have missed that one because I saw this change that removes the article: > -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 > Updated patch attached, thank you! LGTM. Would you create a commitfest entry? I'll set the status to RfC. -- Erik
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Oct 22, 2023, at 20:36, Erik Wienhold wrote: > That's an AppleSingle file according to [1][2]. It only contains the > resource fork and file name but no data fork. Ah, I had “Send large attachments with Mail Drop” enabled. To me 20K is not big but whatever. Let’s see if turning it off fixes the issue. > Any reason for calling it "predicate check expressions" (e.g. the link > text) and sometimes "predicate path expressions" (e.g. the linked > section title)? I think it should be named consistently to avoid > confusion and also to simplify searching. I think "predicate path expressions” is more descriptive, but "predicate check expressions” is what was in the docs before, so let’s stick with that. > Linking the same section twice in the same paragraph seems excessive. Fair. Will link the second one. >> += select jsonb_path_query(:'json', >> '$.track.segments'); >> +select jsonb_path_query(:'json', '$.track.segments'); > > Please remove the second SELECT. Done. >> += select jsonb_path_query(:'json', 'strict >> $.track.segments[0].location'); >> + jsonb_path_query >> +--- >> + [47.763, 13.4034] > > Strict mode is unnecessary to get that result and I'd omit it because > the different modes are not introduced yet at this point. Yep, pasto. > Strict mode is unnecessary here as well. Fixed. >> + using the lax mode. To avoid surprising results, we recommend using >> + the .** accessor only in the strict mode. The > > Please change to "in strict mode" (without "the"). Hrm, I prefer it without the article, too, but it is consistently used that way elsewhere, like here: https://github.com/postgres/postgres/blob/5b36e8f/doc/src/sgml/func.sgml#L17401 I’d be happy to change them all, but was keeping it consistent for now. Updated patch attached, thank you! David v6-0001-Improve-boolean-predicate-JSON-Path-docs.patch Description: Binary data
Re: Patch: Improve Boolean Predicate JSON Path Docs
On 2023-10-20 15:49 +0200, David Wheeler wrote: > On Oct 19, 2023, at 23:49, Erik Wienhold wrote: > > > I don't even know what that represents, probably not some fancy file > > compression. That's an AppleSingle file according to [1][2]. It only contains the resource fork and file name but no data fork. > Oh, weird. Trying from a webmail client instead. Thanks. > +Does JSON path return any item for the specified JSON value? Use only > +SQL-standard JSON path expressions, not > +predicate check > +expressions. Any reason for calling it "predicate check expressions" (e.g. the link text) and sometimes "predicate path expressions" (e.g. the linked section title)? I think it should be named consistently to avoid confusion and also to simplify searching. > +Returns the result of a JSON path > +predicate > +check for the specified JSON value. If the result is not > Boolean, > +then NULL is returned. Use only with > +predicate check > +expressions. Linking the same section twice in the same paragraph seems excessive. > += select jsonb_path_query(:'json', > '$.track.segments'); > +select jsonb_path_query(:'json', '$.track.segments'); Please remove the second SELECT. > += select jsonb_path_query(:'json', 'strict > $.track.segments[0].location'); > + jsonb_path_query > +--- > + [47.763, 13.4034] Strict mode is unnecessary to get that result and I'd omit it because the different modes are not introduced yet at this point. > += select jsonb_path_query(:'json', 'strict > $.track.segments.size()'); > + jsonb_path_query > +-- > + 2 Strict mode is unnecessary here as well. > + using the lax mode. To avoid surprising results, we recommend using > + the .** accessor only in the strict mode. The Please change to "in strict mode" (without "the"). [1] https://www.rfc-editor.org/rfc/rfc1740.txt [2] https://web.archive.org/web/20180311140826/http://kaiser-edv.de/documents/AppleSingle_AppleDouble.pdf -- Erik
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Oct 19, 2023, at 23:49, Erik Wienhold wrote: > I don't even know what that represents, probably not some fancy file > compression. Oh, weird. Trying from a webmail client instead. Best, David v5-0001-Improve-boolean-predicate-JSON-Path-docs.patch Description: Binary data
Re: Patch: Improve Boolean Predicate JSON Path Docs
On 2023-10-20 05:20 +0200, David E. Wheeler wrote: > On Oct 19, 2023, at 10:49 PM, Erik Wienhold wrote: > > > Just wanted to take a look at v5. But it's an applefile again :P > > I don’t get it. It was the other times too! Are you able to save it > with a .patch suffix? Saving it is not the problem, but the actual file contents: $ xxd v5-0001-Improve-boolean-predicate-JSON-Path-docs.patch : 0005 1600 0002 0010: 0002 0009 0020: 0032 000a 0003 003c .2...<.. 0030: 0036 7635 2d30 .6..v5-0 0040: 3030 312d 496d 7072 6f76 652d 626f 6f6c 001-Improve-bool 0050: 6561 6e2d 7072 6564 6963 6174 652d 4a53 ean-predicate-JS 0060: 4f4e 2d50 6174 682d 646f 6373 2e70 6174 ON-Path-docs.pat 0070: 6368 ch I don't even know what that represents, probably not some fancy file compression. -- Erik
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Oct 19, 2023, at 10:49 PM, Erik Wienhold wrote: > Just wanted to take a look at v5. But it's an applefile again :P I don’t get it. It was the other times too! Are you able to save it with a .patch suffix? D
Re: Patch: Improve Boolean Predicate JSON Path Docs
On 2023-10-19 15:39 +0200, David E. Wheeler wrote: > On Oct 19, 2023, at 01:22, jian he wrote: > > Updated patch attached and also on GitHub. > > > https://github.com/postgres/postgres/compare/master...theory:postgres:jsonpath-pred-docs Just wanted to take a look at v5. But it's an applefile again :P -- Erik
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Oct 19, 2023, at 01:22, jian he wrote: > "Do not use with non-predicate", double negative is not easy to > comprehend. Maybe we can simplify it. > > 16933: value. Use only SQL-standard JSON path expressions, not not > there are two "not". > > 15842: SQL-standard JSON path expressions, not not > there are two "not”. Thank you, jian. Updated patch attached and also on GitHub. https://github.com/postgres/postgres/compare/master...theory:postgres:jsonpath-pred-docs Best, David v5-0001-Improve-boolean-predicate-JSON-Path-docs.patch Description: application/applefile
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Tue, Oct 17, 2023 at 10:56 AM David E. Wheeler wrote: > > > Oh, I thought it would report issues from the files they were found in. > You’re right, I forgot a title. Fixed in v4. > > David > +Returns the result of a JSON path +predicate +check for the specified JSON value. If the result is not Boolean, +then NULL is returned. Do not use with non-predicate +JSON path expressions. "Do not use with non-predicate", double negative is not easy to comprehend. Maybe we can simplify it. 16933: value. Use only SQL-standard JSON path expressions, not not there are two "not". 15842: SQL-standard JSON path expressions, not not there are two "not".
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Oct 16, 2023, at 18:07, Erik Wienhold wrote: >> Okay, added, let’s just put all our cards on the table. :-) > > I'll have a look but the attached v3 is not a patch but some applefile. Weird, should be no different from previous attachments. I believe Apple Mail always uses application/octet-stream for attachments it doesn’t recognize, which includes .patch and .diff files, sadly. > One of the added is invalid by the looks of it. Maybe > is missing because it says "got (para para )" at the end. Oh, I thought it would report issues from the files they were found in. You’re right, I forgot a title. Fixed in v4. David v4-0001-Improve-boolean-predicate-JSON-Path-docs.patch Description: Binary data
Re: Patch: Improve Boolean Predicate JSON Path Docs
On 2023-10-16 21:59 +0200, David E. Wheeler write: > On Oct 15, 2023, at 23:03, Erik Wienhold wrote: > > > Your call but I'm not against including it in this patch because it > > already touches the modes section. > > Okay, added, let’s just put all our cards on the table. :-) I'll have a look but the attached v3 is not a patch but some applefile. > Thanks, got it down to one: > > postgres.sgml:112: element sect4: validity error : Element sect4 content does > not follow the DTD, expecting (sect4info? , (title , subtitle? , > titleabbrev?) , (toc | lot | index | glossary | bibliography)* , > (((calloutlist | glosslist | bibliolist | itemizedlist | orderedlist | > segmentedlist | simplelist | variablelist | caution | important | note | tip > | warning | literallayout | programlisting | programlistingco | screen | > screenco | screenshot | synopsis | cmdsynopsis | funcsynopsis | classsynopsis > | fieldsynopsis | constructorsynopsis | destructorsynopsis | methodsynopsis | > formalpara | para | simpara | address | blockquote | graphic | graphicco | > mediaobject | mediaobjectco | informalequation | informalexample | > informalfigure | informaltable | equation | example | figure | table | msgset > | procedure | sidebar | qandaset | task | anchor | bridgehead | remark | > highlights | abstract | authorblurb | epigraph | indexterm | beginpage)+ , > (refentry* | sect5* | simplesect*)) | refentry+ | sect5+ | simplesect+) , > (toc | lot | index | glossary | bibliography)*), got (para para ) > One of the added is invalid by the looks of it. Maybe is missing because it says "got (para para )" at the end. -- Erik
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Oct 15, 2023, at 23:03, Erik Wienhold wrote: > Your call but I'm not against including it in this patch because it > already touches the modes section. Okay, added, let’s just put all our cards on the table. :-) >> Agreed. Would be good if we could teach these functions and operators >> to reject path expressions they don’t support. > > Right, you mentioned that idea in [1] (separate types). Not sure what > the best strategy here is but it's likely to break existing queries. > Maybe deprecating unsupported path expressions in the next major release > and changing that to an error in the major release after that. Well if the functions have a JsonPathItem struct, they can check its type attribute and reject those with a root type that’s a predicate in @? and reject it if it’s not a predicate in @@. Example of checking type here: https://github.com/postgres/postgres/blob/54b208f90963cb8b48b9794a5392b2fae4b40a98/src/backend/utils/adt/jsonpath_exec.c#L622 >>> This can be checked with `make -C doc/src/sgml check`. >> >> Thanks. That produces a bunch of warnings for postgres.sgml and >> legal.sgml (and a failure to load the docbook DTD), but func.sgml is >> clean now. > > Hmm... I get no warnings on 1f89b73c4e. Did you install all tools as > described in [2]? The DTD needs to be installed as well. Thanks, got it down to one: postgres.sgml:112: element sect4: validity error : Element sect4 content does not follow the DTD, expecting (sect4info? , (title , subtitle? , titleabbrev?) , (toc | lot | index | glossary | bibliography)* , (((calloutlist | glosslist | bibliolist | itemizedlist | orderedlist | segmentedlist | simplelist | variablelist | caution | important | note | tip | warning | literallayout | programlisting | programlistingco | screen | screenco | screenshot | synopsis | cmdsynopsis | funcsynopsis | classsynopsis | fieldsynopsis | constructorsynopsis | destructorsynopsis | methodsynopsis | formalpara | para | simpara | address | blockquote | graphic | graphicco | mediaobject | mediaobjectco | informalequation | informalexample | informalfigure | informaltable | equation | example | figure | table | msgset | procedure | sidebar | qandaset | task | anchor | bridgehead | remark | highlights | abstract | authorblurb | epigraph | indexterm | beginpage)+ , (refentry* | sect5* | simplesect*)) | refentry+ | sect5+ | simplesect+) , (toc | lot | index | glossary | bibliography)*), got (para para ) David v3-0001-Improve-boolean-predicate-JSON-Path-docs.patch Description: application/applefile
Re: Patch: Improve Boolean Predicate JSON Path Docs
On 2023-10-16 01:04 +0200, David E. Wheeler write:
> On Oct 14, 2023, at 19:51, Erik Wienhold wrote:
>
> > Thanks for putting this together. See my review at the end.
>
> Appreciate the speedy review!
You're welcome.
> >> 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
>
> I planned to submit these changes in a separate patch, based on Tom
> Lane’s suggestion[1]. Would it be preferred to add them to this patch?
Your call but I'm not against including it in this patch because it
already touches the modes section.
> I pokwds around, and it appears the computeroutput bit is used for
> function output. So I followed the precedent in queries.sgml[2] and
> omitted the computeroutput tags but added prompt, e.g.,
>
> = select jsonb_path_query(:'json', 'strict
> $.**.HR');
> jsonb_path_query
> --
> 73
> 135
>
Okay, Not sure what the preferred style is but I saw and
used together in doc/src/sgml/ref/createuser.sgml.
But it's not applied consistently in the rest of the docs.
> >> +
> >> + Predicate-only path expressions are necessary for implementation of
> >> the
> >> + @@ operator (and the
> >> + jsonb_path_match function), and should not be
> >> used
> >> + with the @? operator (or
> >> + jsonb_path_exists function).
> >> +
> >> +
> >> +
> >> + Conversely, non-predicate jsonpath expressions should
> >> not be
> >> + used with the @@ operator (or the
> >> + jsonb_path_match function).
> >> +
> >> +
> >
> > Both paras should be wrapped in a single so that they stand out
> > from the rest of the text. Maybe even , but is already
> > used on this page for things that I'd consider warnings.
>
> Agreed. Would be good if we could teach these functions and operators
> to reject path expressions they don’t support.
Right, you mentioned that idea in [1] (separate types). Not sure what
the best strategy here is but it's likely to break existing queries.
Maybe deprecating unsupported path expressions in the next major release
and changing that to an error in the major release after that.
> > This can be checked with `make -C doc/src/sgml check`.
>
> Thanks. That produces a bunch of warnings for postgres.sgml and
> legal.sgml (and a failure to load the docbook DTD), but func.sgml is
> clean now.
Hmm... I get no warnings on 1f89b73c4e. Did you install all tools as
described in [2]? The DTD needs to be installed as well.
[1]
https://www.postgresql.org/message-id/BAF11F2D-5EDD-4DBB-87FA-4F35845029AE%40justatheory.com
[2] https://www.postgresql.org/docs/current/docguide-toolsets.html
--
Erik
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Oct 14, 2023, at 19:51, Erik Wienhold wrote:
> Thanks for putting this together. See my review at the end.
Appreciate the speedy review!
> 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).
臘♂️
>> 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
I planned to submit these changes in a separate patch, based on Tom Lane’s
suggestion[1]. Would it be preferred to add them to this patch?
> 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.
Good call, done.
> This should use , , and if it shows
> a psql session, e.g.:
>
>
> select jsonb_path_query(:'json', '$.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"}]
>
>
I pokwds around, and it appears the computeroutput bit is used for function
output. So I followed the precedent in queries.sgml[2] and omitted the
computeroutput tags but added prompt, e.g.,
= select jsonb_path_query(:'json', 'strict
$.**.HR');
jsonb_path_query
--
73
135
> Also the cast to jsonb is not necessary and only adds clutter IMO.
Right, removed them all in function calls.
>> +
>> + Predicate-only path expressions are necessary for implementation of
>> the
>> + @@ operator (and the
>> + jsonb_path_match function), and should not be
>> used
>> + with the @? operator (or
>> + jsonb_path_exists function).
>> +
>> +
>> +
>> + Conversely, non-predicate jsonpath expressions should
>> not be
>> + used with the @@ operator (or the
>> + jsonb_path_match function).
>> +
>> +
>
> Both paras should be wrapped in a single so that they stand out
> from the rest of the text. Maybe even , but is already
> used on this page for things that I'd consider warnings.
Agreed. Would be good if we could teach these functions and operators to reject
path expressions they don’t support.
>> +
>> +Regular Expression Interpretation
>> +
>> + There are minor differences in the interpretation of regular
>> + expression patterns used in like_regex filters, as
>> + described in .
>> +
>> +
>
> should be closed here,
> otherwise the docs won't build. This can be checked with
> `make -C doc/src/sgml check`.
Thanks. That produces a bunch of warnings for postgres.sgml and legal.sgml (and
a failure to load the docbook DTD), but func.sgml is clean now.
> `git diff --check` shows a couple of lines with trailing whitespace
> (mostly psql output).
I must’ve cleaned those after I sent the patch, good now. Updated patch
attached, this time created by `git format-patch -v2`.
Best,
David
[1] https://www.postgresql.org/message-id/1229727.1680535592%40sss.pgh.pa.us
[2]
https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-JOIN
v2-0001-Improve-boolean-predicate-JSON-Path-docs.patch
Description: Binary data
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:
>
> -{
> + \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
>}
> ]
>}
> -}
> +}'
>
>
>
> @@ -17229,7 +17229,10 @@ array w/o UK? | t
> .key accessor
> operator to descend through surrounding JSON objects:
>
> -$.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"}]
>
This should use , , and if it shows
a psql session, e.g.:
select jsonb_path_query(:'json',
'$.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"}]
Also the cast to jsonb is not necessary and only adds clutter IMO.
>
>
> @@ -17239,7 +17242,11 @@ $.track.segments
> the following path will return the location coordinates for all
> the available track segments:
>
> -$.track.segments[*].location
> +select jsonb_path_query(:'json'::jsonb, '$.track.segments[*].location');
> + jsonb_path_query
> +---
> + [47.763, 13.4034]
> + [47.706, 13.2635]
>
>
>
> @@ -17248,7 +17255,10 @@ $.track.segments[*].location
> specify the corresponding subscript in the []
> accessor operator. Recall that JSON array indexes are 0-relative:
>
> -$.track.segments[0].location
> +select jsonb_path_query(:'json'::jsonb, 'strict
> $.track.segments[0].location');
> + jsonb_path_query
> +---
> + [47.763, 13.4034]
>
>
>
> @@ -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:
>
> -$.track.segments.size()
> +select jsonb_path_query(:'json'::jsonb, 'strict $.track.segments.size()');
> +
Re: Patch: Improve Boolean Predicate JSON Path Docs
On Oct 14, 2023, at 16:40, David E. Wheeler wrote: > 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. And now I see I can’t spell “Deviations”. Will fix along with any other requested revisions. GitHub diff here if you’re into that sort of thing: https://github.com/postgres/postgres/compare/master...theory:postgres:jsonpath-pred-docs Best, David
Patch: Improve Boolean Predicate JSON Path Docs
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
jsonpath-pred-docs.patch
Description: Binary data
