MIME-Version: 1.0
References: <CAK98qZ1P_edO9hZJVMuUpnXjN-9H=dz2zUj-06G5qW9CMjLqzA@mail.gmail.com>
 <2cc680c1-12a9-4152-ad31-a1385a9d6912@dunslane.net> <CAK98qZ1kEsF9bwfjftuRfcCX59KChQw52NQ9K4qMMuL3UMHA+Q@mail.gmail.com>
In-Reply-To: <CAK98qZ1kEsF9bwfjftuRfcCX59KChQw52NQ9K4qMMuL3UMHA+Q@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Tue, 10 Feb 2026 11:05:07 +0800
Message-ID: <CACJufxFWcaAywvJeP4eH2k0si1T-gCth4NmCzxvYHAaUd2Li6g@mail.gmail.com>
Subject: Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023)
To: Alexandra Wang <alexandra.wang.oss@gmail.com>
Cc: Andrew Dunstan <andrew@dunslane.net>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, 
	Peter Eisentraut <peter@eisentraut.org>, Jelte Fennema-Nio <postgres@jeltef.nl>, pavel.stehule@gmail.com
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CACJufxFWcaAywvJeP4eH2k0si1T-gCth4NmCzxvYHAaUd2Li6g%40mail.gmail.com>
Precedence: bulk

On Tue, Feb 10, 2026 at 5:16=E2=80=AFAM Alexandra Wang
<alexandra.wang.oss@gmail.com> 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/