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 1urpo1-00DCqw-P9 for pgsql-hackers@arkaria.postgresql.org; Fri, 29 Aug 2025 03:29:39 +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 1urpo1-0066Af-29 for pgsql-hackers@arkaria.postgresql.org; Fri, 29 Aug 2025 03:29:37 +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 1urpo0-0066AX-K9 for pgsql-hackers@lists.postgresql.org; Fri, 29 Aug 2025 03:29:37 +0000 Received: from mail-pg1-x52e.google.com ([2607:f8b0:4864:20::52e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1urpny-002IBa-0u for pgsql-hackers@postgresql.org; Fri, 29 Aug 2025 03:29:36 +0000 Received: by mail-pg1-x52e.google.com with SMTP id 41be03b00d2f7-b476c67c5easo1176193a12.0 for ; Thu, 28 Aug 2025 20:29:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1756438174; x=1757042974; 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=0kkECKMqFc61qsD2ccuuU6IHj3asbDUF4Sax+A1SNFI=; b=KfbkCxLKzVBgMCz+ghJ/zBHBjUNyFznu1f0/vBBzdXoQEdtItK/tqKnxBC4wKRQSin AboKNe8rM4JK3JGfb8EidI1Pm8NLOIIacG6th1/GqgnjjeYThhtZI9yprfeHcf6q9d6J ZBBSvSNLWaN0MpHc2D7NIDdLSYtizWxwM+KxVrFgnX30+TYyjlNDKTIVFVrK6up2MEf7 SG8y4LMYIrSfaRs8CpUnHOAub3ZfUQ1r3vkpfZQjsu6mJMR/vchD5GnnWvsipBsoW+TP dIn52SkMAI08/FF6slNZrjLGVpJe4Orrhn4k9MggrMr9EBX72pq6fy+cnQoncSpO6G2s 1O+A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1756438174; x=1757042974; 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=0kkECKMqFc61qsD2ccuuU6IHj3asbDUF4Sax+A1SNFI=; b=L5LbtZ806JUUEHfTQo/bItWbp3UoyG0Ef1PES5M7sEw53EXxV3qzhZps/c0LKe7O4i TQ+xNWnR6KI7bwqjoKoJVxbb6uKmpabGFECswL/l3biJUqThYipBbTeFNp6mzuSy5Tel klFld9uxRfaD6I8njHPDXIVIMLZTsKTUeZhx0gbfR8/iLfvF6oLzruteqyFHW0nSeqR9 LCjpIDTrQCBkaMHS9ubjpwwCFOrs6HMGr5MHjwS97psHrfq5gAvZ8spr2mU6jqAWpF5Z t7UzQw3+P5wCt+vA4YJLG/x6bB7yunKxEOnVw2qgcn8CNUcFG2cEkIBb5BgSoMvGY3Hx lZXg== X-Forwarded-Encrypted: i=1; AJvYcCWL3u1i+sFO6L4+7AeoitqtkCO8sM2NqxlPOYZIwif8UJnAoScsY0POGrekGod7XWKXPDfSidWaAeh9z+Ri@postgresql.org X-Gm-Message-State: AOJu0YzjfWvQ+HDSnaZOADnZtxV57qRgPjSrujWP+30WJTP7dX+EO8+d uugV0KXnxvOL5hzp0LF9LaCu/jFeB2qHl/gjVqix2FlKv+YbmVBuJlc9 X-Gm-Gg: ASbGncut6BzIN/yxcBz3Z/HbV/FAryNJ/TAsybs4krYIkA6Mn0BMlSmW0m0HvKWTdm1 Z5c0HEEa8+hca+c1QqBSamY1aC/68Euie+9eiw8cDEA1inpoj7vaL5XZMRWaOOF3+TuAhNzb0uh EzdH5v/exmWSppjUXLtW3SZmSf9zFsT2VwJBCHL6zuFShDUpwUAT2m4aGCeyDLhUtsYaB5/zgxW jTDGCCc82yF174jVR/ZyE3EmrrT8npsAK3gj1dD2Gg8mKS/ugwa8lPjgkOThWLPOjo9rpxbb+pe OGFVyo48pRDqtwTPvVrklp0RmcnjBxUl1dcQhAD0U9bkzV5VSQNJo2RLj/ZnJhPUVu6FEUR6Hvf wQQGA9uLGUUhQ6+KP1BlLxOQ3J3/HifYP X-Google-Smtp-Source: AGHT+IGXRdQpnTpOW8U576FQ9KiKzhDVaDkvGcWGQL2N6dvNJvchOF+pdTuPFmM1Ob7JDL8nX9tgag== X-Received: by 2002:a17:902:e743:b0:248:eb3e:54d6 with SMTP id d9443c01a7336-248eb3e64e9mr47101775ad.21.1756438173827; Thu, 28 Aug 2025 20:29:33 -0700 (PDT) Received: from smtpclient.apple ([142.171.105.12]) by smtp.gmail.com with ESMTPSA id d9443c01a7336-2490372ddb6sm10182835ad.48.2025.08.28.20.29.29 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 28 Aug 2025 20:29:33 -0700 (PDT) From: Chao Li Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_02878F8B-D8CD-4ECA-BC47-EE9AA4777961" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\)) Subject: Re: SQL:2023 JSON simplified accessor support Date: Fri, 29 Aug 2025 11:29:15 +0800 In-Reply-To: <263CAF0D-7776-4349-93CA-CC1AB15384CA@gmail.com> Cc: jian he , Nikita Malakhov , Vik Fearing , Mark Dilger , Matheus Alcantara , Peter Eisentraut , Andrew Dunstan , Nikita Glukhov , PostgreSQL Hackers , "David E. Wheeler" To: Alexandra Wang References: <21fa36b4-6d56-4556-b524-9e3955b922c8@eisentraut.org> <263CAF0D-7776-4349-93CA-CC1AB15384CA@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=_02878F8B-D8CD-4ECA-BC47-EE9AA4777961 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 >> On Aug 26, 2025, at 11:52, Alexandra Wang = wrote: >>=20 >> Best, >> Alex >> = >=20 >=20 I found a bug. ``` INSERT INTO test_jsonb_types (data) VALUES ('[1, 2, "three"]'), ('{"con": {"a": [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": = 100}}}]}}=E2=80=99); ``` If I use a index following a slice, it doesn=E2=80=99t work: ``` evantest=3D# select data[0] from test_jsonb_types; data ------ 1 (2 rows) evantest=3D# select data[0:2][1] from test_jsonb_types; # This should = return =E2=80=9C2" data ------ (2 rows) evantest=3D# select (t.data)['con']['a'][0:1] from test_jsonb_types t; # = returned the slice properly data ----------------------------------------------------- [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}] (2 rows) evantest=3D# select (t.data)['con']['a'][0:1][0] from test_jsonb_types = t; # also returned the slice, which is wrong data ----------------------------------------------------- [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": 100}}}] (2 rows) ``` We should consider a slice as a container, so the fix is simple. My = quick unpolished fix is: ``` chaol@ChaodeMacBook-Air postgresql % git diff diff --git a/src/backend/utils/adt/jsonbsubs.c = b/src/backend/utils/adt/jsonbsubs.c index cb72d12ca3f..8845dcf239a 100644 --- a/src/backend/utils/adt/jsonbsubs.c +++ b/src/backend/utils/adt/jsonbsubs.c @@ -247,6 +247,7 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, = List **indirection, Subscripti ListCell *lc; Datum jsp; int pathlen =3D 0; + bool isSlice =3D false; sbsref->refupperindexpr =3D NIL; sbsref->reflowerindexpr =3D NIL; @@ -285,6 +286,7 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, = List **indirection, Subscripti if (ai->is_slice) { + isSlice =3D true; while = (list_length(sbsref->reflowerindexpr) < = list_length(sbsref->refupperindexpr)) sbsref->reflowerindexpr =3D = lappend(sbsref->reflowerindexpr, NULL); @@ -369,6 +371,9 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, = List **indirection, Subscripti path->next =3D jpi; path =3D jpi; pathlen++; + + if (isSlice) + break; } if (pathlen =3D=3D 0) ``` After the fix, let=E2=80=99s test again: ``` evantest=3D# select data[0:2][1] from test_jsonb_types; # good result data ------ 2 (2 rows) evantest=3D# select (t.data)['con']['a'][0:1][0] from test_jsonb_types = t; # good result data ------------------------- {"b": {"c": {"d": 99}}} (2 rows) ``` Regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_02878F8B-D8CD-4ECA-BC47-EE9AA4777961 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
On = Aug 26, 2025, at 11:52, Alexandra Wang = <alexandra.wang.oss@gmail.com> wrote:

