On 2026-01-16 Fr 4:26 PM, Alexandra Wang wrote: [ ... ]
## Summary 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. These are all edge cases and likely low-impact in practice. However, if we were to add broader support for SQL/JSON simplified accessors (dot notation, wildcards, item methods, etc.), which can be chained together and include array access using the existing bracket syntax, it seems useful to clarify what semantics we would want for array access in those situations. For example, with expressions such as select (jb)[0].a select (jb).a[0].b select (jb).a[-1].b (where jb is a jsonb value), it would be potentially confusing if their behavior differed from the equivalent json_query() calls using the same JSON path. Given this, it is unclear to me whether the expectation should be to move closer to the SQL/JSON path semantics, or to preserve the existing jsonb subscripting behavior and document it as PostgreSQL-specific. This question comes up in the context of the dot-notation work I mentioned at the beginning, but more generally it seems like something we may want to be explicit about before extending the syntax further. I would very much appreciate any thoughts or guidance on this.
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.
cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
