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)
