On Fri, Feb 7, 2025 at 11:56 AM Miłosz Chmura <miesz...@gmail.com> wrote:
>
>
>
> On Thu, Feb 6, 2025 at 10:22 PM Robert Treat <r...@xzilla.net> wrote:
> > On Wed, Feb 5, 2025 at 5:11 AM PG Doc comments form
> > <nore...@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[*]'
