po 9. 2. 2026 v 22:16 odesílatel Alexandra Wang < [email protected]> 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’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"} > 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 >
