public inbox for [email protected]  

help / color / mirror / Atom feed

From: jian he <[email protected]>
To: Alexandra Wang <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Jelte Fennema-Nio <[email protected]>
Cc: [email protected]
Subject: Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023)
Date: Tue, 10 Feb 2026 11:05:07 +0800
Message-ID: <CACJufxFWcaAywvJeP4eH2k0si1T-gCth4NmCzxvYHAaUd2Li6g@mail.gmail.com> (raw)
In-Reply-To: <CAK98qZ1kEsF9bwfjftuRfcCX59KChQw52NQ9K4qMMuL3UMHA+Q@mail.gmail.com>
References: <CAK98qZ1P_edO9hZJVMuUpnXjN-9H=dz2zUj-06G5qW9CMjLqzA@mail.gmail.com>
	<[email protected]>
	<CAK98qZ1kEsF9bwfjftuRfcCX59KChQw52NQ9K4qMMuL3UMHA+Q@mail.gmail.com>

On Tue, Feb 10, 2026 at 5:16 AM Alexandra Wang
<[email protected]> wrote:
>
> The attached patches implement exactly this:
>
> 0001: Add numeric type support with truncation

Subject: [PATCH v1 1/2] Support numeric type for jsonb subscripting

Previously, using a numeric value as a jsonb subscript would error.
Now numeric subscripts are accepted and truncated toward zero to
produce an integer index. This matches the semantics of array access
in json_query() per the SQL/JSON standard.

Examples:
  SELECT ('["a","b","c"]'::jsonb)[1.7];   -- returns "b" (truncates to 1)
  SELECT ('["a","b","c"]'::jsonb)[-1.7];  -- returns "c" (truncates to

first thing come to my mind would be special numeric value +inf, -inf, NaN

SELECT ('{"NaN":"b"}'::jsonb)['inf'::numeric];
ERROR:  cannot convert infinity to integer

SELECT ('{"NaN":"b"}'::jsonb)['NaN'::numeric];
ERROR:  cannot convert NaN to integer

Is the above what we expected, or should just return NULL?
Anyway, obviously we need to test these special numeric values.

+select ('[1, "2", null]'::jsonb)[1.5::float8]; -- errors
+ERROR:  subscript type double precision is not supported
+LINE 1: select ('[1, "2", null]'::jsonb)[1.5::float8];
+                                         ^
+HINT:  jsonb subscript must be coercible to either numeric or text.

This errhint message appears to be incorrect?
given that 1.5::float8 is coercible to numeric.



--
jian
https://www.enterprisedb.com/

view thread (4+ messages)  latest in thread

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], [email protected], [email protected], [email protected]
  Subject: Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023)
  In-Reply-To: <CACJufxFWcaAywvJeP4eH2k0si1T-gCth4NmCzxvYHAaUd2Li6g@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