Re: remaining sql/json patches

On Thu, Apr 4, 2024 at 3:50 PM jian he <jian.universality@gmail.com> wrote:
>
> On Thu, Apr 4, 2024 at 2:41 PM jian he <jian.universality@gmail.com> wrote:
> >
> > On Wed, Apr 3, 2024 at 8:39 PM Amit Langote <amitlangote09@gmail.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.