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

On Wed, Feb 5, 2025 at 5:11 AM PG Doc comments form
<noreply@postgresql.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.

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

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


Robert Treat
https://xzilla.net