aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/utils/adt/jsonfuncs.c16
-rw-r--r--src/test/regress/expected/json.out185
-rw-r--r--src/test/regress/expected/json_1.out185
-rw-r--r--src/test/regress/expected/jsonb.out170
-rw-r--r--src/test/regress/expected/jsonb_1.out170
-rw-r--r--src/test/regress/sql/json.sql49
-rw-r--r--src/test/regress/sql/jsonb.sql55
7 files changed, 711 insertions, 119 deletions
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 6c16a953dd3..5fabef0de96 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -726,6 +726,13 @@ get_path_all(FunctionCallInfo fcinfo, const char *funcname, bool as_text)
deconstruct_array(path, TEXTOID, -1, false, 'i',
&pathtext, &pathnulls, &npath);
+ /*
+ * If the array is empty, return NULL; this is dubious but it's what 9.3
+ * did.
+ */
+ if (npath <= 0)
+ PG_RETURN_NULL();
+
tpath = palloc(npath * sizeof(char *));
ipath = palloc(npath * sizeof(int));
@@ -1100,11 +1107,11 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, const char *funcname, bool as_text)
{
Jsonb *jb = PG_GETARG_JSONB(0);
ArrayType *path = PG_GETARG_ARRAYTYPE_P(1);
+ Jsonb *res;
Datum *pathtext;
bool *pathnulls;
int npath;
int i;
- Jsonb *res;
bool have_object = false,
have_array = false;
JsonbValue *jbvp = NULL;
@@ -1120,6 +1127,13 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, const char *funcname, bool as_text)
deconstruct_array(path, TEXTOID, -1, false, 'i',
&pathtext, &pathnulls, &npath);
+ /*
+ * If the array is empty, return NULL; this is dubious but it's what 9.3
+ * did.
+ */
+ if (npath <= 0)
+ PG_RETURN_NULL();
+
if (JB_ROOT_IS_OBJECT(jb))
have_object = true;
else if (JB_ROOT_IS_ARRAY(jb) && !JB_ROOT_IS_SCALAR(jb))
diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out
index 511b6e15139..7c44e76a1f7 100644
--- a/src/test/regress/expected/json.out
+++ b/src/test/regress/expected/json.out
@@ -653,6 +653,45 @@ where json_type = 'array';
t
(1 row)
+-- corner cases
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> null::text;
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> null::int;
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 1;
+ERROR: cannot extract array element from a non-array
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 'z';
+ ?column?
+----------
+
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::json -> 1;
+ ?column?
+-------------
+ {"b": "cc"}
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::json -> 3;
+ ?column?
+----------
+
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z';
+ERROR: cannot extract field from a non-object
+select '"foo"'::json -> 1;
+ERROR: cannot extract element from a scalar
+select '"foo"'::json -> 'z';
+ERROR: cannot extract element from a scalar
-- array length
SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
json_array_length
@@ -831,53 +870,161 @@ select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2','1'];
1
(1 row)
--- same using array literals
-select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f4,f6}';
- ?column?
------------
- "stringy"
+-- corner cases for same
+select '{"a": {"b":{"c": "foo"}}}'::json #> array[]::text[];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a'];
+ ?column?
+--------------------
+ {"b":{"c": "foo"}}
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b'];
+ ?column?
+--------------
+ {"c": "foo"}
(1 row)
-select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f2}';
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b','c'];
?column?
----------
- {"f3":1}
+ "foo"
(1 row)
-select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f2,0}';
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b','c','d'];
?column?
----------
- "f3"
+
(1 row)
-select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f2,1}';
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','z','c'];
?column?
----------
- 1
+
(1 row)
-select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f4,f6}';
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','1','b'];
?column?
----------
- stringy
+ "cc"
(1 row)
-select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2}';
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','z','b'];
?column?
----------
- {"f3":1}
+
(1 row)
-select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,0}';
+select '[{"b": "c"}, {"b": "cc"}]'::json #> array['1','b'];
?column?
----------
- f3
+ "cc"
(1 row)
-select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,1}';
+select '[{"b": "c"}, {"b": "cc"}]'::json #> array['z','b'];
?column?
----------
- 1
+
+(1 row)
+
+select '"foo"'::json #> array['z'];
+ ?column?
+----------
+
+(1 row)
+
+select '42'::json #> array['f2'];
+ ?column?
+----------
+
+(1 row)
+
+select '42'::json #> array['0'];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array[]::text[];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a'];
+ ?column?
+--------------------
+ {"b":{"c": "foo"}}
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b'];
+ ?column?
+--------------
+ {"c": "foo"}
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b','c'];
+ ?column?
+----------
+ foo
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b','c','d'];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','z','c'];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','1','b'];
+ ?column?
+----------
+ cc
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','z','b'];
+ ?column?
+----------
+
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['1','b'];
+ ?column?
+----------
+ cc
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['z','b'];
+ ?column?
+----------
+
+(1 row)
+
+select '"foo"'::json #>> array['z'];
+ ?column?
+----------
+
+(1 row)
+
+select '42'::json #>> array['f2'];
+ ?column?
+----------
+
+(1 row)
+
+select '42'::json #>> array['0'];
+ ?column?
+----------
+
(1 row)
-- array_elements
diff --git a/src/test/regress/expected/json_1.out b/src/test/regress/expected/json_1.out
index bfec32ffe7e..d2827320946 100644
--- a/src/test/regress/expected/json_1.out
+++ b/src/test/regress/expected/json_1.out
@@ -653,6 +653,45 @@ where json_type = 'array';
t
(1 row)
+-- corner cases
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> null::text;
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> null::int;
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 1;
+ERROR: cannot extract array element from a non-array
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 'z';
+ ?column?
+----------
+
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::json -> 1;
+ ?column?
+-------------
+ {"b": "cc"}
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::json -> 3;
+ ?column?
+----------
+
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z';
+ERROR: cannot extract field from a non-object
+select '"foo"'::json -> 1;
+ERROR: cannot extract element from a scalar
+select '"foo"'::json -> 'z';
+ERROR: cannot extract element from a scalar
-- array length
SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
json_array_length
@@ -831,53 +870,161 @@ select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2','1'];
1
(1 row)
--- same using array literals
-select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f4,f6}';
- ?column?
------------
- "stringy"
+-- corner cases for same
+select '{"a": {"b":{"c": "foo"}}}'::json #> array[]::text[];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a'];
+ ?column?
+--------------------
+ {"b":{"c": "foo"}}
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b'];
+ ?column?
+--------------
+ {"c": "foo"}
(1 row)
-select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f2}';
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b','c'];
?column?
----------
- {"f3":1}
+ "foo"
(1 row)
-select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f2,0}';
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b','c','d'];
?column?
----------
- "f3"
+
(1 row)
-select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f2,1}';
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','z','c'];
?column?
----------
- 1
+
(1 row)
-select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f4,f6}';
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','1','b'];
?column?
----------
- stringy
+ "cc"
(1 row)
-select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2}';
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','z','b'];
?column?
----------
- {"f3":1}
+
(1 row)
-select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,0}';
+select '[{"b": "c"}, {"b": "cc"}]'::json #> array['1','b'];
?column?
----------
- f3
+ "cc"
(1 row)
-select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,1}';
+select '[{"b": "c"}, {"b": "cc"}]'::json #> array['z','b'];
?column?
----------
- 1
+
+(1 row)
+
+select '"foo"'::json #> array['z'];
+ ?column?
+----------
+
+(1 row)
+
+select '42'::json #> array['f2'];
+ ?column?
+----------
+
+(1 row)
+
+select '42'::json #> array['0'];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array[]::text[];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a'];
+ ?column?
+--------------------
+ {"b":{"c": "foo"}}
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b'];
+ ?column?
+--------------
+ {"c": "foo"}
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b','c'];
+ ?column?
+----------
+ foo
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b','c','d'];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','z','c'];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','1','b'];
+ ?column?
+----------
+ cc
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','z','b'];
+ ?column?
+----------
+
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['1','b'];
+ ?column?
+----------
+ cc
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['z','b'];
+ ?column?
+----------
+
+(1 row)
+
+select '"foo"'::json #>> array['z'];
+ ?column?
+----------
+
+(1 row)
+
+select '42'::json #>> array['f2'];
+ ?column?
+----------
+
+(1 row)
+
+select '42'::json #>> array['0'];
+ ?column?
+----------
+
(1 row)
-- array_elements
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index c1cc1a9dbec..0be7074cbcb 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -432,6 +432,45 @@ SELECT (test_json->>3) IS NULL AS expect_true FROM test_jsonb WHERE json_type =
t
(1 row)
+-- corner cases
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::text;
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::int;
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 1;
+ERROR: cannot call jsonb_array_element (jsonb -> int) on an object
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 'z';
+ ?column?
+----------
+
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 1;
+ ?column?
+-------------
+ {"b": "cc"}
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 3;
+ ?column?
+----------
+
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z';
+ERROR: cannot call jsonb_object_field (jsonb -> text) on an array
+select '"foo"'::jsonb -> 1;
+ERROR: cannot call jsonb_array_element (jsonb -> int) on a scalar
+select '"foo"'::jsonb -> 'z';
+ERROR: cannot call jsonb_object_field (jsonb -> text) on a scalar
-- equality and inequality
SELECT '{"x":"y"}'::jsonb = '{"x":"y"}'::jsonb;
?column?
@@ -1178,63 +1217,138 @@ SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2','1'];
1
(1 row)
--- same using array literals
-SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>'{f4,f6}';
- ?column?
------------
- "stringy"
+-- corner cases for same
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array[]::text[];
+ ?column?
+----------
+
(1 row)
-SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>'{f2}';
- ?column?
------------
- {"f3": 1}
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a'];
+ ?column?
+---------------------
+ {"b": {"c": "foo"}}
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b'];
+ ?column?
+--------------
+ {"c": "foo"}
(1 row)
-SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>'{f2,0}';
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b','c'];
?column?
----------
- "f3"
+ "foo"
(1 row)
-SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>'{f2,1}';
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b','c','d'];
?column?
----------
- 1
+
(1 row)
-SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>'{f4,f6}';
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','z','c'];
?column?
----------
- stringy
+
(1 row)
-SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>'{f2}';
- ?column?
------------
- {"f3": 1}
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> array['a','1','b'];
+ ?column?
+----------
+ "cc"
(1 row)
-SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>'{f2,0}';
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> array['a','z','b'];
?column?
----------
- f3
+
(1 row)
-SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>'{f2,1}';
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb #> array['1','b'];
?column?
----------
- 1
+ "cc"
(1 row)
--- same on jsonb scalars (expecting errors)
-SELECT '42'::jsonb#>array['f2'];
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb #> array['z','b'];
+ ?column?
+----------
+
+(1 row)
+
+select '"foo"'::jsonb #> array['z'];
+ERROR: cannot extract path from a scalar
+select '42'::jsonb #> array['f2'];
ERROR: cannot extract path from a scalar
-SELECT '42'::jsonb#>array['0'];
+select '42'::jsonb #> array['0'];
+ERROR: cannot extract path from a scalar
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array[]::text[];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a'];
+ ?column?
+---------------------
+ {"b": {"c": "foo"}}
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b'];
+ ?column?
+--------------
+ {"c": "foo"}
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b','c'];
+ ?column?
+----------
+ foo
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b','c','d'];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','z','c'];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> array['a','1','b'];
+ ?column?
+----------
+ cc
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> array['a','z','b'];
+ ?column?
+----------
+
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> array['1','b'];
+ ?column?
+----------
+ cc
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> array['z','b'];
+ ?column?
+----------
+
+(1 row)
+
+select '"foo"'::jsonb #>> array['z'];
ERROR: cannot extract path from a scalar
-SELECT '42'::jsonb#>>array['f2'];
+select '42'::jsonb #>> array['f2'];
ERROR: cannot extract path from a scalar
-SELECT '42'::jsonb#>>array['0'];
+select '42'::jsonb #>> array['0'];
ERROR: cannot extract path from a scalar
-- array_elements
SELECT jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
diff --git a/src/test/regress/expected/jsonb_1.out b/src/test/regress/expected/jsonb_1.out
index 249f5758442..4edef901f41 100644
--- a/src/test/regress/expected/jsonb_1.out
+++ b/src/test/regress/expected/jsonb_1.out
@@ -432,6 +432,45 @@ SELECT (test_json->>3) IS NULL AS expect_true FROM test_jsonb WHERE json_type =
t
(1 row)
+-- corner cases
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::text;
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::int;
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 1;
+ERROR: cannot call jsonb_array_element (jsonb -> int) on an object
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 'z';
+ ?column?
+----------
+
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 1;
+ ?column?
+-------------
+ {"b": "cc"}
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 3;
+ ?column?
+----------
+
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z';
+ERROR: cannot call jsonb_object_field (jsonb -> text) on an array
+select '"foo"'::jsonb -> 1;
+ERROR: cannot call jsonb_array_element (jsonb -> int) on a scalar
+select '"foo"'::jsonb -> 'z';
+ERROR: cannot call jsonb_object_field (jsonb -> text) on a scalar
-- equality and inequality
SELECT '{"x":"y"}'::jsonb = '{"x":"y"}'::jsonb;
?column?
@@ -1178,63 +1217,138 @@ SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2','1'];
1
(1 row)
--- same using array literals
-SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>'{f4,f6}';
- ?column?
------------
- "stringy"
+-- corner cases for same
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array[]::text[];
+ ?column?
+----------
+
(1 row)
-SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>'{f2}';
- ?column?
------------
- {"f3": 1}
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a'];
+ ?column?
+---------------------
+ {"b": {"c": "foo"}}
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b'];
+ ?column?
+--------------
+ {"c": "foo"}
(1 row)
-SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>'{f2,0}';
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b','c'];
?column?
----------
- "f3"
+ "foo"
(1 row)
-SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>'{f2,1}';
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b','c','d'];
?column?
----------
- 1
+
(1 row)
-SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>'{f4,f6}';
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','z','c'];
?column?
----------
- stringy
+
(1 row)
-SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>'{f2}';
- ?column?
------------
- {"f3": 1}
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> array['a','1','b'];
+ ?column?
+----------
+ "cc"
(1 row)
-SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>'{f2,0}';
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> array['a','z','b'];
?column?
----------
- f3
+
(1 row)
-SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>'{f2,1}';
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb #> array['1','b'];
?column?
----------
- 1
+ "cc"
(1 row)
--- same on jsonb scalars (expecting errors)
-SELECT '42'::jsonb#>array['f2'];
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb #> array['z','b'];
+ ?column?
+----------
+
+(1 row)
+
+select '"foo"'::jsonb #> array['z'];
+ERROR: cannot extract path from a scalar
+select '42'::jsonb #> array['f2'];
ERROR: cannot extract path from a scalar
-SELECT '42'::jsonb#>array['0'];
+select '42'::jsonb #> array['0'];
+ERROR: cannot extract path from a scalar
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array[]::text[];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a'];
+ ?column?
+---------------------
+ {"b": {"c": "foo"}}
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b'];
+ ?column?
+--------------
+ {"c": "foo"}
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b','c'];
+ ?column?
+----------
+ foo
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b','c','d'];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','z','c'];
+ ?column?
+----------
+
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> array['a','1','b'];
+ ?column?
+----------
+ cc
+(1 row)
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> array['a','z','b'];
+ ?column?
+----------
+
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> array['1','b'];
+ ?column?
+----------
+ cc
+(1 row)
+
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> array['z','b'];
+ ?column?
+----------
+
+(1 row)
+
+select '"foo"'::jsonb #>> array['z'];
ERROR: cannot extract path from a scalar
-SELECT '42'::jsonb#>>array['f2'];
+select '42'::jsonb #>> array['f2'];
ERROR: cannot extract path from a scalar
-SELECT '42'::jsonb#>>array['0'];
+select '42'::jsonb #>> array['0'];
ERROR: cannot extract path from a scalar
-- array_elements
SELECT jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index ae65ef6921e..964d5baa2a4 100644
--- a/src/test/regress/sql/json.sql
+++ b/src/test/regress/sql/json.sql
@@ -238,6 +238,17 @@ select (test_json->>3) is null as expect_true
from test_json
where json_type = 'array';
+-- corner cases
+
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> null::text;
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> null::int;
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 1;
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json -> 'z';
+select '[{"b": "c"}, {"b": "cc"}]'::json -> 1;
+select '[{"b": "c"}, {"b": "cc"}]'::json -> 3;
+select '[{"b": "c"}, {"b": "cc"}]'::json -> 'z';
+select '"foo"'::json -> 1;
+select '"foo"'::json -> 'z';
-- array length
@@ -281,20 +292,40 @@ select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f4','f6'];
select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2'];
select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2','0'];
select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>array['f2','1'];
+
select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f4','f6'];
select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2'];
select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2','0'];
select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>array['f2','1'];
--- same using array literals
-select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f4,f6}';
-select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f2}';
-select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f2,0}';
-select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>'{f2,1}';
-select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f4,f6}';
-select '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2}';
-select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,0}';
-select '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::json#>>'{f2,1}';
+-- corner cases for same
+select '{"a": {"b":{"c": "foo"}}}'::json #> array[]::text[];
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a'];
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b'];
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b','c'];
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','b','c','d'];
+select '{"a": {"b":{"c": "foo"}}}'::json #> array['a','z','c'];
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','1','b'];
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #> array['a','z','b'];
+select '[{"b": "c"}, {"b": "cc"}]'::json #> array['1','b'];
+select '[{"b": "c"}, {"b": "cc"}]'::json #> array['z','b'];
+select '"foo"'::json #> array['z'];
+select '42'::json #> array['f2'];
+select '42'::json #> array['0'];
+
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array[]::text[];
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a'];
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b'];
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b','c'];
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','b','c','d'];
+select '{"a": {"b":{"c": "foo"}}}'::json #>> array['a','z','c'];
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','1','b'];
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::json #>> array['a','z','b'];
+select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['1','b'];
+select '[{"b": "c"}, {"b": "cc"}]'::json #>> array['z','b'];
+select '"foo"'::json #>> array['z'];
+select '42'::json #>> array['f2'];
+select '42'::json #>> array['0'];
-- array_elements
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 187a8e8ccc9..c1ef6dff56d 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -108,6 +108,17 @@ SELECT (test_json->>'field3') IS NULL AS expect_true FROM test_jsonb WHERE json_
SELECT (test_json->3) IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'array';
SELECT (test_json->>3) IS NULL AS expect_true FROM test_jsonb WHERE json_type = 'array';
+-- corner cases
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::text;
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::int;
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 1;
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 'z';
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 1;
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 3;
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z';
+select '"foo"'::jsonb -> 1;
+select '"foo"'::jsonb -> 'z';
+
-- equality and inequality
SELECT '{"x":"y"}'::jsonb = '{"x":"y"}'::jsonb;
SELECT '{"x":"y"}'::jsonb = '{"x":"z"}'::jsonb;
@@ -252,26 +263,40 @@ SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>array['f4','f6'];
SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>array['f2'];
SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>array['f2','0'];
SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>array['f2','1'];
+
SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f4','f6'];
SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2'];
SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2','0'];
SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2','1'];
--- same using array literals
-SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>'{f4,f6}';
-SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>'{f2}';
-SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>'{f2,0}';
-SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>'{f2,1}';
-SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>'{f4,f6}';
-SELECT '{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>'{f2}';
-SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>'{f2,0}';
-SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>'{f2,1}';
-
--- same on jsonb scalars (expecting errors)
-SELECT '42'::jsonb#>array['f2'];
-SELECT '42'::jsonb#>array['0'];
-SELECT '42'::jsonb#>>array['f2'];
-SELECT '42'::jsonb#>>array['0'];
+-- corner cases for same
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array[]::text[];
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a'];
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b'];
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b','c'];
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','b','c','d'];
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #> array['a','z','c'];
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> array['a','1','b'];
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #> array['a','z','b'];
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb #> array['1','b'];
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb #> array['z','b'];
+select '"foo"'::jsonb #> array['z'];
+select '42'::jsonb #> array['f2'];
+select '42'::jsonb #> array['0'];
+
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array[]::text[];
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a'];
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b'];
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b','c'];
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','b','c','d'];
+select '{"a": {"b":{"c": "foo"}}}'::jsonb #>> array['a','z','c'];
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> array['a','1','b'];
+select '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb #>> array['a','z','b'];
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> array['1','b'];
+select '[{"b": "c"}, {"b": "cc"}]'::jsonb #>> array['z','b'];
+select '"foo"'::jsonb #>> array['z'];
+select '42'::jsonb #>> array['f2'];
+select '42'::jsonb #>> array['0'];
-- array_elements
SELECT jsonb_array_elements('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,8,9]},false]');
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-30 20:24:45 +0000