Re: remaining sql/json patches

On Thu, Apr 4, 2024 at 3:50 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> On Thu, Apr 4, 2024 at 2:41 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> >
> > On Wed, Apr 3, 2024 at 8:39 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > >
> > > Attached updated patches. I have addressed your doc comments on 0001,
> > > but not 0002 yet.

hi
some doc issue about v49, 0002.
+ Each
+ <literal>NESTED PATH</literal> clause can be used to generate one or more
+ columns using the data from a nested level of the row pattern, which can be
+ specified using a <literal>COLUMNS</literal> clause.
maybe change to

+ Each
+ <literal>NESTED PATH</literal> clause can be used to generate one or more
+ columns using the data from an upper nested level of the row
pattern, which can be
+ specified using a <literal>COLUMNS</literal> clause

+ Child
+ columns may themselves contain a <literal>NESTED PATH</literal>
+ specifification thus allowing to extract data located at arbitrary nesting
+ levels.
maybe change to
+ Child
+ columns themselves may contain a <literal>NESTED PATH</literal>
+ specification thus allowing to extract data located at any arbitrary nesting
+ level.

+</screen>
+ </para>
+ <para>
+ The following is a modified version of the above query to show the usage
+ of <literal>NESTED PATH</literal> for populating title and director
+ columns, illustrating how they are joined to the parent columns id and
+ kind:
+<screen>
+SELECT jt.* FROM
+ my_films,
+ JSON_TABLE ( js, '$.favorites[*] ? (@.films[*].director == $filter)'
+ PASSING 'Alfred Hitchcock' AS filter
+ COLUMNS (
+ id FOR ORDINALITY,
+ kind text PATH '$.kind',
+ NESTED PATH '$.films[*]' COLUMNS (
+ title text FORMAT JSON PATH '$.title' OMIT QUOTES,
+ director text PATH '$.director' KEEP QUOTES))) AS jt;
+ id | kind | title | director
+----+----------+---------+--------------------
+ 1 | horror | Psycho | "Alfred Hitchcock"
+ 2 | thriller | Vertigo | "Alfred Hitchcock"
+(2 rows)
+</screen>
+ </para>
+ <para>
+ The following is the same query but without the filter in the root
+ path:
+<screen>
+SELECT jt.* FROM
+ my_films,
+ JSON_TABLE ( js, '$.favorites[*]'
+ COLUMNS (
+ id FOR ORDINALITY,
+ kind text PATH '$.kind',
+ NESTED PATH '$.films[*]' COLUMNS (
+ title text FORMAT JSON PATH '$.title' OMIT QUOTES,
+ director text PATH '$.director' KEEP QUOTES))) AS jt;
+ id | kind | title | director
+----+----------+-----------------+--------------------
+ 1 | comedy | Bananas | "Woody Allen"
+ 1 | comedy | The Dinner Game | "Francis Veber"
+ 2 | horror | Psycho | "Alfred Hitchcock"
+ 3 | thriller | Vertigo | "Alfred Hitchcock"
+ 4 | drama | Yojimbo | "Akira Kurosawa"
+(5 rows)
</screen>

just found out that the query and the query's output condensed together.
in https://www.postgresql.org/docs/current/tutorial-window.html
the query we use <programlisting>, the output we use <screen>.
maybe we can do it the same way,
or we could just have one or two empty new lines separate them.
we have the similar problem in v49, 0001.