From: | PG Doc comments form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-docs(at)lists(dot)postgresql(dot)org |
Cc: | mieszko4(at)gmail(dot)com |
Subject: | Minor suggestions for docs regarding json_table |
Date: | 2025-02-03 15:11:43 |
Message-ID: | 173859550337.1071.4748984213168572913@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Treat | 2025-02-03 15:59:29 | Re: timestamp with time zone ~> GMT |
Previous Message | Peter Eisentraut | 2025-02-03 12:59:18 | Re: Ordering of statistic views |