Re: Minor suggestions for docs regarding json_table

On Sun, Feb 9, 2025 at 12:59 AM Robert Treat <rob(at)xzilla(dot)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%40mail.gmail.com