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 <r...@xzilla.net> wrote: > 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 >
