On Thu, 9 Jul 2026 at 21:28, Thom Brown <[email protected]> wrote:
>
> On Thu, 9 Jul 2026 at 20:52, Alexander Korotkov <[email protected]> wrote:
> >
> > Hi, Thom!
> >
> > On Thu, Jul 9, 2026 at 7:12 PM Thom Brown <[email protected]> wrote:
> > > On Wed, 8 Jul 2026 at 18:04, Alexander Korotkov <[email protected]>
> > > wrote:
> > > >
> > > > On Wed, Jul 8, 2026 at 6:35 PM Nikita Malakhov <[email protected]>
> > > > wrote:
> > > > > Alexander, thank you for your participation. I am very sorry, I've
> > > > > seen your previous
> > > > > email but was busy with other tasks and didn't have time to work on
> > > > > your notes.
> > > > > Thank you very much!
> > > >
> > > > OK, no problem. Are you good with the current shape of the patch?
> > >
> > > I've been giving this a test drive. I saw that this has been
> > > committed, and re-tested against that, and the issues I identified
> > > (unless I've misunderstood some functionality) seem to have survived.
> >
> > I tried my best to check if all the critics is addressed. SQL/JSON patches
> > go through so many threads. Sorry that I missed your valuable insights.
>
> You didn't miss anything from me, so nothing to be sorry about. I just
> meant I retested the issues I had found against the commit to see if
> they had been fixed prior to submitting them. I hadn't raised them
> prior to today.
>
> > > First, doesn't this need a catversion bump? This adds fields to
> > > existing node types.
> >
> > Thank you for noticing, catversion is bumped.
> >
> > > I only get the first column from the following. The second one
> > > disappears without error:
> > >
> > > SELECT * FROM JSON_TABLE(
> > > jsonb '[{"x":[1],"y":[2]}]', '$[*]' AS p0
> > > COLUMNS (
> > > NESTED PATH '$.x[*]' AS json_table_path_0 COLUMNS (x int PATH '$'),
> > > NESTED PATH '$.y[*]' COLUMNS (y int PATH '$')
> > > )
> > > PLAN (p0 OUTER json_table_path_0)
> > > ) jt;
> > > x
> > > ---
> > > 1
> > > (1 row)
> >
> > Yes, there is a bug in assignment of generated names. Fixed in 0003.
> >
> > > I have the following view:
> > > CREATE VIEW v_chain AS
> > > SELECT * FROM JSON_TABLE(
> > > jsonb '[{"x": [{"y": [1,2]}]}]', '$[*]' AS p0
> > > COLUMNS ( NESTED PATH '$.x[*]' AS p1 COLUMNS (
> > > NESTED PATH '$.y[*]' AS p11 COLUMNS ( y int PATH '$' ) ) )
> > > PLAN (p0 OUTER (p1 INNER p11))
> > > ) jt;
> > >
> > > When I dump it, I get:
> > >
> > > CREATE VIEW public.v_chain AS
> > > SELECT y
> > > FROM JSON_TABLE(
> > > '[{"x": [{"y": [1, 2]}]}]'::jsonb, '$[*]' AS p0
> > > COLUMNS (
> > > NESTED PATH '$."x"[*]' AS p1
> > > COLUMNS (
> > > NESTED PATH '$."y"[*]' AS p11
> > > COLUMNS (
> > > y integer PATH '$'
> > > )
> > > )
> > > )
> > > PLAN (p0 OUTER p1 INNER p11)
> > > ) jt;
> > >
> > > The parentheses around "p1 INNER p11" aren't preserved.
> >
> > Yes, there is a bug in the deparsing. 0002 fixes that.
> >
> > > Another dump issue with the following:
> > >
> > > CREATE VIEW v_onempty AS SELECT * FROM JSON_TABLE(
> > > jsonb '{}', '$' AS p0
> > > COLUMNS ( a int PATH '$.nosuch' ERROR ON EMPTY )
> > > ERROR ON ERROR
> > > ) jt;
> > >
> > > This dumps as:
> > >
> > > CREATE VIEW public.v_onempty AS
> > > SELECT a
> > > FROM JSON_TABLE(
> > > '{}'::jsonb, '$' AS p0
> > > COLUMNS (
> > > a integer PATH '$."nosuch"'
> > > ) ERROR ON ERROR
> > > ) jt;
> > >
> > > It's lost "ERROR ON EMPTY".
> > >
> > >
> > > Another example:
> > >
> > > SELECT * FROM JSON_TABLE(jsonb '"mystring"', '$'
> > > COLUMNS (a int PATH '$')
> > > ERROR ON ERROR
> > > ) jt;
> > >
> > > This gives me:
> > >
> > > ERROR: invalid input syntax for type integer: "mystring"
> > >
> > > But the documentation says that the table-level clause "does not
> > > affect the errors that occur when evaluating columns". Am I missing
> > > something here?
> >
> > I've rechecked with SQL 2023 standard. AFAICS, ON ERROR clause should
> > be propagated from table to column. We don't propagate it, so that must be
> > a bug since PG 17. But this patch shouldn't try to change it, it's out of
> > scope.
> > 0001 reverts attempt to change ON ERROR handling (this also fixes the case
> > about of invalid dumpinged). I think we need to consider this subject
> > separately.
>
> Yeah, I haven't exercised JSON_TABLE enough since release, so I'm
> clearly late in hitting this.
>
> Thanks for the fixes. I'll try to get round to testing them tomorrow
> unless someone beats me to it.
Attached are some documentation fixes.
Thom
diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 541d02d4a6e..51a88368c7c 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -3640,8 +3640,8 @@ DETAIL: Missing "]" after array dimensions.
against the row constructed from the columns specified in the parent
<literal>COLUMNS</literal> clause to get the row in the final view. Child
columns themselves may contain a <literal>NESTED PATH</literal>
- specification thus allowing to extract data located at arbitrary nesting
- levels. Columns produced by multiple <literal>NESTED PATH</literal>s at the
+ specification, thus allowing extraction of data located at arbitrary
+ nesting levels. Columns produced by multiple <literal>NESTED PATH</literal>s at the
same level are considered to be <firstterm>siblings</firstterm> of each
other and their rows after joining with the parent row are combined using
UNION.
@@ -3662,12 +3662,12 @@ DETAIL: Missing "]" after array dimensions.
JSON_TABLE (
<replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> <optional> PASSING { <replaceable>value</replaceable> AS <replaceable>varname</replaceable> } <optional>, ...</optional> </optional>
COLUMNS ( <replaceable class="parameter">json_table_column</replaceable> <optional>, ...</optional> )
- <optional> { <literal>ERROR</literal> | <literal>EMPTY</literal> <optional>ARRAY</optional>} <literal>ON ERROR</literal> </optional>
<optional>
PLAN ( <replaceable class="parameter">json_table_plan</replaceable> ) |
PLAN DEFAULT ( { INNER | OUTER } <optional> , { CROSS | UNION } </optional>
| { CROSS | UNION } <optional> , { INNER | OUTER } </optional> )
</optional>
+ <optional> { <literal>ERROR</literal> | <literal>EMPTY</literal> <optional>ARRAY</optional>} <literal>ON ERROR</literal> </optional>
)
<phrase>
@@ -3853,7 +3853,7 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
The <literal>NESTED PATH</literal> syntax is recursive,
so you can go down multiple nested levels by specifying several
<literal>NESTED PATH</literal> subclauses within each other.
- It allows to unnest the hierarchy of JSON objects and arrays
+ It allows you to unnest the hierarchy of JSON objects and arrays
in a single function invocation rather than chaining several
<function>JSON_TABLE</function> expressions in an SQL statement.
</para>
@@ -3886,7 +3886,7 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
<para>
The optional <replaceable>json_path_name</replaceable> serves as an
- identifier of the provided <replaceable>json_path_specification</replaceable>.
+ identifier of the provided <replaceable>path_expression</replaceable>.
The path name must be unique and distinct from the column names. Each
path name can appear in the <literal>PLAN</literal> clause only once.
</para>
@@ -3991,7 +3991,7 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
<varlistentry>
<term>
- <literal>PLAN DEFAULT</literal> ( <literal><replaceable>OUTER | INNER</replaceable> <optional>, <replaceable>UNION | CROSS</replaceable> </optional></literal> )
+ <literal>PLAN DEFAULT</literal> ( { <literal>OUTER</literal> | <literal>INNER</literal> } <optional>, { <literal>UNION</literal> | <literal>CROSS</literal> } </optional> )
</term>
<listitem>
<para>
