Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1usvtU-00DFxN-96 for pgsql-hackers@arkaria.postgresql.org; Mon, 01 Sep 2025 04:11:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1usvtT-004D9l-Mm for pgsql-hackers@arkaria.postgresql.org; Mon, 01 Sep 2025 04:11:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1usvtT-004D9c-8o for pgsql-hackers@lists.postgresql.org; Mon, 01 Sep 2025 04:11:48 +0000 Received: from mail-pf1-x441.google.com ([2607:f8b0:4864:20::441]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1usvtQ-002mGD-2z for pgsql-hackers@postgresql.org; Mon, 01 Sep 2025 04:11:46 +0000 Received: by mail-pf1-x441.google.com with SMTP id d2e1a72fcca58-772627dd50aso111225b3a.1 for ; Sun, 31 Aug 2025 21:11:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1756699904; x=1757304704; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=kuEnT/0qaalDCF9S0Fm9fjNZasKtEFT0x6IZINwRyGw=; b=mhDL28r8xGX3nsICO4iuOxyPiK7acVxl12AR+HmYcAkkw8z8CwakR5ucOW2rYf1Rfh 5MoR0xMZVJdLOs7pn3sH7znaNa3JJkfGG+TkpZJXBRCi9Z4rWEGFKrY5l3KC1/tToglt uc/q2rjL9Ww4MnEGACKNe+cqRHdjtGcrHa8rOaKihtmNpho+hdG+9TMlRMwjMmhsysWm NX1/iAysBj6Y5VtrIwr3Y6ogZMXZY4Qay/AE0crxhsB7nXiKhClOFXQH11+ZTNDPiRNF 8GoxczdGdG6MwzhdMaiq5tMw5urTeugHTxQUnralcjUSH7iwNRIoGcQEZvFZT9Iwwdhq BI1A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1756699904; x=1757304704; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=kuEnT/0qaalDCF9S0Fm9fjNZasKtEFT0x6IZINwRyGw=; b=AWFetZtjJ/JF7rlZC/ctVlpUHWbRaGsoM7sQ107WEfxORooyW6cDUsqak7+7yYkgGB LUNKgY/gLXqljmVxuAlT6BGUCRyOFOmcc26QqpHh4fH+TvywltZPf6W+2BAaSPXxTE3Q boi1edJvQhgSfXKjwvhk/0dpE8CONexhH2YDRUJmFUQWvTYfhkMOTNJOaTpL7armbBAe 5SiYM5j+dMXfYQjZJ0JbShMo5Z/9gF1hiKXjE6Z5VT18HTZ0Zsw1YCf1vL47zIcB/09e MH0zIY/nBXbsmov4GmR91+olIh5bf4nOhS8GyTh+gH9OhWs30bfzp+pRfjJF6Ti66qEi 8YUg== X-Forwarded-Encrypted: i=1; AJvYcCUQLNgNP4O0bdAp0+zKruy2MiCo9v/rMEkYx4tqzn52sfoc60UhOawEycpLWXnRLP9K5jDjqWnhqo2YVuxu@postgresql.org X-Gm-Message-State: AOJu0YxLtypYgSNWWTv0vSqjV/g7ZR3dIesg6t97gMFiZC2ZawFQzkRx Go1C9TuIrsGrrcbW8yE7pQE6E4oq06F09F1GrAQMMNp+HyH5UDOARGlB4LfXYFYf4VrFqPDndJ/ UvCP/8EsRoNxpbpjeAqZn4lcg43nomNY= X-Gm-Gg: ASbGncsvW9Cq1xvXMGjigFO6fYVidUD/dul1y+/fSfhCz0tFLW88EbjXGenA27U8BW/ Ld6awqqZFV3PdLw2lqGroByzlG74nACSdefI4JC6oZk4xAju4i27FnJ4yTmvUx1QnQp0uNbGt8o oGnBFYRnaQ+grk5bFhPjVjwFnyOGEuhmOjHFkpZVRcmTOd32+N4IG7rJmHz7La6JGG4mNpH5CZh 2PLKw4= X-Google-Smtp-Source: AGHT+IELdAsN9zXPDJ+XqQddo82ahDaeB2aS6+ANW7i+39K7V2BjEUwEOh+kifMbbHiVkYaWLa6ftOlUDLfw4QrcOUU= X-Received: by 2002:a05:6a20:9190:b0:243:6e5a:51f4 with SMTP id adf61e73a8af0-243d6f03076mr7872419637.19.1756699904437; Sun, 31 Aug 2025 21:11:44 -0700 (PDT) MIME-Version: 1.0 References: <21fa36b4-6d56-4556-b524-9e3955b922c8@eisentraut.org> <3EF2F248-1881-4764-96D7-CC0957AC68F7@gmail.com> In-Reply-To: <3EF2F248-1881-4764-96D7-CC0957AC68F7@gmail.com> From: Alexandra Wang Date: Sun, 31 Aug 2025 21:11:08 -0700 X-Gm-Features: Ac12FXzbBk5Tkgbrs64SdEiWWv0GnunV0FmFn5_1BbEUPUpKim4VSWpS-gUDqKs Message-ID: Subject: Re: SQL:2023 JSON simplified accessor support To: Chao Li Cc: jian he , Nikita Malakhov , Vik Fearing , Mark Dilger , Matheus Alcantara , Peter Eisentraut , Andrew Dunstan , Nikita Glukhov , PostgreSQL Hackers , "David E. Wheeler" Content-Type: multipart/alternative; boundary="000000000000c7f1e4063db59133" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c7f1e4063db59133 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Chao, On Thu, Aug 28, 2025 at 8:42=E2=80=AFPM Chao Li wr= ote: > I am trying to split different topics to different email to keep every > issue to be focused. > Sure! On Thu, Aug 28, 2025 at 8:42=E2=80=AFPM Chao Li wr= ote: > I also have a suggestion. > > If I do: > > ``` > =E2=80=94 s1 > select (t.data)['con']['a'][1]['b']['c']['d'] from test_jsonb_types t; > > =E2=80=94s2 > select (t.data).con.a[1].b['c'].d from test_jsonb_types t; > ``` > > The two statements are actually identical. But they generate quite > different rewritten query trees. S1=E2=80=99s rewritten tree is much simp= ler than > s2=E2=80=99s. However, their plan trees are the same. > The above two statements are NOT identical. Specifically, dot-notation (e.g., .con) and pre-standard jsonb subscripting (e.g., ['con']) are NOT semantically the same. Here's an example: -- setup create table t (jb jsonb); insert into t SELECT '{"con": 1}'::jsonb; insert into t SELECT '[{"con": 1}, {"con": {"a": 2}}]'::jsonb; -- queries test=3D# select (t.jb).con from t; con --------------- 1 [1, {"a": 2}] (2 rows) test=3D# select (t.jb)['con'] from t; jb ---- 1 (2 rows) As you can see, dot-notation returns different results from jsonb subscripting. As I mentioned in the previous reply: The SQL standard states that simplified access is equivalent to: > JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON > EMPTY NULL ON ERROR > ) > where: > VEP =3D > JC =3D And > *In lax mode:* > *=E2=80=94 If an operation requires an SQL/JSON array but the operand is = not an > SQL/JSON array, then the operand is first =E2=80=9Cwrapped=E2=80=9D in an= SQL/JSON array > prior to performing the operation.* > *=E2=80=94 If an operation requires something other than an SQL/JSON arra= y, but > the operand is an SQL/JSON array, then the operand is =E2=80=9Cunwrapped= =E2=80=9D by > converting its elements into an SQL/JSON sequence prior to performing the > operation.**=E2=80=94 After applying the preceding resolutions to structu= ral > errors, if there is still a structural error , the result is an empty > SQL/JSON sequence.* The example query demonstrates the second point above. The dot-notation attempts to access a member field (."con") of a JSON object, while the operand is a JSON array ([{"con": 1}, {"con": {"a": 2}}]). In "lax" mode, the operand is "unwrapped" into a JSON sequence (two elements: {"con": 1} and {"con": {"a": 2}}), and the member field access is performed on each element. The multiple results are then wrapped into a JSON array ([1, {"a": 2}]) due to WITH CONDITIONAL ARRAY WRAPPER. I=E2=80=99ve already explained what "ARRAY WRAPPER" does in = my previous reply, so I won't repeat it here. Best, Alex --000000000000c7f1e4063db59133 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Chao,

On Thu, Aug 28, 2025 at= 8:42=E2=80=AFPM Chao Li <li.e= van.chao@gmail.com> wrote:
I am trying to split different topics to different = email to keep every issue to be focused.

<= /div>
Sure!
=C2=A0
On Thu, Aug 28, 2025 at 8:42=E2= =80=AFPM Chao Li <li.evan.chao= @gmail.com> wrote:
I also have a suggestion.

If I do:=

```
=E2=80=94 s1
select (t.da= ta)['con']['a'][1]['b']['c']['d'] f= rom test_jsonb_types t;

=E2=80=94s2
sele= ct (t.data).con.a[1].b['c'].d from test_jsonb_types t;
``= `

The two statements are actually identical. But t= hey generate quite different rewritten query trees. S1=E2=80=99s rewritten = tree is much simpler than s2=E2=80=99s. However, their plan trees are the s= ame.

The above two statements a= re NOT identical. Specifically, dot-notation
(e.g., .con) and pre-standa= rd jsonb subscripting (e.g., ['con']) are
NOT semantically the s= ame.

Here's an example:

-- setup
create table t (jb jsonb);
insert into = t SELECT '{"con": 1}'::jsonb;
insert into t SEL= ECT '[{"con": 1}, {"con": {"a": 2}}]'= ::jsonb;

-- queries
test=3D# select (t.j= b).con from t;
=C2=A0 =C2=A0 =C2=A0 con
---------------
=C2=A01=C2=A0[1, {"a": 2}]
(2 rows)

test=3D= # select (t.jb)['con'] from t;
=C2=A0jb
----
=C2=A01
(2 rows)

As you can see, dot-notation returns di= fferent results from jsonb
subscripting.

As= I mentioned in the previous reply:

=C2=A0 =C2=A0 The SQL standard states that= simplified access is equivalent to:
=C2=A0 =C2=A0 JSON_QUERY (VEP, '= ;lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR)

=C2= =A0 =C2=A0 where:
=C2=A0 =C2=A0 =C2=A0 VEP =3D <value expression prim= ary>
=C2=A0 =C2=A0 =C2=A0 JC =3D <JSON simplified accessor op chai= n>

And=C2=A0

In lax mode:
=E2=80= =94 If an operation requires an SQL/JSON array but the operand is not an SQ= L/JSON array, then the operand is first =E2=80=9Cwrapped=E2=80=9D in an SQL= /JSON array prior to performing the operation.
=E2=80=94 If an op= eration requires something other than an SQL/JSON array, but the operand is= an SQL/JSON array, then the operand is =E2=80=9Cunwrapped=E2=80=9D by conv= erting its elements into an SQL/JSON sequence prior to performing the opera= tion.
=E2=80=94 After applying the preceding resolutions to struc= tural errors, if there is still a structural error , the result is an empty= SQL/JSON sequence.
The example query demonstrates the second point above. The
= dot-notation attempts to access a member field (."con") of a JSON=
object, while the operand is a JSON array ([{"con": 1}, {&quo= t;con": {"a":
2}}]). In "lax" mode, the operand= is "unwrapped" into a JSON sequence
(two elements: {"con= ": 1} and {"con": {"a": 2}}), and the member field=
access is performed on each element. The multiple results are then
w= rapped into a JSON array ([1, {"a": 2}]) due to WITH CONDITIONAL<= br>ARRAY WRAPPER. I=E2=80=99ve already explained what "ARRAY WRAPPER&q= uot; does in my
previous reply, so I won't repeat it here.
=C2=A0
Best,
Alex
=C2=A0
--000000000000c7f1e4063db59133--