agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedMinor suggestions for docs regarding json_table
8+ messages / 4 participants
[nested] [flat]
* Minor suggestions for docs regarding json_table
@ 2025-02-03 15:11 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 8+ messages in thread
From: PG Doc comments form @ 2025-02-03 15:11 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: mieszko4@gmail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/17/functions-json.html
Description:
In
https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-TABLE
1. There is unused `PASSING` argument `filter2`
Consider:
```sql
SELECT jt.* FROM
my_films,
JSON_TABLE (js, '$.favorites[*] ? (@.films[*].director == $filter)'
PASSING 'Alfred Hitchcock' AS filter, 'Vertigo' AS filter2
COLUMNS (
id FOR ORDINALITY,
kind text PATH '$.kind',
title text FORMAT JSON PATH '$.films[*].title' OMIT QUOTES,
director text PATH '$.films[*].director' KEEP QUOTES)) AS jt;
```
Issue: `'Vertigo' AS filter2` is unused which is confusing
Fix suggestion: Remove `filter2` or add a comment that it is an extra unused
filter
2. Root jsonpath is confusing
Consider:
```sql
SELECT * FROM JSON_TABLE (
'{"favorites":
{"movies":
[{"name": "One", "director": "John Doe"},
{"name": "Two", "director": "Don Joe"}],
"books":
[{"name": "Mystery", "authors": [{"name": "Brown Dan"}]},
{"name": "Wonder", "authors": [{"name": "Jun Murakami"},
{"name":"Craig Doe"}]}]
}}'::json, '$.favorites[*]'
COLUMNS (
user_id FOR ORDINALITY,
NESTED '$.movies[*]'
COLUMNS (
movie_id FOR ORDINALITY,
mname text PATH '$.name',
director text),
NESTED '$.books[*]'
COLUMNS (
book_id FOR ORDINALITY,
bname text PATH '$.name',
NESTED '$.authors[*]'
COLUMNS (
author_id FOR ORDINALITY,
author_name text PATH '$.name'))));
```
Issue: `$.favorites[*]` is used but `favorites` is an object (not an array).
Without having knowledge about `lax`/`strict` it is confusing why it works.
Also, it would fail if used in strict mode
Fix suggestion: Use `$.favorites` or define `favorites` as a 1 item array or
add a comment
3. Add example for `path_expression [ AS json_path_name ]`
Issue: It is not clear how/why anyone would use `path_expression AS
json_path_name` and it would be great to have an example for it
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Minor suggestions for docs regarding json_table
@ 2025-02-06 21:21 Robert Treat <rob@xzilla.net>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 8+ messages in thread
From: Robert Treat @ 2025-02-06 21:21 UTC (permalink / raw)
To: mieszko4@gmail.com; pgsql-docs@lists.postgresql.org
On Wed, Feb 5, 2025 at 5:11 AM PG Doc comments form
<noreply@postgresql.org> wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/17/functions-json.html
> Description:
>
> In
> https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-TABLE
>
> 1. There is unused `PASSING` argument `filter2`
>
> Consider:
> ```sql
> SELECT jt.* FROM
> my_films,
> JSON_TABLE (js, '$.favorites[*] ? (@.films[*].director == $filter)'
> PASSING 'Alfred Hitchcock' AS filter, 'Vertigo' AS filter2
> COLUMNS (
> id FOR ORDINALITY,
> kind text PATH '$.kind',
> title text FORMAT JSON PATH '$.films[*].title' OMIT QUOTES,
> director text PATH '$.films[*].director' KEEP QUOTES)) AS jt;
> ```
> Issue: `'Vertigo' AS filter2` is unused which is confusing
> Fix suggestion: Remove `filter2` or add a comment that it is an extra unused
> filter
>
Yeah, I don't see any value in keeping this, especially when the
example below it has the filter option removed, only adding to the
confusion.
> 2. Root jsonpath is confusing
>
> Consider:
> ```sql
> SELECT * FROM JSON_TABLE (
> '{"favorites":
> {"movies":
> [{"name": "One", "director": "John Doe"},
> {"name": "Two", "director": "Don Joe"}],
> "books":
> [{"name": "Mystery", "authors": [{"name": "Brown Dan"}]},
> {"name": "Wonder", "authors": [{"name": "Jun Murakami"},
> {"name":"Craig Doe"}]}]
> }}'::json, '$.favorites[*]'
> COLUMNS (
> user_id FOR ORDINALITY,
> NESTED '$.movies[*]'
> COLUMNS (
> movie_id FOR ORDINALITY,
> mname text PATH '$.name',
> director text),
> NESTED '$.books[*]'
> COLUMNS (
> book_id FOR ORDINALITY,
> bname text PATH '$.name',
> NESTED '$.authors[*]'
> COLUMNS (
> author_id FOR ORDINALITY,
> author_name text PATH '$.name'))));
> ```
>
> Issue: `$.favorites[*]` is used but `favorites` is an object (not an array).
> Without having knowledge about `lax`/`strict` it is confusing why it works.
> Also, it would fail if used in strict mode
> Fix suggestion: Use `$.favorites` or define `favorites` as a 1 item array or
> add a comment
>
I'm a bit tempted to suggest putting the lax keyword in place, so that
if people played around with the query and switched it to strict they
would see an example of how that option works, but that feels a bit
whimsical. In any case, I think adding the array bits in looks like a
closer match to our original example (which has the array decoration).
> 3. Add example for `path_expression [ AS json_path_name ]`
>
> Issue: It is not clear how/why anyone would use `path_expression AS
> json_path_name` and it would be great to have an example for it
Well, I can show you the how, but to be honest I am not really sure
why someone would use this:
(hopefully email doesn't eat the formatting)
SELECT * FROM JSON_TABLE (
'{"favorites":
{"movies":
[{"name": "One", "director": "John Doe"},
{"name": "Two", "director": "Don Joe"}],
"books":
[{"name": "Mystery", "authors": [{"name": "Brown Dan"}]},
{"name": "Wonder", "authors": [{"name": "Jun Murakami"},
{"name":"Craig Doe"}]}]
}}'::json, '$.favorites[*]' as fav
COLUMNS (
user_id FOR ORDINALITY,
NESTED '$.movies[*]'
COLUMNS (
movie_id FOR ORDINALITY,
mname text PATH '$.name',
director text),
NESTED '$.books[*]' as book
COLUMNS (
book_id FOR ORDINALITY,
bname text PATH '$.name',
NESTED '$.authors[*]' as writer
COLUMNS (
author_id FOR ORDINALITY,
author_name text PATH '$.fav.book.writer.name'))));
Note the last line. My assumption is that people concoct complIcated
enough json objects and path expressions that the aliasing makes it a
bit easier to follow. If that example sparks an example that you think
is worth adding (or maybe you think the above is?) please post it to
the list, I'd be happy to work it into a patch.
Robert Treat
https://xzilla.net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Minor suggestions for docs regarding json_table
@ 2025-02-07 16:56 Miłosz Chmura <mieszko4@gmail.com>
parent: Robert Treat <rob@xzilla.net>
0 siblings, 1 reply; 8+ messages in thread
From: Miłosz Chmura @ 2025-02-07 16:56 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: pgsql-docs@lists.postgresql.org
On Thu, Feb 6, 2025 at 10:22 PM Robert Treat <rob@xzilla.net> wrote:
>
> On Wed, Feb 5, 2025 at 5:11 AM PG Doc comments form
> <noreply@postgresql.org> wrote:
> >
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/17/functions-json.html
> > Description:
> >
> > In
> >
https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-TABLE
> >
> > 1. There is unused `PASSING` argument `filter2`
> >
> > Consider:
> > ```sql
> > SELECT jt.* FROM
> > my_films,
> > JSON_TABLE (js, '$.favorites[*] ? (@.films[*].director == $filter)'
> > PASSING 'Alfred Hitchcock' AS filter, 'Vertigo' AS filter2
> > COLUMNS (
> > id FOR ORDINALITY,
> > kind text PATH '$.kind',
> > title text FORMAT JSON PATH '$.films[*].title' OMIT QUOTES,
> > director text PATH '$.films[*].director' KEEP QUOTES)) AS jt;
> > ```
> > Issue: `'Vertigo' AS filter2` is unused which is confusing
> > Fix suggestion: Remove `filter2` or add a comment that it is an extra
unused
> > filter
> >
>
> Yeah, I don't see any value in keeping this, especially when the
> example below it has the filter option removed, only adding to the
> confusion.
Sounds good!
>
> > 2. Root jsonpath is confusing
> >
> > Consider:
> > ```sql
> > SELECT * FROM JSON_TABLE (
> > '{"favorites":
> > {"movies":
> > [{"name": "One", "director": "John Doe"},
> > {"name": "Two", "director": "Don Joe"}],
> > "books":
> > [{"name": "Mystery", "authors": [{"name": "Brown Dan"}]},
> > {"name": "Wonder", "authors": [{"name": "Jun Murakami"},
> > {"name":"Craig Doe"}]}]
> > }}'::json, '$.favorites[*]'
> > COLUMNS (
> > user_id FOR ORDINALITY,
> > NESTED '$.movies[*]'
> > COLUMNS (
> > movie_id FOR ORDINALITY,
> > mname text PATH '$.name',
> > director text),
> > NESTED '$.books[*]'
> > COLUMNS (
> > book_id FOR ORDINALITY,
> > bname text PATH '$.name',
> > NESTED '$.authors[*]'
> > COLUMNS (
> > author_id FOR ORDINALITY,
> > author_name text PATH '$.name'))));
> > ```
> >
> > Issue: `$.favorites[*]` is used but `favorites` is an object (not an
array).
> > Without having knowledge about `lax`/`strict` it is confusing why it
works.
> > Also, it would fail if used in strict mode
> > Fix suggestion: Use `$.favorites` or define `favorites` as a 1 item
array or
> > add a comment
> >
>
> I'm a bit tempted to suggest putting the lax keyword in place, so that
> if people played around with the query and switched it to strict they
> would see an example of how that option works, but that feels a bit
> whimsical. In any case, I think adding the array bits in looks like a
> closer match to our original example (which has the array decoration).
I think that using `[*]` never makes sense when dealing with json object
(but maybe I am missing something).
In any case sounds great to wrap favorites in `[]`
>
> > 3. Add example for `path_expression [ AS json_path_name ]`
> >
> > Issue: It is not clear how/why anyone would use `path_expression AS
> > json_path_name` and it would be great to have an example for it
>
> Well, I can show you the how, but to be honest I am not really sure
> why someone would use this:
> (hopefully email doesn't eat the formatting)
> SELECT * FROM JSON_TABLE (
> '{"favorites":
> {"movies":
> [{"name": "One", "director": "John Doe"},
> {"name": "Two", "director": "Don Joe"}],
> "books":
> [{"name": "Mystery", "authors": [{"name": "Brown Dan"}]},
> {"name": "Wonder", "authors": [{"name": "Jun Murakami"},
> {"name":"Craig Doe"}]}]
> }}'::json, '$.favorites[*]' as fav
> COLUMNS (
> user_id FOR ORDINALITY,
> NESTED '$.movies[*]'
> COLUMNS (
> movie_id FOR ORDINALITY,
> mname text PATH '$.name',
> director text),
> NESTED '$.books[*]' as book
> COLUMNS (
> book_id FOR ORDINALITY,
> bname text PATH '$.name',
> NESTED '$.authors[*]' as writer
> COLUMNS (
> author_id FOR ORDINALITY,
> author_name text PATH '$.fav.book.writer.name'))));
>
> Note the last line. My assumption is that people concoct complIcated
> enough json objects and path expressions that the aliasing makes it a
> bit easier to follow. If that example sparks an example that you think
> is worth adding (or maybe you think the above is?) please post it to
> the list, I'd be happy to work it into a patch.
>
Thnx for the example!
I get your point, however, when I run it, it results with NULL for every
author_name.
Does path alias need to be concated/escaped somehow?
>
> Robert Treat
> https://xzilla.net
Cheers,
Miłosz
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Minor suggestions for docs regarding json_table
@ 2025-02-08 15:59 Robert Treat <rob@xzilla.net>
parent: Miłosz Chmura <mieszko4@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Robert Treat @ 2025-02-08 15:59 UTC (permalink / raw)
To: Miłosz Chmura <mieszko4@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org, Amit Langote <amitlangote09@gmail.com>
On Fri, Feb 7, 2025 at 11:56 AM Miłosz Chmura <mieszko4@gmail.com> wrote:
>
>
>
> On Thu, Feb 6, 2025 at 10:22 PM Robert Treat <rob@xzilla.net> wrote:
> > On Wed, Feb 5, 2025 at 5:11 AM PG Doc comments form
> > <noreply@postgresql.org> wrote:
> > >
> > > The following documentation comment has been logged on the website:
> > >
> > > Page: https://www.postgresql.org/docs/17/functions-json.html
> > > Description:
> > >
<snip>
> >
> > > 3. Add example for `path_expression [ AS json_path_name ]`
> > >
> > > Issue: It is not clear how/why anyone would use `path_expression AS
> > > json_path_name` and it would be great to have an example for it
> >
> > Well, I can show you the how, but to be honest I am not really sure
> > why someone would use this:
> > (hopefully email doesn't eat the formatting)
> > SELECT * FROM JSON_TABLE (
> > '{"favorites":
> > {"movies":
> > [{"name": "One", "director": "John Doe"},
> > {"name": "Two", "director": "Don Joe"}],
> > "books":
> > [{"name": "Mystery", "authors": [{"name": "Brown Dan"}]},
> > {"name": "Wonder", "authors": [{"name": "Jun Murakami"},
> > {"name":"Craig Doe"}]}]
> > }}'::json, '$.favorites[*]' as fav
> > COLUMNS (
> > user_id FOR ORDINALITY,
> > NESTED '$.movies[*]'
> > COLUMNS (
> > movie_id FOR ORDINALITY,
> > mname text PATH '$.name',
> > director text),
> > NESTED '$.books[*]' as book
> > COLUMNS (
> > book_id FOR ORDINALITY,
> > bname text PATH '$.name',
> > NESTED '$.authors[*]' as writer
> > COLUMNS (
> > author_id FOR ORDINALITY,
> > author_name text PATH '$.fav.book.writer.name'))));
> >
> > Note the last line. My assumption is that people concoct complIcated
> > enough json objects and path expressions that the aliasing makes it a
> > bit easier to follow. If that example sparks an example that you think
> > is worth adding (or maybe you think the above is?) please post it to
> > the list, I'd be happy to work it into a patch.
> >
>
> Thnx for the example!
> I get your point, however, when I run it, it results with NULL for every author_name.
> Does path alias need to be concated/escaped somehow?
>
My apologies, it's been awhile since I've been down this rabbit hole,
and I think I was conflating SQL/JSON and regular JSON path
expressions together. In the above, you get nulls because PATH
expressions are relative, so it's looking for an entry essentially
like 'authors.fav.book.writer.name' which of course doesn't exist
(hence the nulls).
So, how does this thing get used? AFAIK there is no way to reference
these aliases at the query level, instead they only show up within
EXPLAIN VERBOSE output. Below is said output, where you'll see that
the aliases I provided show up at their corresponding levels, but the
level for "movies", which was unaliased, shows up with the system
generated "json_table_path_0".
Table Function Scan on "json_table" (cost=0.01..1.01 rows=100 width=144)
Output: user_id, movie_id, mname, director, book_id, bname,
author_id, writer_name
Table Function Call: JSON_TABLE('{"favorites": [{"books": [{"name":
"Mystery", "authors": [{"name": "Brown Dan"}]}, {"name": "Wonder",
"authors": [{"name": "Jun Murakami"}, {"name": "Craig Doe"}]}],
"movies": [{"name": "One", "director": "John Doe"}, {"name": "Two",
"director": "Don Joe"}]}]}'::jsonb, '$."favorites"[*]' AS fav COLUMNS
(user_id FOR ORDINALITY, NESTED PATH '$."movies"[*]' AS
json_table_path_0 COLUMNS (movie_id FOR ORDINALITY, mname text PATH
'$."name"', director text PATH '$."director"'), NESTED PATH
'$."books"[*]' AS book COLUMNS (book_id FOR ORDINALITY, bname text
PATH '$."name"', NESTED PATH '$."authors"[*]' AS writer COLUMNS
(author_id FOR ORDINALITY, writer_name text PATH '$."name"'))))
Query Identifier: -8600959643289807018
(4 rows)
Given the relative obscurity of this (and the difficulty I had in
remembering it), I do think it warrants an extra line in the docs.
I've attached a patch with some suggested wording and the previous two
fixes.
Robert Treat
https://xzilla.net
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7efc81936a..c42ac559ee 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19378,6 +19378,8 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
<para>
The optional <replaceable>json_path_name</replaceable> serves as an
identifier of the provided <replaceable>path_expression</replaceable>.
+ Note this identifier is not accesible at the SQL level, but will be
+ visible in <command>EXPLAIN VERBOSE</command> output.
The name must be unique and distinct from the column names.
</para>
</listitem>
@@ -19470,7 +19472,7 @@ SELECT jt.* FROM
SELECT jt.* FROM
my_films,
JSON_TABLE (js, '$.favorites[*] ? (@.films[*].director == $filter)'
- PASSING 'Alfred Hitchcock' AS filter, 'Vertigo' AS filter2
+ PASSING 'Alfred Hitchcock' AS filter
COLUMNS (
id FOR ORDINALITY,
kind text PATH '$.kind',
@@ -19557,13 +19559,13 @@ SELECT jt.* FROM
<programlisting>
SELECT * FROM JSON_TABLE (
'{"favorites":
- {"movies":
+ [{"movies":
[{"name": "One", "director": "John Doe"},
{"name": "Two", "director": "Don Joe"}],
"books":
[{"name": "Mystery", "authors": [{"name": "Brown Dan"}]},
{"name": "Wonder", "authors": [{"name": "Jun Murakami"}, {"name":"Craig Doe"}]}]
-}}'::json, '$.favorites[*]'
+}]}'::json, '$.favorites[*]'
COLUMNS (
user_id FOR ORDINALITY,
NESTED '$.movies[*]'
Attachments:
[text/plain] clarify-json-table.patch.txt (1.5K, ../../CAJSLCQ3Nz4yj73GVpKT673iaxhjs=7yjnbNA75o6Ndf4-rp+pw@mail.gmail.com/2-clarify-json-table.patch.txt)
download | inline diff:
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7efc81936a..c42ac559ee 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19378,6 +19378,8 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
<para>
The optional <replaceable>json_path_name</replaceable> serves as an
identifier of the provided <replaceable>path_expression</replaceable>.
+ Note this identifier is not accesible at the SQL level, but will be
+ visible in <command>EXPLAIN VERBOSE</command> output.
The name must be unique and distinct from the column names.
</para>
</listitem>
@@ -19470,7 +19472,7 @@ SELECT jt.* FROM
SELECT jt.* FROM
my_films,
JSON_TABLE (js, '$.favorites[*] ? (@.films[*].director == $filter)'
- PASSING 'Alfred Hitchcock' AS filter, 'Vertigo' AS filter2
+ PASSING 'Alfred Hitchcock' AS filter
COLUMNS (
id FOR ORDINALITY,
kind text PATH '$.kind',
@@ -19557,13 +19559,13 @@ SELECT jt.* FROM
<programlisting>
SELECT * FROM JSON_TABLE (
'{"favorites":
- {"movies":
+ [{"movies":
[{"name": "One", "director": "John Doe"},
{"name": "Two", "director": "Don Joe"}],
"books":
[{"name": "Mystery", "authors": [{"name": "Brown Dan"}]},
{"name": "Wonder", "authors": [{"name": "Jun Murakami"}, {"name":"Craig Doe"}]}]
-}}'::json, '$.favorites[*]'
+}]}'::json, '$.favorites[*]'
COLUMNS (
user_id FOR ORDINALITY,
NESTED '$.movies[*]'
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Minor suggestions for docs regarding json_table
@ 2025-02-10 11:01 Miłosz Chmura <mieszko4@gmail.com>
parent: Robert Treat <rob@xzilla.net>
1 sibling, 0 replies; 8+ messages in thread
From: Miłosz Chmura @ 2025-02-10 11:01 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: pgsql-docs@lists.postgresql.org, Amit Langote <amitlangote09@gmail.com>
Thnx! All makes sense now.
I guess that if something like `$$.fav.book.writer.name` was implemented to
make absolute paths possible then the alias would be more useful. But I
still did not think of an example of when that would be actually needed :)
In any case, your patch looks great to me.
Thank you!
Miłosz
On Sat, Feb 8, 2025 at 4:59 PM Robert Treat <rob@xzilla.net> wrote:
> On Fri, Feb 7, 2025 at 11:56 AM Miłosz Chmura <mieszko4@gmail.com> wrote:
> >
> >
> >
> > On Thu, Feb 6, 2025 at 10:22 PM Robert Treat <rob@xzilla.net> wrote:
> > > On Wed, Feb 5, 2025 at 5:11 AM PG Doc comments form
> > > <noreply@postgresql.org> wrote:
> > > >
> > > > The following documentation comment has been logged on the website:
> > > >
> > > > Page: https://www.postgresql.org/docs/17/functions-json.html
> > > > Description:
> > > >
> <snip>
> > >
> > > > 3. Add example for `path_expression [ AS json_path_name ]`
> > > >
> > > > Issue: It is not clear how/why anyone would use `path_expression AS
> > > > json_path_name` and it would be great to have an example for it
> > >
> > > Well, I can show you the how, but to be honest I am not really sure
> > > why someone would use this:
> > > (hopefully email doesn't eat the formatting)
> > > SELECT * FROM JSON_TABLE (
> > > '{"favorites":
> > > {"movies":
> > > [{"name": "One", "director": "John Doe"},
> > > {"name": "Two", "director": "Don Joe"}],
> > > "books":
> > > [{"name": "Mystery", "authors": [{"name": "Brown Dan"}]},
> > > {"name": "Wonder", "authors": [{"name": "Jun Murakami"},
> > > {"name":"Craig Doe"}]}]
> > > }}'::json, '$.favorites[*]' as fav
> > > COLUMNS (
> > > user_id FOR ORDINALITY,
> > > NESTED '$.movies[*]'
> > > COLUMNS (
> > > movie_id FOR ORDINALITY,
> > > mname text PATH '$.name',
> > > director text),
> > > NESTED '$.books[*]' as book
> > > COLUMNS (
> > > book_id FOR ORDINALITY,
> > > bname text PATH '$.name',
> > > NESTED '$.authors[*]' as writer
> > > COLUMNS (
> > > author_id FOR ORDINALITY,
> > > author_name text PATH '$.fav.book.writer.name'))));
> > >
> > > Note the last line. My assumption is that people concoct complIcated
> > > enough json objects and path expressions that the aliasing makes it a
> > > bit easier to follow. If that example sparks an example that you think
> > > is worth adding (or maybe you think the above is?) please post it to
> > > the list, I'd be happy to work it into a patch.
> > >
> >
> > Thnx for the example!
> > I get your point, however, when I run it, it results with NULL for every
> author_name.
> > Does path alias need to be concated/escaped somehow?
> >
>
> My apologies, it's been awhile since I've been down this rabbit hole,
> and I think I was conflating SQL/JSON and regular JSON path
> expressions together. In the above, you get nulls because PATH
> expressions are relative, so it's looking for an entry essentially
> like 'authors.fav.book.writer.name' which of course doesn't exist
> (hence the nulls).
>
> So, how does this thing get used? AFAIK there is no way to reference
> these aliases at the query level, instead they only show up within
> EXPLAIN VERBOSE output. Below is said output, where you'll see that
> the aliases I provided show up at their corresponding levels, but the
> level for "movies", which was unaliased, shows up with the system
> generated "json_table_path_0".
>
> Table Function Scan on "json_table" (cost=0.01..1.01 rows=100 width=144)
> Output: user_id, movie_id, mname, director, book_id, bname,
> author_id, writer_name
> Table Function Call: JSON_TABLE('{"favorites": [{"books": [{"name":
> "Mystery", "authors": [{"name": "Brown Dan"}]}, {"name": "Wonder",
> "authors": [{"name": "Jun Murakami"}, {"name": "Craig Doe"}]}],
> "movies": [{"name": "One", "director": "John Doe"}, {"name": "Two",
> "director": "Don Joe"}]}]}'::jsonb, '$."favorites"[*]' AS fav COLUMNS
> (user_id FOR ORDINALITY, NESTED PATH '$."movies"[*]' AS
> json_table_path_0 COLUMNS (movie_id FOR ORDINALITY, mname text PATH
> '$."name"', director text PATH '$."director"'), NESTED PATH
> '$."books"[*]' AS book COLUMNS (book_id FOR ORDINALITY, bname text
> PATH '$."name"', NESTED PATH '$."authors"[*]' AS writer COLUMNS
> (author_id FOR ORDINALITY, writer_name text PATH '$."name"'))))
> Query Identifier: -8600959643289807018
> (4 rows)
>
> Given the relative obscurity of this (and the difficulty I had in
> remembering it), I do think it warrants an extra line in the docs.
> I've attached a patch with some suggested wording and the previous two
> fixes.
>
>
> Robert Treat
> https://xzilla.net
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Minor suggestions for docs regarding json_table
@ 2025-02-14 08:00 Amit Langote <amitlangote09@gmail.com>
parent: Robert Treat <rob@xzilla.net>
1 sibling, 1 reply; 8+ messages in thread
From: Amit Langote @ 2025-02-14 08:00 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: Miłosz Chmura <mieszko4@gmail.com>; pgsql-docs@lists.postgresql.org
On Sun, Feb 9, 2025 at 12:59 AM Robert Treat <rob@xzilla.net> wrote:
> So, how does this thing get used? AFAIK there is no way to reference
> these aliases at the query level, instead they only show up within
> EXPLAIN VERBOSE output. Below is said output, where you'll see that
> the aliases I provided show up at their corresponding levels, but the
> level for "movies", which was unaliased, shows up with the system
> generated "json_table_path_0".
>
> Table Function Scan on "json_table" (cost=0.01..1.01 rows=100 width=144)
> Output: user_id, movie_id, mname, director, book_id, bname,
> author_id, writer_name
> Table Function Call: JSON_TABLE('{"favorites": [{"books": [{"name":
> "Mystery", "authors": [{"name": "Brown Dan"}]}, {"name": "Wonder",
> "authors": [{"name": "Jun Murakami"}, {"name": "Craig Doe"}]}],
> "movies": [{"name": "One", "director": "John Doe"}, {"name": "Two",
> "director": "Don Joe"}]}]}'::jsonb, '$."favorites"[*]' AS fav COLUMNS
> (user_id FOR ORDINALITY, NESTED PATH '$."movies"[*]' AS
> json_table_path_0 COLUMNS (movie_id FOR ORDINALITY, mname text PATH
> '$."name"', director text PATH '$."director"'), NESTED PATH
> '$."books"[*]' AS book COLUMNS (book_id FOR ORDINALITY, bname text
> PATH '$."name"', NESTED PATH '$."authors"[*]' AS writer COLUMNS
> (author_id FOR ORDINALITY, writer_name text PATH '$."name"'))))
> Query Identifier: -8600959643289807018
> (4 rows)
>
> Given the relative obscurity of this (and the difficulty I had in
> remembering it), I do think it warrants an extra line in the docs.
> I've attached a patch with some suggested wording and the previous two
> fixes.
The path names are not particularly useful in queries, meaning they
don’t serve a purpose in path expressions. However, they will be
needed when the PLAN clause is supported [1].
For example, consider the following query that uses the PLAN clause:
SELECT
director1 AS director, title1, kind1, title2, kind2
FROM
JSON_TABLE (
'{ "favorites" : [
{ "kind" : "comedy", "films" : [
{ "title" : "Bananas",
"director" : "Woody Allen"},
{ "title" : "The Dinner Game",
"director" : "Francis Veber" } ] },
{ "kind" : "horror", "films" : [
{ "title" : "Psycho",
"director" : "Alfred Hitchcock" } ] },
{ "kind" : "thriller", "films" : [
{ "title" : "Vertigo",
"director" : "Alfred Hitchcock" } ] },
{ "kind" : "drama", "films" : [
{ "title" : "Yojimbo",
"director" : "Akira Kurosawa" } ] }] }',
'$.favorites' AS favs
COLUMNS (
NESTED PATH '$[*]' AS films1 COLUMNS (
kind1 text PATH '$.kind',
NESTED PATH '$.films[*]' AS film1 COLUMNS (
title1 text PATH '$.title',
director1 text PATH '$.director')
),
NESTED PATH '$[*]' AS films2 COLUMNS (
kind2 text PATH '$.kind',
NESTED PATH '$.films[*]' AS film2 COLUMNS (
title2 text PATH '$.title',
director2 text PATH '$.director'
)
)
)
PLAN (favs OUTER ((films1 INNER film1) CROSS (films2 INNER film2)))
) AS jt
WHERE kind1 > kind2 AND director1 = director2;
director | title1 | kind1 | title2 | kind2
------------------+---------+----------+--------+--------
Alfred Hitchcock | Vertigo | thriller | Psycho | horror
(1 row)
Given this, I think we should leave the path name documentation as it
is for now and address it, if needed, as part of the patch for the
PLAN clause. I'll go ahead and push your other fixes as in the
attached patch.
--
Thanks, Amit Langote
[1] https://www.postgresql.org/message-id/flat/CAN-LCVP7HXmGu-WcinsHvdKqMGEdv%3D1Y67H4U58F6Y%3DQ0M5GyQ%4...
Attachments:
[application/octet-stream] v2-0001-doc-Fix-some-issues-with-JSON_TABLE-examples.patch (1.7K, ../../CA+HiwqHWGffE7Hq0TPN_9JQyv4j_Jy7k+W6F8Dx9QAaS=yarAQ@mail.gmail.com/2-v2-0001-doc-Fix-some-issues-with-JSON_TABLE-examples.patch)
download | inline diff:
From 3b50eb9fcf5b925213b99a8b6f3e9b947ecc3103 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Fri, 14 Feb 2025 16:56:51 +0900
Subject: [PATCH v2] doc: Fix some issues with JSON_TABLE() examples
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
1. Remove an unused PASSING variable.
2. Adjust formatting of JSON data use in an example to be valid under
strict mode
Reported-by: Miłosz Chmura <mieszko4@gmail.com>
Author: Robert Treat <rob@xzilla.net>
Discussion: https://postgr.es/m/173859550337.1071.4748984213168572913@wrigleys.postgresql.org
---
doc/src/sgml/func.sgml | 6 +++---
1 file changed, 3 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7efc81936ab..1e690b1320d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19470,7 +19470,7 @@ SELECT jt.* FROM
SELECT jt.* FROM
my_films,
JSON_TABLE (js, '$.favorites[*] ? (@.films[*].director == $filter)'
- PASSING 'Alfred Hitchcock' AS filter, 'Vertigo' AS filter2
+ PASSING 'Alfred Hitchcock' AS filter
COLUMNS (
id FOR ORDINALITY,
kind text PATH '$.kind',
@@ -19557,13 +19557,13 @@ SELECT jt.* FROM
<programlisting>
SELECT * FROM JSON_TABLE (
'{"favorites":
- {"movies":
+ [{"movies":
[{"name": "One", "director": "John Doe"},
{"name": "Two", "director": "Don Joe"}],
"books":
[{"name": "Mystery", "authors": [{"name": "Brown Dan"}]},
{"name": "Wonder", "authors": [{"name": "Jun Murakami"}, {"name":"Craig Doe"}]}]
-}}'::json, '$.favorites[*]'
+}]}'::json, '$.favorites[*]'
COLUMNS (
user_id FOR ORDINALITY,
NESTED '$.movies[*]'
--
2.43.0
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Minor suggestions for docs regarding json_table
@ 2025-02-14 15:20 Robert Treat <rob@xzilla.net>
parent: Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Robert Treat @ 2025-02-14 15:20 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; +Cc: Miłosz Chmura <mieszko4@gmail.com>; pgsql-docs@lists.postgresql.org
On Fri, Feb 14, 2025 at 3:00 AM Amit Langote <amitlangote09@gmail.com>
wrote:
> On Sun, Feb 9, 2025 at 12:59 AM Robert Treat <rob@xzilla.net> wrote:
> > So, how does this thing get used? AFAIK there is no way to reference
> > these aliases at the query level, instead they only show up within
> > EXPLAIN VERBOSE output. Below is said output, where you'll see that
> > the aliases I provided show up at their corresponding levels, but the
> > level for "movies", which was unaliased, shows up with the system
> > generated "json_table_path_0".
> >
> > Table Function Scan on "json_table" (cost=0.01..1.01 rows=100 width=144)
> > Output: user_id, movie_id, mname, director, book_id, bname,
> > author_id, writer_name
> > Table Function Call: JSON_TABLE('{"favorites": [{"books": [{"name":
> > "Mystery", "authors": [{"name": "Brown Dan"}]}, {"name": "Wonder",
> > "authors": [{"name": "Jun Murakami"}, {"name": "Craig Doe"}]}],
> > "movies": [{"name": "One", "director": "John Doe"}, {"name": "Two",
> > "director": "Don Joe"}]}]}'::jsonb, '$."favorites"[*]' AS fav COLUMNS
> > (user_id FOR ORDINALITY, NESTED PATH '$."movies"[*]' AS
> > json_table_path_0 COLUMNS (movie_id FOR ORDINALITY, mname text PATH
> > '$."name"', director text PATH '$."director"'), NESTED PATH
> > '$."books"[*]' AS book COLUMNS (book_id FOR ORDINALITY, bname text
> > PATH '$."name"', NESTED PATH '$."authors"[*]' AS writer COLUMNS
> > (author_id FOR ORDINALITY, writer_name text PATH '$."name"'))))
> > Query Identifier: -8600959643289807018
> > (4 rows)
> >
> > Given the relative obscurity of this (and the difficulty I had in
> > remembering it), I do think it warrants an extra line in the docs.
> > I've attached a patch with some suggested wording and the previous two
> > fixes.
>
> The path names are not particularly useful in queries, meaning they
> don’t serve a purpose in path expressions. However, they will be
> needed when the PLAN clause is supported [1].
>
<snip>
>
>
Given this, I think we should leave the path name documentation as it
> is for now and address it, if needed, as part of the patch for the
> PLAN clause. I'll go ahead and push your other fixes as in the
> attached patch.
>
>
Ah, thanks for the heads up. Reasoning makes sense to me / patch looks
good. Thanks!
Robert Treat
https://xzilla.net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Minor suggestions for docs regarding json_table
@ 2025-02-19 07:26 Amit Langote <amitlangote09@gmail.com>
parent: Robert Treat <rob@xzilla.net>
0 siblings, 0 replies; 8+ messages in thread
From: Amit Langote @ 2025-02-19 07:26 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: Miłosz Chmura <mieszko4@gmail.com>; pgsql-docs@lists.postgresql.org
On Sat, Feb 15, 2025 at 12:20 AM Robert Treat <rob@xzilla.net> wrote:
>> Given this, I think we should leave the path name documentation as it
>> is for now and address it, if needed, as part of the patch for the
>> PLAN clause. I'll go ahead and push your other fixes as in the
>> attached patch.
>>
>
> Ah, thanks for the heads up. Reasoning makes sense to me / patch looks good. Thanks!
Pushed this now, thanks for the patch.
--
Thanks, Amit Langote
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-02-19 07:26 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-03 15:11 Minor suggestions for docs regarding json_table PG Doc comments form <noreply@postgresql.org>
2025-02-06 21:21 ` Robert Treat <rob@xzilla.net>
2025-02-07 16:56 ` Miłosz Chmura <mieszko4@gmail.com>
2025-02-08 15:59 ` Robert Treat <rob@xzilla.net>
2025-02-10 11:01 ` Miłosz Chmura <mieszko4@gmail.com>
2025-02-14 08:00 ` Amit Langote <amitlangote09@gmail.com>
2025-02-14 15:20 ` Robert Treat <rob@xzilla.net>
2025-02-19 07:26 ` Amit Langote <amitlangote09@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox