Re: Minor suggestions for docs regarding json_table

From: Miłosz Chmura <mieszko4(at)gmail(dot)com>
To: Robert Treat <rob(at)xzilla(dot)net>
Cc: pgsql-docs(at)lists(dot)postgresql(dot)org, Amit Langote <amitlangote09(at)gmail(dot)com>
Subject: Re: Minor suggestions for docs regarding json_table
Date: 2025-02-10 11:01:52
Message-ID: CADCuHVW=4zH0vK48qi-4m8sqOoA4bNkKLaA+dJKb1ZXQToRNEA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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(at)xzilla(dot)net> wrote:

> On Fri, Feb 7, 2025 at 11:56 AM Miłosz Chmura <mieszko4(at)gmail(dot)com> wrote:
> >
> >
> >
> > On Thu, Feb 6, 2025 at 10:22 PM Robert Treat <rob(at)xzilla(dot)net> wrote:
> > > On Wed, Feb 5, 2025 at 5:11 AM PG Doc comments form
> > > <noreply(at)postgresql(dot)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
>

In response to

Browse pgsql-docs by date

  From Date Subject
Next Message PG Doc comments form 2025-02-11 08:10:48 Regarding experiencing
Previous Message Robert Treat 2025-02-08 15:59:19 Re: Minor suggestions for docs regarding json_table