Re: SQL/JSON json_table plan clause

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.

> 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 dumping). I think we need to consider this subject
separately.

> I haven't done any performance testing yet

I ran a basic benchmark to show where the PLAN clause actually helps
performance. The lever is row multiplicity: the default parent/child
join is OUTER, so JSON_TABLE emits a row for every parent -- including
ones whose nested array is empty. When the nested array is usually
empty (a common shape for real JSON), asking for INNER via the PLAN
clause prunes those parents at the source instead of generating them and
discarding them with a later WHERE.

Setup -- 300k parent items, each with a few scalar columns, only every
300th has a child array:

SET jit = off;
SET max_parallel_workers_per_gather = 0;

CREATE TEMP TABLE d(js jsonb);
INSERT INTO d
SELECT jsonb_build_object('items', jsonb_agg(item))
FROM (
SELECT jsonb_build_object(
'id', g, 'a', g+1, 'b', g+2, 'c', g+3, 'd', g+4,
'kids', CASE WHEN g % 300 = 0
THEN jsonb_build_array(
jsonb_build_object('v', g),
jsonb_build_object('v', g+1))
ELSE '[]'::jsonb END) AS item
FROM generate_series(1, 300000) g
) s;

A) Default plan (OUTER) + WHERE to keep only matched rows:

SELECT count(jt.v) FROM d, JSON_TABLE(js, '$.items[*]'
COLUMNS (id int PATH '$.id', a int PATH '$.a', b int PATH '$.b',
c int PATH '$.c', dd int PATH '$.d',
NESTED PATH '$.kids[*]' COLUMNS (v int PATH '$.v'))) jt
WHERE jt.v IS NOT NULL;

B) Same result, but prune childless parents inside JSON_TABLE:

SELECT count(jt.v) FROM d, JSON_TABLE(js, '$.items[*]' AS root
COLUMNS (id int PATH '$.id', a int PATH '$.a', b int PATH '$.b',
c int PATH '$.c', dd int PATH '$.d',
NESTED PATH '$.kids[*]' AS kids COLUMNS (v int PATH '$.v'))
PLAN (root INNER kids)) jt;

Both return the same 2000 rows. EXPLAIN ANALYZE:

A) OUTER + WHERE : ~1100 ms
-> Table Function Scan on "json_table" (rows=2000)
Rows Removed by Filter: 299000
B) PLAN INNER : ~413 ms
-> Table Function Scan on "json_table" (rows=2000)

So ~2.7x here. The "Rows Removed by Filter: 299000" line is the whole
story: (A) materializes 299k wide childless-parent rows (evaluating all
five column paths for each) just to throw them away, while (B) never
builds them.

To be fair, it's data-dependent – if most parents have children there's
nothing to prune and INNER has similar performance to OUTER
and the case of win is specifically OUTER => INNER pruning,
because UNION/CROSS changes the result set rather than speeding
anything up.

------
Regards,
Alexander Korotkov
Supabase