Horton

Re: SQL:2023 JSON simplified accessor support

Chao Li <li.evan.chao@gmail.com>

View thread
From: Chao Li <li.evan.chao@gmail.com>
To: Alexandra Wang <alexandra.wang.oss@gmail.com>
Cc: jian he <jian.universality@gmail.com>, Nikita Malakhov <hukutoc@gmail.com>, 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>
Date: 2025-08-29T03:29:15Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add test coverage for indirection transformation

  2. Fix typo in comment

  3. Implementation of subscripting for jsonb

>> On Aug 26, 2025, at 11:52, Alexandra Wang <alexandra.wang.oss@gmail.com> wrote:
>> 
>> Best,
>> Alex
>> <v14-0002-Allow-Generic-Type-Subscripting-to-Accept-Dot-No.patch><v14-0003-Export-jsonPathFromParseResult.patch><v14-0001-Allow-transformation-of-only-a-sublist-of-subscr.patch><v14-0005-Implement-read-only-dot-notation-for-jsonb.patch><v14-0007-Implement-jsonb-wildcard-member-accessor.patch><v14-0006-Implement-Jsonb-subscripting-with-slicing.patch><v14-0004-Extract-coerce_jsonpath_subscript.patch>
> 
> 


I found a bug.

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

If I use a index following a slice, it doesn’t work:

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

 1

(2 rows)

evantest=# select data[0:2][1] from test_jsonb_types; # This should return “2"
 data
------


(2 rows)

evantest=# 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=# 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 = 0;
+       bool            isSlice = false;

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

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

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

        if (pathlen == 0)
```

After the fix, let’s test again:

```
evantest=# select data[0:2][1] from test_jsonb_types; # good result
 data
------
 2

(2 rows)

evantest=# 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/