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 1uTi4P-008XMP-1N for pgsql-hackers@arkaria.postgresql.org; Mon, 23 Jun 2025 14:22: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 1uTi4N-003Tes-5T for pgsql-hackers@arkaria.postgresql.org; Mon, 23 Jun 2025 14:22:47 +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 1uTi4M-003Tei-Nq for pgsql-hackers@lists.postgresql.org; Mon, 23 Jun 2025 14:22:47 +0000 Received: from mail-pf1-x443.google.com ([2607:f8b0:4864:20::443]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uTi4L-003Yfx-1j for pgsql-hackers@postgresql.org; Mon, 23 Jun 2025 14:22:46 +0000 Received: by mail-pf1-x443.google.com with SMTP id d2e1a72fcca58-7426c44e014so3671425b3a.3 for ; Mon, 23 Jun 2025 07:22:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1750688564; x=1751293364; 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=54y5MvjhcDBME8VfY258kp6FJUpjz7YK8+3MSMd1G2o=; b=Jfk5AleksgfzOo9MDMp+S/snGvtR56NexgfiDZ/D0OqsL7/z49PYyoggEiMcOQlX+c dNww0TdX71fYHOCY9BvV9u2hPNo177cJXqR/cvjmayKWGEw2tMyyD8ufvRRENjMORTqg FCoAbocpTmo6I1uSYcPxc12f3JNnym1o5tF9Pusb9f3byB4P5EWo0RQeZyzSZcoNal7b knQ2GyAZyLZTpwWLFCZCb+0PNlfYRIhk1PAR6o1EQkf1JlpIs0+I8aJnsijAeK2sqwrO O5GPi2Z8UrGzd6hHxYw8hhSoxuORFAZFVUyTlTOhDGAQdi2J5oFWx230ebPKs48JlA2E RyiQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1750688564; x=1751293364; 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=54y5MvjhcDBME8VfY258kp6FJUpjz7YK8+3MSMd1G2o=; b=RWXRBcAosPQ3qAhGYdJIPwEVwu8wH/hewYFLPQZMRLsHM7RU8zLgLqK+CrJswWgaS2 k6N0Ov60swrlfBNMMxo/V6g0mINna0lJakZL59PUArUL0pCSciEVDt9XKqo9CmzCwpwo Chx4UQIl4ONzJzm/TikTQrQ9EjpiVc4leWG+9U1QogAL7GTl83aCZzzAJC46FYCsYKcO m8LZXM7shu3lyZ6rOH/848fI+pnxexFCn66QAp0TLy4rkkT40iUPcy/w2HnTa3R27kIm yu/TJITQC/s9H4w1+3pMo1R92vEzkL1Pib6VEpPywxOogcRxpyr583pz0pibQcj8YvpO AN2A== X-Forwarded-Encrypted: i=1; AJvYcCXkCoUAWJUb7jwDZcerhWTTOn6eiPc2VvdfTz9rruHylypWGiOXpL3kg5olstIY0HvO+9ZIhHO+4vdd2ajo@postgresql.org X-Gm-Message-State: AOJu0Yz2gvaIBH9U5Aoes4cebRp1zBNKGqvqNGbzx7L/WriIHeJ5q1zb /92XXientyCnmtdbKfrs5dq6VhMZ8mHWb79B5HOyGI0fy4aSr7VSVP8dwkU9oJ8/S629oVoo+AE q8mEdU81CF9Y1VLMsKM5T41+mFStBEVw= X-Gm-Gg: ASbGncvXvJfml9HY7qVTU0OjbVmG7mxCulE4Yq/NJNYFRWo/toC4IhDFwlxGJtrvtD6 Q9rLqbxjbyNdCD/1Chfj+S/FE0NicVfGS0EHkGaO9kRi1EOz7KSVYJyDmr8a1HeMH4pXbQrOS15 uJeDc8ZHucPhgyEMXxVCaXJZd8FPYLg4JaNDj6Kl6saRj8TK/dpdhniOJUttHIbZGcbirPKUa21 F6z8Q== X-Google-Smtp-Source: AGHT+IEF4DckhCbBCXFEEPxA/6r9/TX14Dkgv38HJYYc/VAHbVaggP+GNORYBK9AM48kvfBcuycCrtsDk6z0G7vXhOY= X-Received: by 2002:a05:6a00:2d08:b0:742:3fe0:8289 with SMTP id d2e1a72fcca58-7490d6f23f2mr18628744b3a.20.1750688564469; Mon, 23 Jun 2025 07:22:44 -0700 (PDT) MIME-Version: 1.0 References: <21fa36b4-6d56-4556-b524-9e3955b922c8@eisentraut.org> In-Reply-To: From: Alexandra Wang Date: Mon, 23 Jun 2025 16:22:07 +0200 X-Gm-Features: AX0GCFvQIGsS-qo3nfK7AGulEGU36PovtfUENigGDQtkFzB--73fw4RQJTrKrJk Message-ID: Subject: Re: SQL:2023 JSON simplified accessor support To: Vik Fearing Cc: Mark Dilger , Matheus Alcantara , Peter Eisentraut , Andrew Dunstan , Nikita Glukhov , PostgreSQL Hackers , "David E. Wheeler" , jian he Content-Type: multipart/alternative; boundary="000000000000ff638c06383df132" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ff638c06383df132 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Vik, On Thu, Mar 27, 2025 at 3:27=E2=80=AFPM Vik Fearing wrote: > > I am reviewing this from a feature perspective and not from a code > perspective. On the whole, this looks good to me from a standards point > of view. > Thank you so much for reviewing! On Thu, Mar 27, 2025 at 3:27=E2=80=AFPM Vik Fearing wrote: > There are two things that stand out to me about this feature: > > > 1) I am not seeing the ** syntax in the standard, so does it need to be > signaled as not supported? Perhaps I am just overlooking something. > I think you=E2=80=99re referring to patch v11-0008, which adds the ** token= in the lexer. You=E2=80=99re right =E2=80=94 the ** syntax is not mentioned at= all in the SQL standard. I added lexer support for ** based on earlier feedback from Mark Dilger. See his comment below: On Thu, Mar 13, 2025 at 3:02=E2=80=AFPM Alexandra Wang wrote: > On Tue, Mar 4, 2025 at 8:05=E2=80=AFAM Mark Dilger > wrote: > >> >> > +SELECT (jb).a.**.x FROM test_jsonb_dot_notation; -- not supported >> +ERROR: syntax error at or near "**" >> +LINE 1: SELECT (jb).a.**.x FROM test_jsonb_dot_notation; >> >> I wonder if it would be better to have the parser handle this case and >> raise a ERRCODE_FEATURE_NOT_SUPPORTED instead? >> > > In 0008 I added a new token named "DOUBLE_ASTERISK" to the lexers to > represent "**". Hope this helps! > The ** tests are there because ** is valid in jsonpath, and can be used in functions like json_query() and jsonb_path_query(). So I think we should make it explicit to users that ** is not supported by dot access. As long as the tests make that clear, I don=E2=80=99t have a strong prefere= nce whether we throw a syntax error in the lexer or a =E2=80=9Cfeature not supported=E2=80=9D error in the parser. If we prefer a syntax error, I=E2= =80=99m happy to drop patch 0008. On Thu, Mar 27, 2025 at 3:27=E2=80=AFPM Vik Fearing wrote: > 2) There is no support for . Since this appears to be > constructing a jsonpath query, could that not be added to the syntax and > allow jsonpath to throw the error if the function doesn't exist? > You=E2=80=99re right =E2=80=94 there=E2=80=99s currently no support for . I intentionally didn=E2=80=99t include it in this patch because it would agai= n expand the scope of work. Supporting seems non-trivial to me, because the current design transforms expression nodes separately for each indirection. I previously tried a patch that simply converted the full chain of indirections into a JSON_QUERY() call in transformIndirection(). The problem with that approach is that in EXPLAIN output or view definitions, we=E2=80=99d see the rewritten JSON_QUERY() instead of the original dot notation =E2=80=94 which feels lik= e a leaky abstraction. To support item methods, I think we=E2=80=99d need to add a new kind of indirection to represent function calls. Then, we could either: a) explicitly add each item method token in gram.y, similar to the list of method keywords in jsonpath_gram.y; or b) allow a generic method-call token in gram.y, and transform it into the corresponding JsonPathItemType that jsonpath understands. Either way, I=E2=80=99m happy to work on it, but would prefer to discuss an= d implement it in a separate follow-up patch. P.S. After reading your second question, your first one makes more sense to me =E2=80=94 I=E2=80=99ve already taken the easy route of not pars= ing item methods and was planning to leave that work for a follow-up patch. So maybe it=E2=80=99s more consistent to just let the syntax error for ** happ= en, rather than explicitly throwing a =E2=80=9Cfeature not supported=E2=80=9D e= rror? The only difference, I think, is that we don=E2=80=99t want ** in simplifie= d accessor syntax, whereas we do want to support item methods in the future. Let me know what you think! Best, Alex --000000000000ff638c06383df132 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0Vik,

