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 1tpStu-005zAc-HR for pgsql-hackers@arkaria.postgresql.org; Tue, 04 Mar 2025 14:05:38 +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 1tpSts-00Fxmi-VM for pgsql-hackers@arkaria.postgresql.org; Tue, 04 Mar 2025 14:05:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tpSts-00FxeS-JD for pgsql-hackers@lists.postgresql.org; Tue, 04 Mar 2025 14:05:36 +0000 Received: from mail-oa1-x32.google.com ([2001:4860:4864:20::32]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tpStn-000uPN-0e for pgsql-hackers@postgresql.org; Tue, 04 Mar 2025 14:05:34 +0000 Received: by mail-oa1-x32.google.com with SMTP id 586e51a60fabf-2b2b6019a74so2647231fac.0 for ; Tue, 04 Mar 2025 06:05:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1741097130; x=1741701930; 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=P8D+pKRfeJ0fmIo1T1pNn/PbZHVbllGBvQhiHdrNSR0=; b=FYRzMqtKA57OAj32sZ4YHMI/kqqSAmE6UIKsqmAPw+D9vy1XYzE/FptB1OC0awBQ1Z 7VO2nZKJFIMBZS+RsoUNiaNoqQLkb31giXco7cCKhchrRpgvfB6/BZc7qbAJ1P5LT1/F DfQ8hjRD1O6VUswD0qFkKK5/b4UlPXM0GDE22rP9cOATk48lgXVUcQJmfsYSHl5LPkax oPv7tIQdmBPn7Hv4EJQ2+V8JVlLQI/CyFIDeOgyiVWv3hqBT+mprtwvPxNQ2YoL0y5GC rQ8qkkslBs7xkqvrGeRMWN4ofyJDDIHJJBo3rviGMo7sJgFMWIRBvrnze6Xx6/WtXF8G 16vg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741097130; x=1741701930; 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=P8D+pKRfeJ0fmIo1T1pNn/PbZHVbllGBvQhiHdrNSR0=; b=jts2kQ+LqmGjzqElDYLIPg/0pS3/VGVLuAZltOnvZqKAHfnCAw3XzKr7W/GnsJWUQc ics27rP5ZiSmUZxsnW0udAzBsPvl8/XQfWmJ0e+Vx5xn7IqLvc8CtHn3M9nvzO8tIC5e 9ujw2IShNuvquy4VJzU5ua9kPNVh7hgp8n1tLXYbsQW0wF7Nm9bXIAabE8cqMDYl7eig sf2ZXBSS8Wo0RamkzO1RHiV8ohHTlKABbBlsVHsL3z16JEG9aQQxNdWXRzT+NfM3h2ky jo0oWXOMUj60wFqVQBELhRt++gizLJdN9D07zrUhs0Z9A4hqvja3L9xv4Px7ANzLo9s7 G5zA== X-Forwarded-Encrypted: i=1; AJvYcCUlTsEQDZdF/npF5zVL8787P7riXR/aPVIZZTZmqD3pYWFJvscQluHfQDZ7AenRW1Os6+bEPwAR07npSzYc@postgresql.org X-Gm-Message-State: AOJu0Yw5mm1BErwmc25CPV3rnfg+/ml+K1mmT/65A7mzkVP1nfYl5Edh g+7P46ChVQsf/CpV3gJIYw3ggWP9JNzOC09yF7DDS+8UMa+g72ViIjxHPmHxZrTnXQb3IeWSAq5 1j6bn0+GWjCeGfrkw+7uaxo+hlG80czwhu6rQ X-Gm-Gg: ASbGncuYVvhibiL1BtQUTO5L06SapFqNOeniPMibI/54vEzdiPG9Yx2hDAVHHR/kJ2O mw+qVyZuZCYWQo40ydoGVG6nCRgQtMf/DBWBcIrodqMlOsAXtWQwnS2ajLtt4bPZnHb7RT+ej1d X5m/8OlaKp19fk6Qc5CkJEpdw/SA== X-Google-Smtp-Source: AGHT+IEeMPklrdd7LtCzhTW89g5uoJmEzwdjz+D73cccsadiCCYLRQbjxStb/tzJj/c0k6ruo0ihuLegxTFxduLM0wQ= X-Received: by 2002:a05:6870:56a1:b0:29e:5d4e:a4c9 with SMTP id 586e51a60fabf-2c1786cb58bmr10418151fac.32.1741097130048; Tue, 04 Mar 2025 06:05:30 -0800 (PST) MIME-Version: 1.0 References: <21fa36b4-6d56-4556-b524-9e3955b922c8@eisentraut.org> In-Reply-To: From: Mark Dilger Date: Tue, 4 Mar 2025 06:05:18 -0800 X-Gm-Features: AQ5f1JqcH7ERvtbcYxt4Gsf5YyazoaBPJSkGWinccseaKP3XU46c7Rv22WNQLjs Message-ID: Subject: Re: SQL:2023 JSON simplified accessor support To: Alexandra Wang Cc: Matheus Alcantara , Peter Eisentraut , Andrew Dunstan , Nikita Glukhov , PostgreSQL Hackers , "David E. Wheeler" , jian he Content-Type: multipart/alternative; boundary="000000000000f4e971062f84c38a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f4e971062f84c38a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Mar 3, 2025 at 12:23=E2=80=AFPM Alexandra Wang wrote: > I've attached v10, which addresses your feedback. > > Hi Alex! Thanks for the patches. In src/test/regress/sql/jsonb.sql, the section marked with "-- slices are not supported" should be relabeled. That comment predates these patches, and is now misleading. A bit further down in expected/jsonb.out, there is an expected failure, but no SQL comment to indicate that it is expected: +SELECT (t.jb).* FROM test_jsonb_dot_notation; +ERROR: missing FROM-clause entry for table "t" +LINE 1: SELECT (t.jb).* FROM test_jsonb_dot_notation; Perhaps a "-- fails" comment would clarify? Then, further down, +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? I got curious about the support for this new dot notation in the plpgsql parser and tried: +DO $$ +DECLARE + a jsonb :=3D '[1,2,3,4,5,6,7]'::jsonb; +BEGIN + WHILE a IS NOT NULL + LOOP + RAISE NOTICE '%', a; + a :=3D a[2:]; + END LOOP; +END +$$ LANGUAGE plpgsql; +NOTICE: [1, 2, 3, 4, 5, 6, 7] +NOTICE: [3, 4, 5, 6, 7] +NOTICE: [5, 6, 7] +NOTICE: 7 which looks good! But then I tried: +DO $$ +DECLARE + a jsonb :=3D '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}'::jsonb; +BEGIN + WHILE a IS NOT NULL + LOOP + RAISE NOTICE '%', a; + a :=3D COALESCE(a."NU", a[2]); + END LOOP; +END +$$ LANGUAGE plpgsql; +NOTICE: {"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]} +ERROR: missing FROM-clause entry for table "a" +LINE 1: a :=3D COALESCE(a."NU", a[2]) + ^ +QUERY: a :=3D COALESCE(a."NU", a[2]) +CONTEXT: PL/pgSQL function inline_code_block line 8 at assignment which suggests the plpgsql parser does not recognize a."NU" as we'd expect. Any thoughts on this? I notice there are no changes in src/interfaces/ecpg/test, which concerns me. The sqljson.pgc and sqljson_jsontable.pgc files are already testing json handling in ecpg; perhaps just extend those a bit? =E2=80=94 Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --000000000000f4e971062f84c38a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Mon, Mar 3, 2025 at 12:23=E2=80=AFPM Al= exandra Wang <alexandra.= wang.oss@gmail.com> wrote:
=C2=A0I've attached v10, which=C2=A0addresses your feedback.
=


