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 protected]> 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 protected]> 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 protected]> 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
v1-0001-Support-numeric-type-for-jsonb-subscripting.patch
Description: Binary data
v1-0002-Implement-lax-mode-for-jsonb-subscripting.patch
Description: Binary data
