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