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 1v0XHx-00FHt7-PU for pgsql-hackers@arkaria.postgresql.org; Mon, 22 Sep 2025 03:32:29 +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 1v0XHv-00GgmA-IJ for pgsql-hackers@arkaria.postgresql.org; Mon, 22 Sep 2025 03:32:27 +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 1v0XHv-00Ggm2-3C for pgsql-hackers@lists.postgresql.org; Mon, 22 Sep 2025 03:32:27 +0000 Received: from mail-pf1-x436.google.com ([2607:f8b0:4864:20::436]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v0XHs-001h8V-1R for pgsql-hackers@postgresql.org; Mon, 22 Sep 2025 03:32:25 +0000 Received: by mail-pf1-x436.google.com with SMTP id d2e1a72fcca58-77f32d99a97so474758b3a.1 for ; Sun, 21 Sep 2025 20:32:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758511944; x=1759116744; darn=postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=V5Tzwigg+UCHOlDB++kWI/eUkSS8Pbm2MT9Wa5P3VJg=; b=NKwnLB6Ajeua5JwT+ySPF6TZSVc2lTmDti1HmeMbTre9ZI7oq3KDqFuwMlLZGYpa+v O+kCQP5lI10u7Lk0NktyffqJW01mQ7xcCb9BGRz5xA0SnMwjgpIZ21PQSORBuJkijy5T 6vjnYmUPhFJ7plNNhsuBt4/RugYy6DQzjvwpVDe/h3PNo2MN+GjtH2dQNfAN+pfdLdmG tEkVd6sNs27XPLjAkX7gcDQ5ZK0BlZJBZOBRcOg9lJTcmtHYrFx0SGZE3LF7ayUzMLYh 2vJ3GAKAonyn7Rpr6Z+lJJB8+z8CQy8BhEc/H/Y/oXsQd/2x/KexyKt7BrPFx2Y13yPH aewg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758511944; x=1759116744; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=V5Tzwigg+UCHOlDB++kWI/eUkSS8Pbm2MT9Wa5P3VJg=; b=OeoqbV5dnv/92Zf059nDpBTaoC+s/K5K3ieKUvc7FlTFNzOaTLCH+G3TBy3iiPx5K4 V04vk/p6L6s79afBhfnMy3dQ1ZWZ/qVjK3WkBEmLRXUZZ0TaSuYo4AQn+ZzRPAP20Yhq d27cilnGnOFONzrBc2RTAqi9OkLRarodTsXh7Pu929HjWyMz+VXxVOay0MfF5xatjtUA qCn1P1HvKzlNdWm8T80RuDnO/UpS6sjvNDO8tATXE9u2njEu9GMevKDf7w3a9N9cCNdY /Ds7B12PE7nTGuqlW5QHGJ85BycKS4cEdvBt8rUvEuhsD1skxtt4+8tmwSB9CbFqoTXJ 6tNw== X-Gm-Message-State: AOJu0YwCkGK7GEqyFWX0hksZ6HJIn/pjjp/IeIH+qexdP9DlPC0WCGcL n/lxqsJQmuBPziHT7Y0HH3zbqra1hVcoxXPAb2bjTDURJYyzt28TcRYf X-Gm-Gg: ASbGncui4CX5PqaTR75+3fYidhs71cSXla2S+KTG5mJOAB+BfdaQ+kcVb/ySahE103z WyhJxLP5yfqXyDtsLhJiSVmFz09YW9/zZbmjorK061l39/PSN/I5ElkuT6DN/GdMqV604+bg/bV I6jvec47V3roGGYrVlw1Hm3OdeUU0qkyz7EezRLkPhgG3CiHdB93fCIN4yRliy87pL45LsVd6tG ywkdLPgBSoZddszg0znuypttkRyjEORTiAOVgPXOBtkEeAgT+7QBrgkOsgT8OHZ3J0sYnJMddFi NR4xamVoUEEJYOYi0nSVN+r+/R1hff3tIkL6TyriMXXSRlqEL3eZahGZD6jCn7VJn9V34BSaBuN jEXcz4Sa4NsMiHO4WWYccFSaK4kJoU6kXkKT/d4EYsM4= X-Google-Smtp-Source: AGHT+IGnGvNogesv517Fu/VWUmKId8jpR/HjQwkkhcRiGmgcjDVDdCoqCrz1oMZiUxI8BuW1cvrqrg== X-Received: by 2002:a05:6a20:5491:b0:251:9f29:453e with SMTP id adf61e73a8af0-2926e840fc8mr16431125637.39.1758511943906; Sun, 21 Sep 2025 20:32:23 -0700 (PDT) Received: from smtpclient.apple ([142.171.105.12]) by smtp.gmail.com with ESMTPSA id 41be03b00d2f7-b553105339dsm5252035a12.14.2025.09.21.20.32.19 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 21 Sep 2025 20:32:23 -0700 (PDT) From: Chao Li Message-Id: <826F61C4-AE01-4070-8F78-CEE8781EACEB@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_04665FC4-D560-482A-A241-933DDAB2FE97" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\)) Subject: Re: SQL:2023 JSON simplified accessor support Date: Mon, 22 Sep 2025 11:32:05 +0800 In-Reply-To: Cc: PostgreSQL Hackers , Nikita Glukhov , jian he , Nikita Malakhov , Vik Fearing , Mark Dilger , Matheus Alcantara , Peter Eisentraut , Andrew Dunstan , "David E. Wheeler" To: Alexandra Wang References: <21fa36b4-6d56-4556-b524-9e3955b922c8@eisentraut.org> <290418A2-3A9B-4304-AAAD-4A40CFF1CB93@gmail.com> <98C6F445-773B-4C82-8198-7F5CD56D5252@gmail.com> <052E941B-C905-448F-AE3A-753C12AAC938@gmail.com> <8CB9A1EC-2D14-44FF-A600-6669353A14C2@gmail.com> X-Mailer: Apple Mail (2.3826.700.81) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_04665FC4-D560-482A-A241-933DDAB2FE97 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Looks like patch files need rebase. > On Sep 20, 2025, at 04:40, Alexandra Wang = wrote: >=20 > Hi there, >=20 > I've attached v20. It has the following changes: >=20 > 1. New 0001: It adds test coverage for single-argument functions and > casting for jsonb expressions. This ensures that the relevant behavior > changes become visible in 0005 when field access via dot-notation is > introduced. >=20 > Specifically, once member access through dot-notation is enabled for > jsonb, we can no longer write single-argument functions (including > casts) in dot form for jsonb. For example: >=20 > Before 0005:=20 >=20 > select ('{"a":1}'::jsonb).jsonb_typeof; > jsonb_typeof > -------------- > object > (1 row) >=20 > select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb).name; > name > -------------------------------------- > [{"name": "alice"}, {"name": "bob"}] > (1 row) >=20 > After 0005: >=20 > select ('{"a":1}'::jsonb).jsonb_typeof; > jsonb_typeof > -------------- >=20 > (1 row) >=20 > select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb).name; > name > ------------------ > ["alice", "bob"] > (1 row) >=20 > In the meanwhile, these functions still return correct results through > standard syntax: >=20 > test=3D# select jsonb_typeof(('{"a":1}'::jsonb)); > jsonb_typeof > -------------- > object > (1 row) > test=3D# select ('[{"name": "alice"}, {"name": = "bob"}]'::jsonb)::name; > name > -------------------------------------- > [{"name": "alice"}, {"name": "bob"}] > (1 row) >=20 > I don't consider this behavior change a major issue, because the > dot-form for single-argument functions is not standard SQL and seems > to be PostgreSQL-specific. Still, it's worth highlighting here so > users aren't surprised. >=20 > 2. Refactored 0002: It combines and refactors v19-0001 and v19-0002. > Instead of changing the existing transform() callback in > SubscriptRoutines, it now introduces an additional callback, > transform_partial(). This alternative transform method, used by jsonb, > is more flexible: it accepts a wider range of indirection node types > and can transform only a prefix of the indirection list. This avoids > breaking compatibility for arrays, hstore, and external data types > that supports subscripting. >=20 > 3. 0003 and 0004 stay unchanged. They are both small and can be = squashed > into 0005. I leave them as-is for now for easier review. >=20 > 4. Added two additional tests in 0005 for assignments using jsonb > dot-notation, showing explicitly that assignment is not yet supported. >=20 > 5. Removed 0006 (array slicing) and 0007 (wildcard) from the previous > versions, as they need additional work. My immediate goal is to first > reach consensus on the dot-notation implementation. >=20 > Best, > Alex >=20 > = -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_04665FC4-D560-482A-A241-933DDAB2FE97 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii Looks like = patch files need rebase.

On Sep 20, 2025, at 04:40, Alexandra Wang = <alexandra.wang.oss@gmail.com> wrote:

Hi = there,

I've attached v20. It has the following = changes:

1. New 0001: It adds test coverage for = single-argument functions and
casting for jsonb expressions. This = ensures that the relevant behavior
changes become visible in 0005 = when field access via dot-notation = is
introduced.

Specifically, once member = access through dot-notation is enabled for
jsonb, we can no longer = write single-argument functions (including
casts) in dot form for = jsonb. For example:

Before = 0005: 

select = ('{"a":1}'::jsonb).jsonb_typeof;
  = jsonb_typeof
 --------------
 object
(1 = row)

select ('[{"name": "alice"}, {"name": = "bob"}]'::jsonb).name;
            =     =  name
--------------------------------------
[{"name": = "alice"}, {"name": "bob"}]
(1 = row)

After = 0005:

select = ('{"a":1}'::jsonb).jsonb_typeof;
  = jsonb_typeof
 --------------

(1 = row)

select ('[{"name": "alice"}, {"name": = "bob"}]'::jsonb).name;
      =  name
------------------
 ["alice", "bob"]
 (1 = row)

In the meanwhile, these functions still return = correct results through
standard syntax:

test=3D# =  select = jsonb_typeof(('{"a":1}'::jsonb));
 jsonb_typeof
--------= ------
 object
(1 row)
test=3D#  select = ('[{"name": "alice"}, {"name": "bob"}]'::jsonb)::name;
    =             =  name
--------------------------------------
 [{"name": = "alice"}, {"name": "bob"}]
(1 row)

I don't = consider this behavior change a major issue, because the
dot-form for = single-argument functions is not standard SQL and seems
to be = PostgreSQL-specific. Still, it's worth highlighting here so
users = aren't surprised.

2. Refactored 0002: It combines and refactors = v19-0001 and v19-0002.
Instead of changing the existing transform() = callback in
SubscriptRoutines, it now introduces an additional = callback,
transform_partial(). This alternative transform method, = used by jsonb,
is more flexible: it accepts a wider range of = indirection node types
and can transform only a prefix of the = indirection list. This avoids
breaking compatibility for arrays, = hstore, and external data types
that supports subscripting.

3. = 0003 and 0004 stay unchanged. They are both small and can be = squashed
into 0005. I leave them as-is for now for easier = review.

4. Added two additional tests in 0005 for assignments = using jsonb
dot-notation, showing explicitly that assignment is not = yet supported.

5. Removed 0006 (array slicing) and 0007 = (wildcard) from the previous
versions, as they need additional work. = My immediate goal is to first
reach consensus on the dot-notation = implementation.

Best,
Alex

<v20-0005-Implement-read-only-dot-notation-for-j= sonb.patch><v20-0004-Extract-coerce_jsonpath_subscript.patc= h><v20-0001-Add-test-coverage-for-indirection-tran= sformation.patch><v20-0002-Add-an-alternative-transform-function-= in-Subscri.patch><v20-0003-Export-jsonPathFromParseResult.patch&g= t;

--
Chao Li (Evan)
HighGo Software = Co., Ltd.
https://www.highgo.com/




= --Apple-Mail=_04665FC4-D560-482A-A241-933DDAB2FE97--