Re: SQL:2023 JSON simplified accessor support

>> On Aug 26, 2025, at 11:52, Alexandra Wang <alexandra(dot)wang(dot)oss(at)gmail(dot)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(at)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/