Thread
Commits
-
doc: Fix some issues with JSON_TABLE() examples
- e7563e3c75a8 18.0 landed
-
doc: Fix some issues with JSON_TABLE() exampls
- 77cdee9ea905 17.5 landed
-
Minor suggestions for docs regarding json_table
The Post Office <noreply@postgresql.org> — 2025-02-03T15:11:43Z
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 -
Re: Minor suggestions for docs regarding json_table
Robert Treat <rob@xzilla.net> — 2025-02-06T21:21:51Z
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 -
Re: Minor suggestions for docs regarding json_table
Miłosz Chmura <mieszko4@gmail.com> — 2025-02-07T16:56:41Z
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? > > Robert Treat > https://xzilla.net Cheers, Miłosz -
Re: Minor suggestions for docs regarding json_table
Robert Treat <rob@xzilla.net> — 2025-02-08T15:59:19Z
On Fri, Feb 7, 2025 at 11:56 AM Miłosz Chmura <mieszko4@gmail.com> wrote: > > > > 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: > > > <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 -
Re: Minor suggestions for docs regarding json_table
Miłosz Chmura <mieszko4@gmail.com> — 2025-02-10T11:01:52Z
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@xzilla.net> wrote: > On Fri, Feb 7, 2025 at 11:56 AM Miłosz Chmura <mieszko4@gmail.com> wrote: > > > > > > > > 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: > > > > > <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 > -
Re: Minor suggestions for docs regarding json_table
Amit Langote <amitlangote09@gmail.com> — 2025-02-14T08:00:20Z
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]. For example, consider the following query that uses the PLAN clause: SELECT director1 AS director, title1, kind1, title2, kind2 FROM JSON_TABLE ( '{ "favorites" : [ { "kind" : "comedy", "films" : [ { "title" : "Bananas", "director" : "Woody Allen"}, { "title" : "The Dinner Game", "director" : "Francis Veber" } ] }, { "kind" : "horror", "films" : [ { "title" : "Psycho", "director" : "Alfred Hitchcock" } ] }, { "kind" : "thriller", "films" : [ { "title" : "Vertigo", "director" : "Alfred Hitchcock" } ] }, { "kind" : "drama", "films" : [ { "title" : "Yojimbo", "director" : "Akira Kurosawa" } ] }] }', '$.favorites' AS favs COLUMNS ( NESTED PATH '$[*]' AS films1 COLUMNS ( kind1 text PATH '$.kind', NESTED PATH '$.films[*]' AS film1 COLUMNS ( title1 text PATH '$.title', director1 text PATH '$.director') ), NESTED PATH '$[*]' AS films2 COLUMNS ( kind2 text PATH '$.kind', NESTED PATH '$.films[*]' AS film2 COLUMNS ( title2 text PATH '$.title', director2 text PATH '$.director' ) ) ) PLAN (favs OUTER ((films1 INNER film1) CROSS (films2 INNER film2))) ) AS jt WHERE kind1 > kind2 AND director1 = director2; director | title1 | kind1 | title2 | kind2 ------------------+---------+----------+--------+-------- Alfred Hitchcock | Vertigo | thriller | Psycho | horror (1 row) 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. -- Thanks, Amit Langote [1] https://www.postgresql.org/message-id/flat/CAN-LCVP7HXmGu-WcinsHvdKqMGEdv%3D1Y67H4U58F6Y%3DQ0M5GyQ%40mail.gmail.com -
Re: Minor suggestions for docs regarding json_table
Robert Treat <rob@xzilla.net> — 2025-02-14T15:20:15Z
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 https://xzilla.net -
Re: Minor suggestions for docs regarding json_table
Amit Langote <amitlangote09@gmail.com> — 2025-02-19T07:26:33Z
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