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