Best,
Alex
<v14-0002-Allow-Generic-Type-Subscripting-to-Acc= ept-Dot-No.patch><v14-0003-Export-jsonPathFromParseResult.patch&g= t;<v14-0001-Allow-transformation-of-only-a-sublist= -of-subscr.patch><v14-0005-Implement-read-only-dot-notation-for-j= sonb.patch><v14-0007-Implement-jsonb-wildcard-member-access= or.patch><v14-0006-Implement-Jsonb-subscripting-with-slic= ing.patch><v14-0004-Extract-coerce_jsonpath_subscript.patc= h>


I found a = bug.

```
INSERT INTO = test_jsonb_types (data) VALUES
('[1, 2, = "three"]'),
('{"con": {"a": [{"b": {"c": {"d": 99}}}, {"b": = {"c": {"d": = 100}}}]}}=E2=80=99);
```

If I = use a index following a slice, it doesn=E2=80=99t = work:

```
evantest=3D# select = data[0] from = test_jsonb_types;
 data
------

 1

(2 = rows)

evantest=3D# select data[0:2][1] from = test_jsonb_types; # This should return = =E2=80=9C2"
 data
------


(2 rows)

evantest=3D# = select (t.data)['con']['a'][0:1] from test_jsonb_types t; # returned the = slice properly
            =             = data
-----------------------------------------------------
=

 [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": = 100}}}]
(2 rows)

evantest=3D# select = (t.data)['con']['a'][0:1][0] from test_jsonb_types t; # also returned = the slice, which is wrong
          =               = data
-----------------------------------------------------
=

 [{"b": {"c": {"d": 99}}}, {"b": {"c": {"d": = 100}}}]
(2 = rows)
```

