Re: Minor suggestions for docs regarding json_table

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:
> >
> > In
> >
https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-TABLE
> >
> > 1. There is unused `PASSING` argument `filter2`
> >
> > Consider:
> > ```sql
> > SELECT jt.* FROM
> > my_films,
> > JSON_TABLE (js, '$.favorites[*] ? (@.films[*].director == $filter)'
> > PASSING 'Alfred Hitchcock' AS filter, 'Vertigo' AS filter2
> > COLUMNS (
> > id FOR ORDINALITY,
> > kind text PATH '$.kind',
> > title text FORMAT JSON PATH '$.films[*].title' OMIT QUOTES,
> > director text PATH '$.films[*].director' KEEP QUOTES)) AS jt;
> > ```
> > Issue: `'Vertigo' AS filter2` is unused which is confusing
> > Fix suggestion: Remove `filter2` or add a comment that it is an extra
unused
> > filter
> >
>
> Yeah, I don't see any value in keeping this, especially when the
> example below it has the filter option removed, only adding to the
> confusion.

Sounds good!

>
> > 2. Root jsonpath is confusing
> >
> > Consider:
> > ```sql
> > 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[*]'
> > COLUMNS (
> > user_id FOR ORDINALITY,
> > NESTED '$.movies[*]'
> > COLUMNS (
> > movie_id FOR ORDINALITY,
> > mname text PATH '$.name',
> > director text),
> > NESTED '$.books[*]'
> > COLUMNS (
> > book_id FOR ORDINALITY,
> > bname text PATH '$.name',
> > NESTED '$.authors[*]'
> > COLUMNS (
> > author_id FOR ORDINALITY,
> > author_name text PATH '$.name'))));
> > ```
> >
> > Issue: `$.favorites[*]` is used but `favorites` is an object (not an
array).
> > Without having knowledge about `lax`/`strict` it is confusing why it
works.
> > Also, it would fail if used in strict mode
> > Fix suggestion: Use `$.favorites` or define `favorites` as a 1 item
array or
> > add a comment
> >
>
> I'm a bit tempted to suggest putting the lax keyword in place, so that
> if people played around with the query and switched it to strict they
> would see an example of how that option works, but that feels a bit
> whimsical. In any case, I think adding the array bits in looks like a
> closer match to our original example (which has the array decoration).

I think that using `[*]` never makes sense when dealing with json object
(but maybe I am missing something).
In any case sounds great to wrap favorites in `[]`

>
> > 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?

>
> Robert Treat
> https://xzilla.net

Cheers,
Miłosz