public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexandra Wang <[email protected]>
To: Chao Li <[email protected]>
Cc: Nikita Glukhov <[email protected]>
Cc: jian he <[email protected]>
Cc: Nikita Malakhov <[email protected]>
Cc: Vik Fearing <[email protected]>
Cc: Mark Dilger <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: David E. Wheeler <[email protected]>
Subject: Re: SQL:2023 JSON simplified accessor support
Date: Tue, 9 Sep 2025 16:53:04 -0700
Message-ID: <CAK98qZ1GwW_dCHSi7wMV_H+eQ9qdWajUNXmntufHF4Md=xRSsw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAK98qZ2QGcyJrJAFv9wjY6S8yP9dUVnmG9Gb4OXuzuMMuM1Z5Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com>
<CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com>
<CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com>
<CAK98qZ1rZaVNy6ViangQom4iinZVH7=ebqhTsPxMQbN5ZtE9XQ@mail.gmail.com>
<CAFY6G8cY5SUG5L-0ryVpom6HynE49p8-XQ59qkaEgnZZJ-c4Rg@mail.gmail.com>
<CAFY6G8eUGcU3A4AHprgYbSOAOj7+WGhGxS_YP0wd2+aCpZTiNg@mail.gmail.com>
<CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com>
<CAHgHdKtL9nNaKXGCLt9gWugVzYWKhoBDQ7NESUwdCBty8kFK-A@mail.gmail.com>
<CAK98qZ1nz6ZZhQqTOCNwRguZE5GsBLW5BQT_k=s7AA6gc2CN_g@mail.gmail.com>
<[email protected]>
<CAN-LCVM6A9z6AzxsEX-vx9=3XgEUU6+zjUqejQw8LfjaRY5P1A@mail.gmail.com>
<CAK98qZ2Pmf6ZSChLq+CEPKJ_8jSa0gFTNJWJTcWbCziDpqa=CA@mail.gmail.com>
<CACJufxEt5bL-xYojenA7x1Fq=-DfhW3KGH=3Zz9TjJ1k95=uuQ@mail.gmail.com>
<CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com>
<CACJufxHqiKbh1RN4-rquYdnS8qK9kEQq=bpt6ED_yo1+OkU8jg@mail.gmail.com>
<CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@mail.gmail.com>
<CACJufxGW0Uq2Xx2NLexYR410pvLx2+QKBvBjgM+5qHsqvT5BFQ@mail.gmail.com>
<CAK98qZ3CvSfKS5yV3FAtOpWhbWHkB5aFWrngV_wYnwUbmHF4SQ@mail.gmail.com>
<CACJufxG34m9BGnfD9RD5OEohkV3Oh-+7Xf=3epXyHfQj4DPiOw@mail.gmail.com>
<CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAK98qZ0SUBjETvUT31U01pSKYCuQPznCtb2kf3-jB5+Dh2V03w@mail.gmail.com>
<[email protected]>
Hi Chao,
On Tue, Sep 2, 2025 at 11:57 PM Chao Li <[email protected]> wrote:
> On Sep 3, 2025, at 10:20, Alexandra Wang <[email protected]>
> wrote:
>
> This change would give an incorrect result for an accessor like
> "[0].a" when the jsonb column data is a jsonb object instead of a
> jsonb array. I've added two new test cases to cover this scenario:
>
> In jsonb.sql, the newly added tests are:
> select (jb)[0].a from test_jsonb_dot_notation; -- returns same result as
> (jb).a
> select (jb)[1].a from test_jsonb_dot_notation; -- returns NULL
>
>
> I think the latest patch is still wrong. Ultimately, dot notation “.a",
> index “[0] and slice "[1:4]” rely on jsonpath, and subscript [“a”] relies
> on the rest logic in jsonb_subscript_transform() in “foreach”.
>
> Now “jsonb_check_jsonpath_needed()” checks only dot nation and slice, but
> not checking index case. So that reason why your case “select (jb)[0].a”
> works is because the second indirection is a dot nation. However, “select
> (jb)[0][‘a’]” will not work. See my test:
>
> ```
> evantest=# select ('{"name": "Alice", "age": 30}'::jsonb)[0].name;
> name
> ---------
> "Alice"
> (1 row)
>
> evantest=# select ('{"name": "Alice", "age": 30}'::jsonb)[0]['name'];
> jsonb
> -------
>
> (1 row)
> ```
>
> In my test, (jsonb)[0][’name’] should also return “Alice”.
>
> So, end up, “jsonb_check_jsonpath_needed()” only does incomplete and
> inaccurate checks, only jsonb_subscript_make_jsonpath() can make an
> accurate decision and return a null jsonpath upon subscript “[‘a’]”.
>
> As a result, “json_check_jsonpath_needed()” feels not needed at all. In
> jsonb_subscript_transform(), just go ahead to run
> jsonb_subscript_make_jsonpath() first, if returned jsonpath is NULL, then
> run rest of logic.
>
> With my dirty change of removing json_check_jsonpath_needed:
> ```
> chaol@ChaodeMacBook-Air postgresql % git diff
> diff --git a/src/backend/utils/adt/jsonbsubs.c
> b/src/backend/utils/adt/jsonbsubs.c
> index 374040b3b4e..d9faab5c799 100644
> --- a/src/backend/utils/adt/jsonbsubs.c
> +++ b/src/backend/utils/adt/jsonbsubs.c
> @@ -416,12 +416,12 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
> sbsref->refrestype = JSONBOID;
> sbsref->reftypmod = -1;
>
> - if (jsonb_check_jsonpath_needed(*indirection, isSlice))
> - {
> + //if (jsonb_check_jsonpath_needed(*indirection, isSlice))
> + //{
> jsonb_subscript_make_jsonpath(pstate, indirection, sbsref);
> if (sbsref->refjsonbpath)
> return;
> - }
> + //}
>
> /*
> * We reach here only in two cases: (a) the JSON simplified
> accessor is
> ```
>
> You can see:
> ```
> evantest=# select ('{"name": "Alice", "age": 30}'::jsonb)[0]['name'];
> jsonb
> ---------
> "Alice"
> (1 row)
>
> evantest=# select ('{"name": "Alice", "age": 30}'::jsonb)[0].name;
> name
> ---------
> "Alice"
> (1 row)
> ```
>
> Then I found “make check” failed. For example the first broken test case:
>
> ```
> @@ -4998,7 +4998,7 @@
> select ('123'::jsonb)[0];
> jsonb
> -------
> -
> + 123
> (1 row)
> ```
>
> The test expected an empty result, which implies “strict” mode.
>
> But the problem is, which mode should be the default? JSON_QUERY() uses
> “lax” as default mode. And from Peter Eisentraut’s blog:
> https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-whats-new
>
> ```
> The semantics of this are defined in terms of JSON_QUERY and JSON_VALUE constructions
> (which have been available since SQL:2016), so this really just syntactic
> sugar.
> ```
>
> Also feels like “lax” should be the default mode. If that is true, then my
> dirty change of removing “json_check_jsonpath_need()” works properly.
>
> The current logic with this patch sounds strange. Because
> “json_check_jsonpath_need()” iterate through unprocessed indirections to
> decide if jsonpath is needed (lax mode). With this logic:
>
> 1) if index [0] directly following dot notation, like (data).a[0], it’s
> lax mode
> 2) if index [0] directly following subscript [‘a’], like (data)[‘a’][0],
> it’s strict mode
> 3) if index [0] directly following the data column, then if there is a dot
> nation in indirection list, use lax mode, otherwise strict mode. For the
> failed test case, as there is no more indirection following [0], so it
> expected strict mode.
>
> I wonder where this behavior is defined?
>
> With my change, 1) and 2) are the same, for 3), if index [0] directly
> following the data column, regardless what indirections are followed, it’s
> by default lax mode.
>
> So, I think this is a design decision. Maybe I missed something from your
> previous design, but I don’t find anything about that from the commit
> comments. I feel this would be better aligned with 1) and 2).
>
Thanks for investigating this, and for the great questions!
Here’s a bit of background. The pre-standard jsonb subscripting
feature [1] was introduced in Postgres 14. Specifically, support for
queries like (jb)[0] and (jb)[0]['a'] was added by this commit:
commit 676887a3b0b8e3c0348ac3f82ab0d16e9a24bd43 (HEAD)
Author: Alexander Korotkov <[email protected]>
Date: Sun Jan 31 23:50:40 2021 +0300
Implementation of subscripting for jsonb
Without my patch, from version 14 through the current master branch,
all supported versions of Postgres return the following results:
test=# select ('123'::jsonb)[0];
jsonb
-------
(1 row)
test=# select ('{"name": "Alice", "age": 30}'::jsonb)[0];
jsonb
-------
(1 row)
test=# select ('{"name": "Alice", "age": 30}'::jsonb)[0]['age'];
jsonb
-------
(1 row)
The simplified accessor syntax defined in the SQL standard includes
dot-notation access as well as integer-based subscripts. It does not
include text-based subscripts such as ['a']. Therefore, the only
overlap between the pre-standard jsonb subscripting and the standard
simplified accessor is non-slicing integer-based subscripts. (Since
the pre-standard jsonb subscripting never supported slicing, we don’t
need to consider that case either when comparing the two syntaxes.)
When we compare the two syntaxes for non-slicing integer-based
subscripts, the only discrepancy is that for a jsonb object jb,
(jb)[0] in the pre-standard syntax returns NULL, while (jb)[0] in the
standard syntax returns (jb) itself. As long as the integer subscript
is non-zero, both syntaxes return the same results.
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.
The main goal of my patch is to add dot-notation. For now, my
intention is for cases like (jb)[0].a to work in the standard way,
because if a user chooses dot-notation instead of text-based
subscripting, they are likely expecting the standard behavior.
Thoughts?
[1]
https://www.postgresql.org/docs/current/datatype-json.html#JSONB-SUBSCRIPTING
view thread (68+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL:2023 JSON simplified accessor support
In-Reply-To: <CAK98qZ1GwW_dCHSi7wMV_H+eQ9qdWajUNXmntufHF4Md=xRSsw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox