Re: Minor suggestions for docs regarding json_table - Mailing list pgsql-docs

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].
 

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.