Subject: Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023)

From: Alexandra Wang <[email-masked]>
Date: Mon, 9 Feb 2026 13:15:54 -0800

Hi all,

I've put together patches based on the discussion so far to help move
things forward. The current vote seems to be 2:1 in favor of
implementing lax mode and numeric truncation while keeping negative
subscripts, but I’d like to hear more opinions before we settle on a
direction.

On Mon, Feb 2, 2026 at 7:32 AM Andrew Dunstan <[email-masked]> wrote:
>
> On 2026-01-16 Fr 4:26 PM, Alexandra Wang wrote:
>> In all cases above, json_query() follows the SQL/JSON standard
>> specification. jsonb subscripting, which predates the standard,
>> differs in several ways:
>>
>> 1. Array access on non-arrays (scalars or objects) does not use 'lax'
>> mode wrapping. As a result, "[0]" does not return the original value.
>> 2. Non-integer subscripts are not supported.
>> 3. Negative subscripts use a PostgreSQL-specific extension.
>
> I'd be inclined to move to the standard for 1 and 2, and document that
> people might need to reindex after an upgrade if they have expression
> indexes, but keep supporting negative subscripts. Not sure how feasible
> that is exactly.

The attached patches implement exactly this:

0001: Add numeric type support with truncation
0002: Add lax mode support for non-array access

On Sat, Jan 17, 2026 at 1:54 AM Jelte Fennema-Nio <[email-masked]>
wrote:
>> 3. Negative subscripts use a PostgreSQL-specific extension.
> I think there are probably people relying on it. And postgres
> behaviour actually seems way more useful than the SQL Standard
> behaviour.
>
> You said DuckDB does the same as Postgres. That doesn't surprise me
> much, since DuckDB usually defaults to Postgres behaviour. They don't
> care much about being strictly sql standard compliant, if that means
> more sensible/useful SQL for their users. And since many of their
> users are used to Postgres, they try to stay PostgreSQL compatible in
> their SQL (unless they think the postgres behaviour is really
> weird/confusing).
>
> I do wonder what other databases do though. Does Oracle, MySQL or
> MSSQL actually follow the standard here? i.e how incompatible is this
> behaviour in practice with other databases?

I did more research on what other databases do:

Lax mode (integer subscript on non-array):
  Oracle: lax - SELECT jcol[0] on '{"a":1}' returns {"a":1}
  MySQL: lax - SELECT data->'$[0]' on '{"a":1}' returns {"a":1},
although this is not exactly simplified accessor syntax.
  DuckDB, MSSQL: no lax (return NULL)

Numeric truncation:
  DuckDB: supports rounding
  Oracle has odd behavior - jcol[3.0] on '{"a":123}' also returns
  {"a":123}, same as [0]. Not sure what's happening there.
  MySQL and MSSQL only accept integers in their JSON path syntax.

Negative index support:
  PostgreSQL, DuckDB: supported (count from end)
  Oracle, MySQL, MSSQL: not supported

On Sat, Jan 17, 2026 at 7:07 AM Pavel Stehule <[email-masked]>
wrote:
> I see some JavaScript philosophy (or HTML) in design of SQL/JSON -
> '{[1,2,3]}'[0,1] -> 1 (SQL/JSON) versus -> NULL (Postgres)

I believe Pavel is referring to the lax mode behavior that the
standard defines for the json simplified accessor, am I right? This is
indeed a philosophical difference, and so far the exact syntax is only
implemented in Oracle (MySQL also has lax mode behavior, but not
exactly using the simplified accessor syntax). If others share this
concern about lax mode being too lenient, I'd like to hear it.

One thing we didn't discuss explicitly: implementing lax mode also
affects assignment behavior. For put-get consistency, if val[0] reads
the value, then val[0] = 'x' should replace it:

UPDATE t SET val[0] = '"x"' WHERE val = '123';
-- was ERROR, now sets val to "x"

UPDATE t SET val['a'][0] = '"x"' WHERE val = '{"a": "hello"}';
-- val becomes {"a": "x"}

Direct subscript assignment is a PostgreSQL extension anyway - Oracle
uses JSON_TRANSFORM, MySQL uses JSON_SET, MS SQL uses JSON_MODIFY.  So
we have some freedom here in defining the semantics.

I'd appreciate more votes on whether this direction makes sense, or if
there are concerns I've missed.

Best,
Alex

-- 
Alexandra Wang
EDB: https://www.enterprisedb.com

---

From: jian he <[email-masked]>
Date: Tue, 10 Feb 2026 11:05:07 +0800

> 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/

---

From: Pavel Stehule <[email-masked]>
Date: Tue, 10 Feb 2026 11:34:31 +0100

> -- val becomes {"a": "x"}

When I see this, then I prefer a strict SQL/JSON  behaviour and well and
strong documentation. Introducing one's own behaviour is way to hell. This
is too complex, and maintaining one's own behavior is against users. I
dislike it, but the best we can do is respect the standard.

The problem that should be well documented is fact, so we can have a json
with an array inside, but we can have a postgresql's array of json values
too - and can be pretty messy, because the behaviour can be different. I am
not sure if ANSI/SQL knows arrays (there are only sets if I remember it
correctly).

Regards

Pavel

---

From: Peter Eisentraut <[email-masked]>
Date: Tue, 31 Mar 2026 16:43:00 +0200

> there are concerns I've missed.

At the moment, I see no consensus here, in fact, it seems there are more 
opinions than options. ;-)  I think this is an important topic to 
resolve one way or another, and there also appear to be at least some 
documentation gaps if not bugs in the existing code.  I created a 
commitfest entry for this for PG20-1, so we can continue tracking this.