Re: ON ERROR in json_query and the like

From: Amit Langote <amitlangote09(at)gmail(dot)com>
To: Markus Winand <markus(dot)winand(at)winand(dot)at>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: ON ERROR in json_query and the like
Date: 2024-06-20 09:19:09
Message-ID: CA+HiwqF2Z6FATWQV6bG9NeKYf=++fOgmdbYc9gWSNJ81jfqCuA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

On Mon, Jun 17, 2024 at 10:07 PM Markus Winand <markus(dot)winand(at)winand(dot)at> wrote:
> > On 17.06.2024, at 08:20, Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > Agree that the documentation needs to be clear about this. I'll update
> > my patch at [1] to add a note next to table 9.16.3. SQL/JSON Query
> > Functions.
>
> Considering another branch of this thread [1] I think the
> "Supported Features” appendix of the docs should mention that as well.
>
> The way I see it is that the standards defines two overloaded
> JSON_QUERY functions, of which PostgreSQL will support only one.
> In case of valid JSON, the implied CAST makes it look as though
> the second variant of these function was supported as well but that
> illusion totally falls apart once the JSON is not valid anymore.
>
> I think it affects the following feature IDs:
>
> - T821, Basic SQL/JSON query operators
> For JSON_VALUE, JSON_TABLE and JSON_EXISTS
> - T828, JSON_QUERY
>
> Also, how hard would it be to add the functions that accept
> character strings? Is there, besides the effort, any thing else
> against it? I’m asking because I believe once released it might
> never be changed — for backward compatibility.

Hmm, I'm starting to think that adding the implied cast to json wasn't
such a great idea after all, because it might mislead the users to
think that JSON parsing is transparent (respects ON ERROR), which is
what you are saying, IIUC.

I'm inclined to push the attached patch which puts back the
restriction to allow only jsonb arguments, asking users to add an
explicit cast if necessary.

--
Thanks, Amit Langote

Attachment Content-Type Size
v1-0001-SQL-JSON-Only-allow-arguments-of-jsonb-type.patch application/octet-stream 19.9 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2024-06-20 09:31:09 Re: pg_combinebackup --clone doesn't work
Previous Message Kyotaro Horiguchi 2024-06-20 08:30:45 Re: [HACKERS] make async slave to wait for lsn to be replayed