MIME-Version: 1.0
References: <d2d872c3-84f9-48f6-9963-f4537f02bc91@eisentraut.org>
 <adb15d59-2e6b-4d2e-ac5b-7aaea41f738f@gmail.com>
 <CAK98qZ2QGcyJrJAFv9wjY6S8yP9dUVnmG9Gb4OXuzuMMuM1Z5Q@mail.gmail.com>
 <b24dc997-d78f-47bd-b323-14e68232d127@dunslane.net>
 <21fa36b4-6d56-4556-b524-9e3955b922c8@eisentraut.org>
 <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>
 <c7dc6735-57cb-4287-871f-4c92a7205357@postgresfriends.org>
 <CAN-LCVM6A9z6AzxsEX-vx9=3XgEUU6+zjUqejQw8LfjaRY5P1A@mail.gmail.com>
In-Reply-To: 
 <CAN-LCVM6A9z6AzxsEX-vx9=3XgEUU6+zjUqejQw8LfjaRY5P1A@mail.gmail.com>
From: Alexandra Wang <alexandra.wang.oss@gmail.com>
Date: Mon, 23 Jun 2025 16:34:34 +0200
Message-ID: 
 <CAK98qZ2Pmf6ZSChLq+CEPKJ_8jSa0gFTNJWJTcWbCziDpqa=CA@mail.gmail.com>
Subject: Re: SQL:2023 JSON simplified accessor support
To: Nikita Malakhov <hukutoc@gmail.com>
Cc: 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>, Nikita Glukhov <glukhov.n.a@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 "David E. Wheeler" <david@justatheory.com>,
	jian he <jian.universality@gmail.com>
Content-Type: multipart/alternative; boundary="00000000000075417a06383e1e6e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAK98qZ2Pmf6ZSChLq%2BCEPKJ_8jSa0gFTNJWJTcWbCziDpqa%3DCA%40mail.gmail.com>
Precedence: bulk

--00000000000075417a06383e1e6e
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Nikita,

Thank you so much for reviewing!

On Wed, Apr 23, 2025 at 6:54=E2=80=AFPM Nikita Malakhov <hukutoc@gmail.com>=
 wrote:

> Hi Alex!
>
> Glad you made so much effort to develop this patch set!
> I think this is an important part of Json functionality.
>
> I've looked into you patch and noticed change in behavior
> in new test results:
>
> postgres@postgres=3D# create table t(x int, y jsonb);
> insert into t select 1, '{"a": 1, "b": 42}'::jsonb;
> insert into t select 1, '{"a": 2, "b": {"c": 42}}'::jsonb;
> insert into t select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::jsonb=
;
> CREATE TABLE
> Time: 6.373 ms
> INSERT 0 1
> Time: 3.299 ms
> INSERT 0 1
> Time: 2.532 ms
> INSERT 0 1
> Time: 2.453 ms
>
> Original master:
> postgres@postgres=3D# select (t.y).b.c.d.e from t;
> ERROR:  column notation .b applied to type jsonb, which is not a composit=
e
> type
> LINE 1: select (t.y).b.c.d.e from t;
>                 ^
> Time: 0.553 ms
>
> Patched (with v11):
> postgres@postgres=3D# select (t.y).b.c.d.e from t;
>  e
> ---
>
>
>
> (3 rows)
>
> Is this correct?
>

This is correct.

With this patch, the query should return 3 empty rows. We expect
dot notation to behave the same as the json_query() below in lax mode
with NULL ON EMPTY.

postgres=3D# select json_query(y, 'lax $.b.c.d.e' WITH CONDITIONAL ARRAY
WRAPPER NULL ON EMPTY NULL ON ERROR) from t;
 json_query
------------


(3 rows)

Best,
Alex

--00000000000075417a06383e1e6e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi Nikita,</div><div><br></div><div>Thank you so much=
 for reviewing!</div><br><div class=3D"gmail_quote gmail_quote_container"><=
div dir=3D"ltr" class=3D"gmail_attr">On Wed, Apr 23, 2025 at 6:54=E2=80=AFP=
M Nikita Malakhov &lt;<a href=3D"mailto:hukutoc@gmail.com">hukutoc@gmail.co=
m</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
><div dir=3D"ltr"><div dir=3D"ltr">Hi Alex!<div><br></div><div>Glad you mad=
e so much effort to develop this patch set!</div><div>I think this is an im=
portant part of Json functionality.</div><div><br></div><div>I&#39;ve looke=
d into you patch and noticed change in behavior</div><div>in new test resul=
ts:</div><div><br></div><div>postgres@postgres=3D# create table t(x int, y =
jsonb);<br>insert into t select 1, &#39;{&quot;a&quot;: 1, &quot;b&quot;: 4=
2}&#39;::jsonb;<br>insert into t select 1, &#39;{&quot;a&quot;: 2, &quot;b&=
quot;: {&quot;c&quot;: 42}}&#39;::jsonb;<br>insert into t select 1, &#39;{&=
quot;a&quot;: 3, &quot;b&quot;: {&quot;c&quot;: &quot;42&quot;}, &quot;d&qu=
ot;:[11, 12]}&#39;::jsonb;<br>CREATE TABLE<br>Time: 6.373 ms<br>INSERT 0 1<=
br>Time: 3.299 ms<br>INSERT 0 1<br>Time: 2.532 ms<br>INSERT 0 1<br>Time: 2.=
453 ms</div><div><br></div><div>Original master:</div><div>postgres@postgre=
s=3D# select (t.y).b.c.d.e from t;<br>ERROR: =C2=A0column notation .b appli=
ed to type jsonb, which is not a composite type<br>LINE 1: select (t.y).b.c=
.d.e from t;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^<b=
r>Time: 0.553 ms</div><div><br></div><div>Patched (with v11):</div><div>pos=
tgres@postgres=3D# select (t.y).b.c.d.e from t;<br>=C2=A0e<br>---<br><br><b=
r><br>(3 rows)</div><div><br></div><div>Is this correct?</div></div></div><=
/blockquote><div><br></div><div>This is correct.</div><div><br></div><div>W=
ith this patch, the query should return 3 empty rows. We expect<br>dot nota=
tion to behave the same as the json_query() below in lax mode<br>with NULL =
ON EMPTY.</div></div><div class=3D"gmail_quote gmail_quote_container"><br><=
/div><div class=3D"gmail_quote gmail_quote_container">postgres=3D# select j=
son_query(y, &#39;lax $.b.c.d.e&#39; WITH CONDITIONAL ARRAY WRAPPER NULL ON=
 EMPTY NULL ON ERROR) from t;<br>=C2=A0json_query<br>------------<br><br><b=
r><br>(3 rows)</div><div class=3D"gmail_quote gmail_quote_container"><br></=
div><div class=3D"gmail_quote gmail_quote_container">Best,</div><div class=
=3D"gmail_quote gmail_quote_container">Alex</div></div>

--00000000000075417a06383e1e6e--