On Thu, Mar 27, 2025 at 3:27=E2=80=AFPM Vik Fearing <vik@postgresfriends.org> wrote:

I am reviewing this from a feature perspective and not from a code
perspective.=C2=A0 On the whole, this looks good to me from a standards poi= nt
of view.

Thank you so much for reviewin= g!
=C2=A0
On Thu, Mar 27, 2025 at 3:27=E2=80=AFPM Vik F= earing <vik@postgresfriends.o= rg> wrote:
There are two things that stand out to me about this feature:


1) I am not seeing the ** syntax in the standard, so does it need to be signaled as not supported?=C2=A0 Perhaps I am just overlooking something.

I think you=E2=80=99re referring to patc= h v11-0008, which adds the ** token in
the lexer. You=E2=80=99re right = =E2=80=94 the ** syntax is not mentioned at all in the
SQL standard. I a= dded lexer support for ** based on earlier feedback
from Mark Dilger. Se= e his comment below:

On Thu, Mar 1= 3, 2025 at 3:02=E2=80=AFPM Alexandra Wang <alexandra.wang.oss@gmail.com> wrote:
On Tue, = Mar 4, 2025 at 8:05=E2=80=AFAM Mark Dilger <mark.dilger@enterprisedb.com> = wrote:
=C2=A0
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">
+SELECT (jb).a.**.x FROM test_jsonb_dot_notati= on; -- not supported
+ERROR: =C2=A0syntax error at or near "**"= ;
+LINE 1: SELECT (jb).a.**.x FROM test_jsonb_dot_notation;

