agora inbox for pgsql-docs@postgresql.org  

help / color / mirror / Atom feed
From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: mieszko4@gmail.com
Subject: Minor suggestions for docs regarding json_table
Date: Mon, 03 Feb 2025 15:11:43 +0000
Message-ID: <173859550337.1071.4748984213168572913@wrigleys.postgresql.org> (raw)

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


view thread (8+ messages)  latest in thread

Message-ID: <173859550337.1071.4748984213168572913@wrigleys.postgresql.org>
Permalink:  ../173859550337.1071.4748984213168572913@wrigleys.postgresql.org/
Also on:    postgresql.org/message-id/173859550337.1071.4748984213168572913@wrigleys.postgresql.org

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-docs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, mieszko4@gmail.com
  Subject: Re: Minor suggestions for docs regarding json_table
  In-Reply-To: <173859550337.1071.4748984213168572913@wrigleys.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox