Re: SQL/JSON json_table plan clause

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.

First, doesn't this need a catversion bump? This adds fields to
existing node types.

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)

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.

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 haven't done any performance testing yet

Thom