Thread: Minor suggestions for docs regarding json_table
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 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 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
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
On Thu, Feb 6, 2025 at 10:22 PM Robert Treat <rob@xzilla.net> wrote:
>
> 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.
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?
Cheers,
Miłosz
On Fri, Feb 14, 2025 at 3:00 AM Amit Langote <amitlangote09@gmail.com> wrote:
On Sun, Feb 9, 2025 at 12:59 AM Robert Treat <rob@xzilla.net> wrote:
> 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.
The path names are not particularly useful in queries, meaning they
don’t serve a purpose in path expressions. However, they will be
needed when the PLAN clause is supported [1].
<snip>
Given this, I think we should leave the path name documentation as it
is for now and address it, if needed, as part of the patch for the
PLAN clause. I'll go ahead and push your other fixes as in the
attached patch.
Ah, thanks for the heads up. Reasoning makes sense to me / patch looks good. Thanks!
Robert Treat
On Sat, Feb 15, 2025 at 12:20 AM Robert Treat <rob@xzilla.net> wrote: >> Given this, I think we should leave the path name documentation as it >> is for now and address it, if needed, as part of the patch for the >> PLAN clause. I'll go ahead and push your other fixes as in the >> attached patch. >> > > Ah, thanks for the heads up. Reasoning makes sense to me / patch looks good. Thanks! Pushed this now, thanks for the patch. -- Thanks, Amit Langote