public inbox for [email protected]  

help / color / mirror / Atom feed

From: Thom Brown <[email protected]>
To: Alexander Korotkov <[email protected]>
Cc: Nikita Malakhov <[email protected]>
Cc: Amit Langote <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: SQL/JSON json_table plan clause
Date: Thu, 9 Jul 2026 17:11:32 +0100
Message-ID: <CAA-aLv7aZGSExnbjJRw8eKkoXbu34TdoKLLA2gPye3aHjO5OSA@mail.gmail.com> (raw)
In-Reply-To: <CAPpHfdtjXz=MekGoqmST4FWJXgHuvZ9bG8qoqRWUQU8emBhKBQ@mail.gmail.com>
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>

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.

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

view thread (9+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: SQL/JSON json_table plan clause
  In-Reply-To: <CAA-aLv7aZGSExnbjJRw8eKkoXbu34TdoKLLA2gPye3aHjO5OSA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox