Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vpl5O-00FH9s-1L for pgsql-hackers@arkaria.postgresql.org; Tue, 10 Feb 2026 10:35:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vpl5N-00F4uI-1g for pgsql-hackers@arkaria.postgresql.org; Tue, 10 Feb 2026 10:35:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vpl5N-00F4u9-0Z for pgsql-hackers@lists.postgresql.org; Tue, 10 Feb 2026 10:35:13 +0000 Received: from mail-yw1-x1134.google.com ([2607:f8b0:4864:20::1134]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vpl5K-00000001yXd-1tyR for pgsql-hackers@lists.postgresql.org; Tue, 10 Feb 2026 10:35:12 +0000 Received: by mail-yw1-x1134.google.com with SMTP id 00721157ae682-7961e04355cso39812057b3.3 for ; Tue, 10 Feb 2026 02:35:09 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770719708; cv=none; d=google.com; s=arc-20240605; b=E7fXl/JqxKJna8Jdd4CAIHGr4RTZ8rPr2OiTh0KGxktcoAIo7WmzniDF4xq0nvYbfQ lRs6K0rBzY50Dr+wOOJmeHzwWrsEUChGQT0tRzizJpjEz6f21v8x3UnU7TceIELiD+SZ qTAxUH5d8EKg2NIjihiQf4eoxB/OiESadicB+5mazOKYQ818exPrATpiJO1UyBTEPufH ChjNcB+M4/T3MjC433lprNcADrmRUsDw+vcmcl2dgJCybY1GReYcB0MntR8KhrGwWRvc XXjaC71Y+BEHdXFV4o0TenT5OkOqb2nqh6UuVt4KGjXYZbXaqsB6nOOhUke1QOgOUtto eefg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=q+fmg/JtfOajcoAdPQsxTG12Y6Yetu7NrSG+yU77jEQ=; fh=tiAXuIDRnl9xxSobFN2E2rCCyIx8NcoYlLHXtg217kM=; b=K6ZVvMXwu/wsaBrrLkgMdqy3/k6WP2CWP9tcBa+8EUiH+1U2l5qNARR9WuLDSeO8Ax DZa+uKeLYZc1bnaAJ/HaVxyAjCFa8TKOD7ApSo4eydOksT0KdrOQkmK0nE2R7WCyE3ca IqCZZ21huQUitUNGolJc2Ybr3TwpvpUn69T6G+EAvFfV510qzoNBfOo1GN+GddErfPwl P8W/gRLiKVmqMHCwy0+L6DkjMrghkMD7SnvYlFCMlmdShCcRSrwfFt9FWmPVV6NlNh3T GjLwqlYrKTDkxmpEnNXRYrNYja208Ty7Dx1AZNliPXTpi8jhm/FTGwehvHBsPmU/LGIX QxgA==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1770719708; x=1771324508; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=q+fmg/JtfOajcoAdPQsxTG12Y6Yetu7NrSG+yU77jEQ=; b=fdkVeAOsey+spKFNDBLE8bEtCdwnfTSZIET/t1ydQ0R3F1gntv3CImUxNxiPgrkhdz wn7gLFM5NRESB9pm0xldKE3XGaqe8iDkTuopr2zUtSjDvpklmr+MytDhY30UFf0zEzfp SGKdk5tp5/7+iYg0/vTD1uKzRzFNKpQVF+ALLWlf41lSzbF0keUOLBgGxtA/VJ9lOjxw KNGHLdPK4ZgGDsMn3tTZFaOpF3PBFdeFIK5GHUPF2MpMfFuXEPN8+SmTGxcsgau+pZrk Olc2sSYqJD/OZnXBBnp7an73IfBKAUTAl8V5fDitdJGHBwKbryD2rv7ILR044YCnpYuB KsaQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770719708; x=1771324508; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=q+fmg/JtfOajcoAdPQsxTG12Y6Yetu7NrSG+yU77jEQ=; b=EvFxHHhSsD82gC3QlU1pLp4Hxeoq/ByZMvDUYpVgBqgrEFDz12W0wuNSfQqTCaYUka oEJU0LIeXSQ/ut2AcoYuO4wQom7Ff47XAtIPWAIJ9ZGHG3xRnSO6blxGxnZ9Bw8kdPs1 niHj/IOO6PDGrXZyOxoBvk19iUMQX9SEqtrdyI3oetcmvR2kUixw8Z9T5I/340l2t0Ki ncBKljuQ3oq+M+bW2nCjHIW7We8m9TBsKThAgLhkugznf4qNiOLKGhopSJUJCKI1caiX DfsFf7RXg004dFx4+Xu/qI4uUWR84pfsnG0wBWHv00MYVlyDII85tM87vcnt0wy3Gg9e Bx2Q== X-Forwarded-Encrypted: i=1; AJvYcCWejy56wQ8NS0xIP3bhvO7NN/uoxrIsEdJKwOyQ65a6FTGYjxYBIRG7UtG94eXV2P8jXlW8cZG+5HYTm0ub@lists.postgresql.org X-Gm-Message-State: AOJu0Yz3HxwzGUXM+nZtnhI9Td3qaFmazTfU2f5V88+k+/2frgUVyMaQ 3giqCYqw8f9D92RcrEvWOZrbQemgpH9aXMWuSicohGdyFX0ia+li6VaU+S1Kfnb47kHnAfvTPpD U44cgN8dBdqSFmhRSMqSmt4U+3afJios= X-Gm-Gg: AZuq6aIUAZ8672be2YiXK20WCemL60zBPYD8XU8fRDfpzlfA6+9LoDcMuKBH+8xLY4r v+8jSlnSm1sIXkc7lgzjz5/JcaSv+EA+MlTUTSGf+ccom0aBoS9yHZ2w2E0o1LWX/eyad/CAWb2 aQ3fxXRsRfnQNd2F/NuMQrIXAuil81FYjaEg9kCBDx0oVcCp2nEbESI6uqNA1utRv2kd5X6aFxP uHXQSVh8a8rz9iIQlSsFTplIvaR5XbrQQHEZ+86vPihWYNfaQvMJyztLaqduAJEKVMHEWbJ5NOx INDcWGcEwlAezrO34UOE//Ktwa5JPqPDiLGo11b141tbDZB+Nk1KuRg4bYqiNRZ2OJM1bis+F/F yV57f0LYqIic0RZOzjCtllvzLD7u7+X7IXTu/3se9TQBCztMv2ObqvYTzMQ== X-Received: by 2002:a05:690c:b87:b0:78c:3320:9c5d with SMTP id 00721157ae682-7952ab3bb2amr118271487b3.42.1770719708258; Tue, 10 Feb 2026 02:35:08 -0800 (PST) MIME-Version: 1.0 References: <2cc680c1-12a9-4152-ad31-a1385a9d6912@dunslane.net> In-Reply-To: From: Pavel Stehule Date: Tue, 10 Feb 2026 11:34:31 +0100 X-Gm-Features: AZwV_QjrQkqeXI65BiUXCaEwcIkJN9cmQQDlrnBfUepZWBsmhsVn9DD6bjFLjS8 Message-ID: Subject: Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023) To: Alexandra Wang Cc: Andrew Dunstan , PostgreSQL Hackers , Peter Eisentraut , Jelte Fennema-Nio Content-Type: multipart/alternative; boundary="0000000000003524f3064a75cf83" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003524f3064a75cf83 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable po 9. 2. 2026 v 22:16 odes=C3=ADlatel Alexandra Wang < alexandra.wang.oss@gmail.com> napsal: > 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=E2=80=99d like to hear more opinions before we settle o= n a > direction. > > On Mon, Feb 2, 2026 at 7:32=E2=80=AFAM Andrew Dunstan 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=E2=80=AFAM Jelte Fennema-Nio > 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=E2=80=AFAM Pavel Stehule > 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] =3D 'x' should replace it: > > UPDATE t SET val[0] =3D '"x"' WHERE val =3D '123'; > -- was ERROR, now sets val to "x" > > UPDATE t SET val['a'][0] =3D '"x"' WHERE val =3D '{"a": "hello"}'; > -- 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 > 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 > --0000000000003524f3064a75cf83 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


po 9. 2. 2026 v= =C2=A022:16 odes=C3=ADlatel Alexandra Wang <alexandra.wang.oss@gmail.com> napsal:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">
Hi all,

I've put together patches based on the discussi= on 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 n= egative
subscripts, but I=E2=80=99d like to hear more opinions before we= settle on a
direction.

On Mon, Feb 2, 2026 at 7:32=E2=80=AFAM An= drew Dunstan <a= ndrew@dunslane.net> 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 &= #39;lax'
>> mode wrapping. As a result, "[0]" does n= ot return the original value.
>> 2. Non-integer subscripts are not= supported.
>> 3. Negative subscripts use a PostgreSQL-specific ex= tension.
>
> I'd be inclined to move to the standard for 1 = and 2, and document that
> people might need to reindex after an upgr= ade if they have expression
> indexes, but keep supporting negative s= ubscripts. Not sure how feasible
> that is exactly.

The attach= ed patches implement exactly this:

0001: Add numeric type support wi= th truncation
0002: Add lax mode support for non-array access

On = Sat, Jan 17, 2026 at 1:54=E2=80=AFAM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:>> 3. Negative subscripts use a PostgreSQL-specific extension.
&= gt; I think there are probably people relying on it. And postgres
> b= ehaviour actually seems way more useful than the SQL Standard
> behav= iour.
>
> 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 standa= rd compliant, if that means
> more sensible/useful SQL for their user= s. And since many of their
> users are used to Postgres, they try to = stay PostgreSQL compatible in
> their SQL (unless they think the post= gres behaviour is really
> weird/confusing).
>
> I do wo= nder what other databases do though. Does Oracle, MySQL or
> MSSQL ac= tually follow the standard here? i.e how incompatible is this
> behav= iour in practice with other databases?

I did more research on what o= ther databases do:

Lax mode (integer subscript on non-array):
=C2= =A0 Oracle: lax - SELECT jcol[0] on '{"a":1}' returns {&q= uot;a":1}
=C2=A0 MySQL: lax - SELECT data->'$[0]' on = 9;{"a":1}' returns {"a":1},
although this is not= exactly simplified accessor syntax.
=C2=A0 DuckDB, MSSQL: no lax (retur= n NULL)

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

Negative index support:
=C2=A0 PostgreSQL, Duc= kDB: supported (count from end)
=C2=A0 Oracle, MySQL, MSSQL: not support= ed

On Sat, Jan 17, 2026 at 7:07=E2=80=AFAM Pavel Stehule <pavel.stehule@gmail.c= om> wrote:
> I see some JavaScript philosophy (or HTML) in des= ign of SQL/JSON -
> '{[1,2,3]}'[0,1] -> 1 (SQL/JSON) versu= s -> 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 exac= t 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 a= lso
affects assignment behavior. For put-get consistency, if val[0] read= s
the value, then val[0] =3D 'x' should replace it:

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

UPDATE t SET val['a&= #39;][0] =3D '"x"' WHERE val =3D '{"a": &qu= ot;hello"}';
-- val becomes {"a": "x"}
<= /div>

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

The problem th= at should be well documented is fact, so we can have a json with an array i= nside, but we can have a postgresql's array of json values too - and ca= n 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).=C2= =A0

Regards

Pavel



Direct subscript assignment is a P= ostgreSQL extension anyway - Oracle
uses JSON_TRANSFORM, MySQL uses JSON= _SET, MS SQL uses JSON_MODIFY.=C2=A0 So
we have some freedom here in def= ining the semantics.

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

Best,
Alex

--
Alexandra W= ang<= /div>
--0000000000003524f3064a75cf83--