I wonder if it would be better to have the parser handle t= his case and raise a=C2=A0ERRCODE_FEATURE_NOT_SUPPORTED instead?

In 0008 I added a new token n= amed "DOUBLE_ASTERISK" to the lexers to
represent "= ;**". Hope this helps!=C2=A0

The ** tests are there because ** is valid in js= onpath, and can be
used in functions like json_query() and jsonb_path_qu= ery(). So I think
we should make it explicit to users that ** is not sup= ported by dot
access.

As long as the tests make that clear, I don= =E2=80=99t have a strong preference
whether we throw a syntax error in t= he lexer or a =E2=80=9Cfeature not
supported=E2=80=9D error in the parse= r. If we prefer a syntax error, I=E2=80=99m happy
to drop patch 0008.

On Thu, Mar 27, 2025 at 3:27=E2=80=AFPM Vik Fearing = <vik@postgresfriends.org&= gt; wrote:=C2=A0
2) = There is no support for <JSON item method>. Since this appears to be =
constructing a jsonpath query, could that not be added to the syntax and allow jsonpath to throw the error if the function doesn't exist?

You=E2=80=99re right =E2=80=94 there=E2=80=99= s currently no support for <JSON item method>. I
intentionally did= n=E2=80=99t include it in this patch because it would again
expand the s= cope of work.

Supporting <JSON item method> seems non-trivial = to me, because the
current design transforms expression nodes separately= for each
indirection. I previously tried a patch that simply converted = the full
chain of indirections into a JSON_QUERY() call in
transformI= ndirection(). The problem with that approach is that in
EXPLAIN output o= r view definitions, we=E2=80=99d see the rewritten
JSON_QUERY() instead = of the original dot notation =E2=80=94 which feels like a
leaky abstract= ion.

To support item methods, I think we=E2=80=99d need to add a new= kind of
indirection to represent function calls. Then, we could either:= a)
explicitly add each item method token in gram.y, similar to the list=
of method keywords in jsonpath_gram.y; or b) allow a generic
method-= call token in gram.y, and transform it into the corresponding
JsonPathIt= emType that jsonpath understands.

Either way, I=E2=80=99m happy to w= ork on it, but would prefer to discuss and
implement it in a separate fo= llow-up patch.

P.S. After reading your second = question, your first one makes more
sense to me =E2=80=94 I=E2=80=99ve a= lready taken the easy route of not parsing item
methods and was planning= to leave that work for a follow-up patch. So
maybe it=E2=80=99s more co= nsistent to just let the syntax error for ** happen,
rather than explici= tly throwing a =E2=80=9Cfeature not supported=E2=80=9D error?

The on= ly difference, I think, is that we don=E2=80=99t want ** in simplified
a= ccessor syntax, whereas we do want to support item methods in the
future= .

Let me know what you think!

Best,
Alex
--000000000000ff638c06383df132--