MIME-Version: 1.0
References: 
 <CAN-LCVP7HXmGu-WcinsHvdKqMGEdv=1Y67H4U58F6Y=Q0M5GyQ@mail.gmail.com>
 <CA+HiwqGqz1+jvrWHHzYSnzuVZPp4v-vfEyX-hB=3hTP3Asf78Q@mail.gmail.com>
 <CAN-LCVNSSCTdMjSFHKMkPUDcxNiGX1qn=EWMmmph8dmhkOaj_g@mail.gmail.com>
 <CA+HiwqErd3+tMB5yOerBeNJ5HzYWcByYjn=kJpEcSvdU9OADkg@mail.gmail.com>
 <CAN-LCVOH0AKMXT83SxyP7KKaCPbt_KF_Uibbpmn5_tfFvcnbWA@mail.gmail.com>
 <CAN-LCVPSYfLZHcmvct-_nGZ7peTat-Bo--04K9VXGU8p_TcbWg@mail.gmail.com>
 <CAN-LCVMk6pBMBaghaq5h5awLyWje1iaDXUPJNpWhecFvwWonkw@mail.gmail.com>
 <CAN-LCVMUFri=hm=OFxOnw=oY+yR-WQudRy35R2ZBbCxGhcM_Aw@mail.gmail.com>
 <CAPpHfdvnacwjFGhqit7gE2RevJ8TEHM3bqZjMxb=fJ7P6ZNvKw@mail.gmail.com>
 <CAN-LCVMvrkaNM+pUN=KvoJ4_c6yCVaW5gNucQWWNtsAmMxzOCA@mail.gmail.com>
 <CAN-LCVPU_NTSD+DUguOH58h0X1K5jiVeWRWshFSHzNnHys1T5A@mail.gmail.com>
 <CAPpHfdu-1oDzhKHXRF0vVSqab5Q3V5Sx1iQ5+tOmL_+nqY5_tA@mail.gmail.com>
 <CAPpHfdtJB2APBjD7Npa58Cg4P0KqFjNKq_mT4tpNWN2y+5zKoA@mail.gmail.com>
 <CAN-LCVMpMMf1D7qT5+4sBShzPa_n97wm96zUME2-NKq0-naSTw@mail.gmail.com>
 <CAPpHfdtjXz=MekGoqmST4FWJXgHuvZ9bG8qoqRWUQU8emBhKBQ@mail.gmail.com>
In-Reply-To: 
 <CAPpHfdtjXz=MekGoqmST4FWJXgHuvZ9bG8qoqRWUQU8emBhKBQ@mail.gmail.com>
From: Thom Brown <thom@linux.com>
Date: Thu, 9 Jul 2026 17:11:32 +0100
Message-ID: 
 <CAA-aLv7aZGSExnbjJRw8eKkoXbu34TdoKLLA2gPye3aHjO5OSA@mail.gmail.com>
Subject: Re: SQL/JSON json_table plan clause
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Nikita Malakhov <hukutoc@gmail.com>,
 Amit Langote <amitlangote09@gmail.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAA-aLv7aZGSExnbjJRw8eKkoXbu34TdoKLLA2gPye3aHjO5OSA%40mail.gmail.com>
Precedence: bulk

On Wed, 8 Jul 2026 at 18:04, Alexander Korotkov <aekorotkov@gmail.com> wrot=
e:
>
> On Wed, Jul 8, 2026 at 6:35=E2=80=AFPM Nikita Malakhov <hukutoc@gmail.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 you=
r 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