Hi Al= ex!=C2=A0 Thanks for the patches.

In src/test/regress/sq= l/jsonb.sql, the section marked with "-- slices are not supported"= ; should be relabeled.=C2=A0 That comment predates these patches, and is no= w misleading.

A bit further down in expected/jsonb= .out, there is an expected failure, but no SQL comment to indicate that it = is expected:

+SELECT (t.jb).* FROM test_jsonb_dot_= notation;
+ERROR: =C2=A0missing FROM-clause entry for table "t"= ;
+LINE 1: SELECT (t.jb).* FROM test_jsonb_dot_notation;
<= br>
Perhaps a "-- fails" comment would clarify?=C2=A0 T= hen, further down,

+SELECT (jb).a.**.x FROM test_j= sonb_dot_notation; -- not supported
+ERROR: =C2=A0syntax error at or nea= r "**"
+LINE 1: SELECT (jb).a.**.x FROM test_jsonb_dot_notatio= n;

I wonder if it would be better to have the = parser handle this case and raise a=C2=A0ERRCODE_FEATURE_NOT_SUPPORTED inst= ead?

I got curious about the support for this new = dot notation in the plpgsql parser and tried:

+DO = $$
+DECLARE
+ =C2=A0a jsonb :=3D '[1,2,3,4,5,6,7]'::jsonb;+BEGIN
+ =C2=A0WHILE a IS NOT NULL
+ =C2=A0LOOP
+ =C2=A0 =C2=A0RA= ISE NOTICE '%', a;
+ =C2=A0 =C2=A0a :=3D a[2:];
+ =C2=A0END L= OOP;
+END
+$$ LANGUAGE plpgsql;
+NOTICE: =C2=A0[1, 2, 3, 4, 5, 6, = 7]
+NOTICE: =C2=A0[3, 4, 5, 6, 7]
+NOTICE: =C2=A0[5, 6, 7]
+NOTICE= : =C2=A07

which looks good!=C2=A0 But then I t= ried:

+DO $$
+DECLARE
+ =C2=A0a jsonb :=3D &= #39;{"": 6, "NU": [{"": [[3]]}, [6], [2], &qu= ot;bCi"], "aaf": [-6, -8]}'::jsonb;
+BEGIN
+ =C2= =A0WHILE a IS NOT NULL
+ =C2=A0LOOP
+ =C2=A0 =C2=A0RAISE NOTICE '= %', a;
+ =C2=A0 =C2=A0a :=3D COALESCE(a."NU", a[2]);
+ = =C2=A0END LOOP;
+END
+$$ LANGUAGE plpgsql;
+NOTICE: =C2=A0{"&= quot;: 6, "NU": [{"": [[3]]}, [6], [2], "bCi"= ], "aaf": [-6, -8]}
+ERROR: =C2=A0missing FROM-clause entry fo= r table "a"
+LINE 1: a :=3D COALESCE(a."NU", a[2])+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0^
+QUERY: =C2=A0a :=3D COALESCE(a."NU", a[2])
+CONTEX= T: =C2=A0PL/pgSQL function inline_code_block line 8 at assignment
=

which suggests the plpgsql parser does not recognize a.= "NU" as we'd expect.=C2=A0 Any thoughts on this?
I notice there are no changes in=C2=A0src/interfaces/ecpg/test= , which concerns me.=C2=A0 The sqljson.pgc and sqljson_jsontable.pgc files = are already testing json handling in ecpg; perhaps just extend those a bit?=

=E2=80=94
Mark Dilger
EnterpriseDB:=C2=A0http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--000000000000f4e971062f84c38a--