Horton

Re: SQL:2023 JSON simplified accessor support

Chao Li <li.evan.chao@gmail.com>

View thread
From: Chao Li <li.evan.chao@gmail.com>
To: Alexandra Wang <alexandra.wang.oss@gmail.com>
Cc: Nikita Glukhov <glukhov.n.a@gmail.com>, jian he <jian.universality@gmail.com>, Nikita Malakhov <hukutoc@gmail.com>, Vik Fearing <vik@postgresfriends.org>, Mark Dilger <mark.dilger@enterprisedb.com>, Matheus Alcantara <matheusssilv97@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, "David E. Wheeler" <david@justatheory.com>
Date: 2025-09-10T04:03:36Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add test coverage for indirection transformation

  2. Fix typo in comment

  3. Implementation of subscripting for jsonb


> On Sep 10, 2025, at 07:53, Alexandra Wang <alexandra.wang.oss@gmail.com> wrote:
> 
> I think this (jb)[0] case is rather trivial. However, since it's been
> behaving the pre-standard way since PG14, I hesitate to change the
> existing behavior as part of my patches, and I feel it could be a
> bikeshedding kind of discussion in a separate thread. 


I am fine to retain the PG14 behavior. But the confusion part is:

```
evantest=# select ('{"name": "Alice", "birthday": {"year": 2000, "month": 8}}'::jsonb)[0]['birthday']['year'];
 jsonb
-------

(1 row)

evantest=# select ('{"name": "Alice", "birthday": {"year": 2000, "month": 8}}'::jsonb)[0]['birthday'].year;
 year
------
 2000
(1 row)
```

As long as the expression containing a dot notation, “[0]” behaves differently from PG14.

I agree we should not recommend the mixed usages of `[`a`]` and `.a`, but we haven’t prevented from that. Given they are different meanings, `[‘a’]`  implies strict mode and `.a` implies lax mode, for a complex JSON object, users may intentionally mix use both, which sounds reasonable.

I don’t see you have updated any documentation yet. I don’t want to block you because of this issue. As long as you state the behavior clearly in the document, I am happy to let it go.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/