Re: SQL:2023 JSON simplified accessor support - Mailing list pgsql-hackers
| From | Chao Li |
|---|---|
| Subject | Re: SQL:2023 JSON simplified accessor support |
| Date | |
| Msg-id | D4D603CB-2F2F-4AFC-91E9-F7D16B67EDBD@gmail.com Whole thread Raw |
| In response to | Re: SQL:2023 JSON simplified accessor support (Chao Li <li.evan.chao@gmail.com>) |
| List | pgsql-hackers |
On Aug 26, 2025, at 11:52, Alexandra Wang <alexandra.wang.oss@gmail.com> wrote:<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>Best,Alex
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/
HighGo Software Co., Ltd.
https://www.highgo.com/
pgsql-hackers by date: