Re: SQL/JSON json_table plan clause

On Fri, 10 Jul 2026 at 17:28, Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
>
> On Fri, Jul 10, 2026 at 6:48 PM Thom Brown <thom(at)linux(dot)com> wrote:
> >
> > On Thu, 9 Jul 2026 at 21:28, Thom Brown <thom(at)linux(dot)com> wrote:
> > >
> > > On Thu, 9 Jul 2026 at 20:52, Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
> > > >
> > > > Hi, Thom!
> > > >
> > > > On Thu, Jul 9, 2026 at 7:12 PM Thom Brown <thom(at)linux(dot)com> wrote:
> > > > > On Wed, 8 Jul 2026 at 18:04, Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
> > > > > >
> > > > > > On Wed, Jul 8, 2026 at 6:35 PM Nikita Malakhov <hukutoc(at)gmail(dot)com> 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.
>
> Good, I've added them to my patchset. I see you moved OUTER to come
> before INNER. I made the same reordering in other places for
> consistency.

I've tested patches 0001-0003.

0001 - the ERROR ON ERROR query returns a NULL row again, so it now
matches the docs. ERROR ON EMPTY is now output in pg_dump
too.paragraph is accurate again.

0002 - PLAN (p0 OUTER (p1 INNER p11)) is now preserving parentheses,
including a three-level (p1 INNER (p11 INNER p111)) chain and the
mixed version (p0 OUTER ((p1 INNER p11) UNION p2)). Schema-only
dump/restore of all test views work.

0003 - the silent column-drop now correctly errors with "PLAN clause
for nested path json_table_path_1 was not found".

However, I am getting a new error:

postgres=# SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS
(json_table_path_0 int PATH '$')) jt;
ERROR: duplicate JSON_TABLE column or path name: json_table_path_0
LINE 1: SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (json_table_...

So I think patch 0003 needs a new version because it claims "a
generated name can no longer coincide with a user-supplied one"

I also did a few performance tests. I've noticed that JSON_TABLE
queries using NESTED PATH have become noticeably slower since this
commit, even when no PLAN clause is used.

Here's what I get prior to this commit:

postgres=# \timing
Timing is on.
postgres=# CREATE TEMP TABLE doc AS
SELECT (SELECT jsonb_agg(jsonb_build_object('a', i,
'b', (SELECT jsonb_agg(j) FROM generate_series(1,1000) j)))
FROM generate_series(1,1000) i) AS j;
SELECT 1
Time: 195.441 ms
postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]'
COLUMNS (a int PATH '$.a',
NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt;
count
-------
4000
(1 row)

Time: 150.463 ms
postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]'
COLUMNS (a int PATH '$.a',
NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt;
count
-------
4000
(1 row)

Time: 146.457 ms
postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]'
COLUMNS (a int PATH '$.a',
NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt;
count
-------
4000
(1 row)

Time: 147.257 ms

And post-commit:

postgres=# CREATE TEMP TABLE doc AS
SELECT (SELECT jsonb_agg(jsonb_build_object('a', i,
'b', (SELECT jsonb_agg(j) FROM generate_series(1,1000) j)))
FROM generate_series(1,1000) i) AS j;
SELECT 1
postgres=# \timing
Timing is on.
postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]'
COLUMNS (a int PATH '$.a',
NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt;
count
-------
4000
(1 row)

Time: 536.018 ms
postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]'
COLUMNS (a int PATH '$.a',
NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt;
count
-------
4000
(1 row)

Time: 526.044 ms
postgres=# SELECT count(*) FROM doc, JSON_TABLE(doc.j, '$[*]'
COLUMNS (a int PATH '$.a',
NESTED PATH '$.b[*] ? (@ < 5)' COLUMNS (v int PATH '$'))) jt;
count
-------
4000
(1 row)

Time: 582.042 ms

This gives an average of 148.059 pre-commit vs.548.035ms post-commit,
a 3.7 x slowdown.

This seems to grow with the size of the nested arrays., With small
ones the difference is only around 10%.

Is this expected?

Regards

Thom