so 17. 1. 2026 v 10:54 odesÃlatel Jelte Fennema-Nio <[email protected]> napsal:
> On Fri, 16 Jan 2026 at 22:27, Alexandra Wang > <[email protected]> wrote: > > In all cases above, json_query() follows the SQL/JSON standard > > specification. jsonb subscripting, which predates the standard, > > differs in several ways: > > My thoughts on changing these 3 behaviours: > > > 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. > > I think it's unlikely anyone cares about the exact behaviour here in > practice. But changing the behaviour could corrupt expression indexes > that use this syntax. > > > 2. Non-integer subscripts are not supported. > > Changing this to not throw an error seems fine to me. Making something > that throw an error, now not throw an error should not cause breakage > for people. The new behaviour would be of arguable usefulness though. > > > 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 would very much appreciate any thoughts or guidance on this. > > If change 3 would not have been there, I would have probably been okay > with changing Postgres to behave like the SQL standard and telling > people to re-index their indexes that use this syntax in that major > release. But I think we should keep our current behaviour for option > 3. > > An approach that I think would be viable to do that is: > 1. Define a new sqlpath mode (e.g. with the name lax_postgres or > something). And define that as our current behaviour (possibly with 2 > changed to behave like lax). > 2. Document that our SQL/JSON simplified accessors diverge slightly > from the SQL standard because they use lax_postgres instead of lax. > > That would mean there's still an easy 1-to-1 translation between the > simplified accessor string and and JSON_QUERY (all that would be > different is the change from lax to lax_postgres in the string) > I am not sure if the implementation of the third method is the best we can do. Described handling of corner cases in SQL/JSON has some logic and consistency, but it is not compatible with the generic philosophy of PostgreSQL arrays. If I know ANSI/SQL doesn't know arrays, so this inconsistency is just a PostgreSQL problem, and because we don't like feature flags, I don't see any solution to how this situation can be solved. Any solution will be ugly. In this situation I prefer current behavior - (inconsistency between array access and JSON_QUERY) with good description in documentation. Theoretically it can be introduced lax_postgres like you propose. But I don't see how it can help with possible compatibility issues when somebody will migrate from other databases. So anything inside JSON_XXXX functions can be rigidly consistent with standard SQL/JSON. Outside should not be true - and it is better to say it explicitly. I don't think introducing some JavaScripts concepts to Postgres (although just for some corner cases) is a good idea (when we have some specific handling of some corner cases too). Regards Pavel