We should consider a = slice as a container, so the fix is simple. My quick unpolished fix = is:

```
chaol@ChaodeMacBook-Air = postgresql % git diff
diff --git = a/src/backend/utils/adt/jsonbsubs.c = b/src/backend/utils/adt/jsonbsubs.c
index = cb72d12ca3f..8845dcf239a 100644
--- = a/src/backend/utils/adt/jsonbsubs.c
+++ = b/src/backend/utils/adt/jsonbsubs.c
@@ -247,6 +247,7 @@ = jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection, = Subscripti
        ListCell   = *lc;
        Datum       =     jsp;
        int   =                   pathlen =3D= 0;
+       bool         =    isSlice =3D false;

    =     sbsref->refupperindexpr =3D NIL;
  =       sbsref->reflowerindexpr =3D NIL;
@@ = -285,6 +286,7 @@ jsonb_subscript_make_jsonpath(ParseState *pstate, List = **indirection, Subscripti

      =                   if = (ai->is_slice)
            =             {
+     =                     =       isSlice =3D true;
      =                     =       while (list_length(sbsref->reflowerindexpr) < = list_length(sbsref->refupperindexpr))
      =                     =               = sbsref->reflowerindexpr =3D lappend(sbsref->reflowerindexpr, = NULL);

@@ -369,6 +371,9 @@ = jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection, = Subscripti
              =   path->next =3D jpi;
        =         path =3D jpi;
      =           pathlen++;
+
+ =               if = (isSlice)
+               =         break;
        = }

        if (pathlen =3D=3D = 0)
```

After the fix, let=E2=80=99= s test again:

```
evantest=3D# = select data[0:2][1] from test_jsonb_types; # good = result
 data
------
 2
<= br>
(2 rows)

evantest=3D# = select (t.data)['con']['a'][0:1][0] from test_jsonb_types t; # good = result
          = data
-------------------------

 {"= b": {"c": {"d": 99}}}
(2 = rows)
```

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




= --Apple-Mail=_02878F8B-D8CD-4ECA-BC47-EE9AA4777961--