Minor suggestions for docs regarding json_table

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

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

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

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 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]/messages/by-id/CAN-LCVP7HXmGu-WcinsHvdKqMGEdv=1Y67H4U58F6Y=Q0M5GyQ@mail.gmail.com.

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]: /messages/by-id/CAN-LCVP7HXmGu-WcinsHvdKqMGEdv=1Y67H4U58F6Y=Q0M5GyQ@mail.gmail.com

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

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