Add path extraction(#>, #>>)operators to agtype (#1224)
Original work was done by Josh Innis for issue#282.
The changes included in this PR are as follows:
- Implement the path extraction operators for agtype, similar
to the one in postgres where this operator works with jsonb operands
- Allow using these operators inside cypher queries
- Added regression tests
diff --git a/age--1.4.0.sql b/age--1.4.0.sql
index d9fbcc5..c9e7345 100644
--- a/age--1.4.0.sql
+++ b/age--1.4.0.sql
@@ -2924,6 +2924,36 @@
FUNCTION = ag_catalog.agtype_array_element_text
);
+CREATE FUNCTION ag_catalog.agtype_extract_path(agtype, agtype)
+RETURNS agtype
+LANGUAGE c
+IMMUTABLE
+RETURNS NULL ON NULL INPUT
+PARALLEL SAFE
+AS 'MODULE_PATHNAME';
+
+-- return the extracted path as agtype
+CREATE OPERATOR #> (
+ LEFTARG = agtype,
+ RIGHTARG = agtype,
+ FUNCTION = ag_catalog.agtype_extract_path
+);
+
+CREATE FUNCTION ag_catalog.agtype_extract_path_text(agtype, agtype)
+RETURNS text
+LANGUAGE c
+IMMUTABLE
+RETURNS NULL ON NULL INPUT
+PARALLEL SAFE
+AS 'MODULE_PATHNAME';
+
+-- return the extracted path as text
+CREATE OPERATOR #>> (
+ LEFTARG = agtype,
+ RIGHTARG = agtype,
+ FUNCTION = ag_catalog.agtype_extract_path_text
+);
+
--
-- Contains operators @> <@
--
diff --git a/regress/expected/jsonb_operators.out b/regress/expected/jsonb_operators.out
index 78b2f89..a665bd6 100644
--- a/regress/expected/jsonb_operators.out
+++ b/regress/expected/jsonb_operators.out
@@ -805,6 +805,504 @@
DETAIL: Expected ":", but found "}".
CONTEXT: agtype data, line 1: {"null"}
--
+-- Agtype path extraction operators (#>, #>>)
+--
+/*
+ * #> operator to return the extracted value as agtype
+ */
+SELECT pg_typeof('{"a":"b","c":[1,2,3]}'::agtype #> '["a"]');
+ pg_typeof
+-----------
+ agtype
+(1 row)
+
+SELECT pg_typeof('[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[-1,"5"]');
+ pg_typeof
+-----------
+ agtype
+(1 row)
+
+-- left operand is agtype object, right operand should be an array of strings
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["a"]';
+ ?column?
+----------
+ "b"
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["c"]';
+ ?column?
+-----------
+ [1, 2, 3]
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '[]';
+ ?column?
+----------------------------
+ {"a": "b", "c": [1, 2, 3]}
+(1 row)
+
+SELECT '{"0": true}'::agtype #> '["0"]';
+ ?column?
+----------
+ true
+(1 row)
+
+SELECT '{"a":"b","c":{"d": [1,2,3]}}'::agtype #> '["c", "d"]';
+ ?column?
+-----------
+ [1, 2, 3]
+(1 row)
+
+SELECT '{"a":"b","c":{"d": {"e": -1}}}'::agtype #> '["c", "d", "e"]';
+ ?column?
+----------
+ -1
+(1 row)
+
+-- left operand is vertex/edge/path, right operand should be an array of strings
+SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype #> '[]';
+ ?column?
+---------------------------------------------------------------------------------------------------------------------------
+ {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}
+(1 row)
+
+SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype #> '["e", "h", -2]';
+ ?column?
+----------
+ []
+(1 row)
+
+SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"a": "xyz", "b" : true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::edge'::agtype #> '[]';
+ ?column?
+---------------------------------------------------------------------------------------------------------------------------
+ {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}
+(1 row)
+
+SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"a": "xyz", "b" : true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::edge'::agtype #> '["i", "k", "l"]';
+ ?column?
+----------
+ "mnopq"
+(1 row)
+
+-- left operand is agtype array, right operand should be an array of integers or valid integer strings
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[0]';
+ ?column?
+----------
+ 0
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[4]';
+ ?column?
+---------------
+ {"5": "five"}
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[]';
+ ?column?
+----------------------------------
+ [0, 1, 2, [3, 4], {"5": "five"}]
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[-2]';
+ ?column?
+----------
+ [3, 4]
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[-2, -1]';
+ ?column?
+----------
+ 4
+(1 row)
+
+SELECT '[[-3, 1]]'::agtype #> '[0, 1]';
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '["0"]';
+ ?column?
+----------
+ 0
+(1 row)
+
+SELECT '[[-3, 1]]'::agtype #> '["0", "1"]';
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT '[[-3, 1]]'::agtype #> '["0", 1]';
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT '[[-3, 1]]'::agtype #> '["0", "-1"]';
+ ?column?
+----------
+ 1
+(1 row)
+
+-- path extraction pattern for arrays nested in object or object nested in array as left operand
+-- having object at top level
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["c",0]';
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["c",-3]';
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT '{"a":["b"],"c":[1,2,3]}'::agtype #> '["a", 0]';
+ ?column?
+----------
+ "b"
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3]]]}'::agtype #> '["1", 2, 0, 0]';
+ ?column?
+----------
+ -3
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": true}]]]}'::agtype #> '["1", -1, -1, -1, "a"]';
+ ?column?
+----------
+ true
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a"]';
+ ?column?
+--------------------------------------------------
+ {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a", "c"]';
+ ?column?
+----------
+ "foo"
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a", "b", "d", -2]';
+ ?column?
+---------------
+ -1.9::numeric
+(1 row)
+
+-- having array at top level
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[4,"5"]';
+ ?column?
+----------
+ "five"
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[-1,"5"]';
+ ?column?
+----------
+ "five"
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":["five", "six"]}]'::agtype #> '[-1,"5",-1]';
+ ?column?
+----------
+ "six"
+(1 row)
+
+-- should return null
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '[0]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["c",3]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["c","3"]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3,4]}'::agtype #> '["c",4]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["c",-4]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["a","b"]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["a","c"]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["a", []]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":["b"],"c":[1,2,3]}'::agtype #> '["a", []]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a", "c", "d"]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a", "b", "d", -2, 0]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"0": true}'::agtype #> '[0]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '[null]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{}'::agtype #> '[null]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{}'::agtype #> '[{}]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype #> '["id"]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"a": "xyz", "b" : true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::edge'::agtype #> '["start_id"]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[4,5]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[-1,5]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[3, -1, 0]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[{}]'::agtype;
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[{"5":"five"}]'::agtype;
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"},6,7]'::agtype #> '["6", "7"]'::agtype;
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"},6,7]'::agtype #> '[6, 7]'::agtype;
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[null]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[null]'::agtype #> '[null]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[]'::agtype #> '[null]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[[-3, 1]]'::agtype #> '["0", "1.1"]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[[-3, 1]]'::agtype #> '["0", "true"]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '[[-3, 1]]'::agtype #> '["0", "string"]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a", "b", "d", "false"]';
+ ?column?
+----------
+
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a", "b", "d", "a"]';
+ ?column?
+----------
+
+(1 row)
+
+-- errors out
+SELECT '{"0": true}'::agtype #> '"0"';
+ERROR: right operand must be an array
+SELECT '{"n": 1}'::agtype #> '{"n": 1}';
+ERROR: right operand must be an array
+SELECT '[{"n": 1}]'::agtype #> '{"n": 1}';
+ERROR: right operand must be an array
+SELECT '[{"n": 100}]'::agtype #> '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex';
+ERROR: right operand must be an array
+SELECT '-19'::agtype #> '[-1]'::agtype;
+ERROR: scalar object must be a vertex or edge
+SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype #> '"a"';
+ERROR: right operand must be an array
+/*
+ * #>> operator to return the extracted value as text
+ */
+SELECT pg_typeof('{"a":"b","c":[1,2,3]}'::agtype #>> '["a"]');
+ pg_typeof
+-----------
+ text
+(1 row)
+
+SELECT pg_typeof('[0,1,2,[3,4],{"5":"five"}]'::agtype #>> '[-1,"5"]');
+ pg_typeof
+-----------
+ text
+(1 row)
+
+/*
+ * All the tests added for #> are also valid for #>>
+ */
+/*
+ * test the combination of #> and #>> operators below
+ * (left and right operands have to be agtype for #> and #>>,
+ * errors out when left operand is a text, i.e., the output of #>> operator)
+ */
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a"]' #> '["b", "d", -1]';
+ ?column?
+----------
+ false
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a"]' #> '["b", "d", "-1"]';
+ ?column?
+----------
+ false
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a"]' #>> '["b", "d", -1]';
+ ?column?
+----------
+ false
+(1 row)
+
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a"]' #>> '["b", "d", "-1"]';
+ ?column?
+----------
+ false
+(1 row)
+
+SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype #> '["e"]' #>> '["h", "-1"]';
+ ?column?
+----------
+ {}
+(1 row)
+
+SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype #> '["e"]' #> '["h", "-1"]' #>> '[]';
+ ?column?
+----------
+ {}
+(1 row)
+
+SELECT '[[-3, [1]]]'::agtype #> '["0", "1"]' #>> '[]';
+ ?column?
+----------
+ [1]
+(1 row)
+
+SELECT '[[-3, [1]]]'::agtype #> '["0", "1"]' #>> '[-1]';
+ ?column?
+----------
+ 1
+(1 row)
+
+-- errors out
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #>> '[-1, "5"]' #> '[]';
+ERROR: operator does not exist: text #> unknown
+LINE 1: ...[0,1,2,[3,4],{"5":"five"}]'::agtype #>> '[-1, "5"]' #> '[]';
+ ^
+HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #>> '["1", -1, -1, -1, "a"]' #> '["b", "d", -1]';
+ERROR: operator does not exist: text #> unknown
+LINE 1: ..."foo"}}]]]}'::agtype #>> '["1", -1, -1, -1, "a"]' #> '["b", ...
+ ^
+HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
+--
-- concat || operator
--
SELECT i, pg_typeof(i) FROM (SELECT '[0, 1]'::agtype || '[0, 1]'::agtype as i) a;
@@ -1548,6 +2046,260 @@
SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& n.json $$) as (a agtype);
ERROR: invalid agtype value for right operand
--
+-- path extraction #> operator
+--
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', 'c']} AS map
+ RETURN map #> []
+$$) AS (result agtype);
+ result
+---------------------------------------------------------------------------------
+ {"json": {"a": 1, "b": ["a", "b"], "c": {"d": ["a"]}}, "list": ["a", "b", "c"]}
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', 'c']} AS map
+ RETURN map #> ['json', 'c', 'd']
+$$) AS (result agtype);
+ result
+--------
+ ["a"]
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', 'c']} AS map
+ RETURN map #> ['json', 'c', 'd', -1]
+$$) AS (result agtype);
+ result
+--------
+ "a"
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', 'c']} AS map
+ RETURN map #> ['json', 'c', 'd', -1, -1]
+$$) AS (result agtype);
+ result
+--------
+
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', 'c']} AS map
+ RETURN map #> ['list', "-1"]
+$$) AS (result agtype);
+ result
+--------
+ "c"
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', ['c', 'd']]} AS map
+ RETURN map #> ['list', "-1", "-1"]
+$$) AS (result agtype);
+ result
+--------
+ "d"
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', ['c', 'd']]} AS map
+ RETURN map #> ['list', "-1", -1]
+$$) AS (result agtype);
+ result
+--------
+ "d"
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [[-3, 1]] AS list
+ RETURN list #> []
+$$) AS (result agtype);
+ result
+-----------
+ [[-3, 1]]
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [[-3, 1]] AS list
+ RETURN list #> [0]
+$$) AS (result agtype);
+ result
+---------
+ [-3, 1]
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [[-3, 1]] AS list
+ RETURN list #> [-1, -1]
+$$) AS (result agtype);
+ result
+--------
+ 1
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [[-3, 1]] AS list
+ RETURN list #> [-1, -1, -1]
+$$) AS (result agtype);
+ result
+--------
+
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [[-3, 1]] AS list
+ RETURN list #> [{}]
+$$) AS (result agtype);
+ result
+--------
+
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [null] AS list
+ RETURN list #> []
+$$) AS (result agtype);
+ result
+--------
+ [null]
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [null] AS list
+ RETURN list #> [-1, -1, -1]
+$$) AS (result agtype);
+ result
+--------
+
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [] AS list
+ RETURN list #> []
+$$) AS (result agtype);
+ result
+--------
+ []
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [] AS list
+ RETURN list #> ["a", 1]
+$$) AS (result agtype);
+ result
+--------
+
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> []$$) as (a agtype);
+ a
+-------------------------------------------------------------------------------
+ {"json": {"a": 1, "b": ["a", "b"], "c": {"d": "a"}}, "list": ["a", "b", "c"]}
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json"]$$) as (a agtype);
+ a
+--------------------------------------------
+ {"a": 1, "b": ["a", "b"], "c": {"d": "a"}}
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["jsonb"]$$) as (a agtype);
+ a
+---
+
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "a"]$$) as (a agtype);
+ a
+---
+ 1
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "a", 0]$$) as (a agtype);
+ a
+---
+
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "b"]$$) as (a agtype);
+ a
+------------
+ ["a", "b"]
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "b", -1]$$) as (a agtype);
+ a
+-----
+ "b"
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "b", "-1"]$$) as (a agtype);
+ a
+-----
+ "b"
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "b", -1, 0]$$) as (a agtype);
+ a
+---
+
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "c"]$$) as (a agtype);
+ a
+------------
+ {"d": "a"}
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "c", "d"]$$) as (a agtype);
+ a
+-----
+ "a"
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "c", "d", -1]$$) as (a agtype);
+ a
+---
+
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ['list', -1]$$) as (a agtype);
+ a
+-----
+ "c"
+(1 row)
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ['list', 4]$$) as (a agtype);
+ a
+---
+
+(1 row)
+
+-- errors out
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> "json"$$) as (a agtype);
+ERROR: right operand must be an array
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', 'c']} AS map
+ RETURN map #> 'jsonb'
+$$) AS (result agtype);
+ERROR: right operand must be an array
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [[-3, 1]] AS list
+ RETURN list #> 0
+$$) AS (result agtype);
+ERROR: right operand must be an array
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH 3 AS elem
+ RETURN elem #> [0]
+$$) AS (result agtype);
+ERROR: scalar object must be a vertex or edge
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH 'string' AS elem
+ RETURN elem #> [0]
+$$) AS (result agtype);
+ERROR: scalar object must be a vertex or edge
+--
-- concat || operator
--
SELECT * FROM cypher('jsonb_operators', $$ RETURN [1,2] || 2 $$) AS (result agtype);
diff --git a/regress/sql/jsonb_operators.sql b/regress/sql/jsonb_operators.sql
index 689c148..e8ed488 100644
--- a/regress/sql/jsonb_operators.sql
+++ b/regress/sql/jsonb_operators.sql
@@ -189,6 +189,132 @@
SELECT '{"a":null, "b":"qq"}'::agtype ?& '{"null"}';
--
+-- Agtype path extraction operators (#>, #>>)
+--
+
+/*
+ * #> operator to return the extracted value as agtype
+ */
+SELECT pg_typeof('{"a":"b","c":[1,2,3]}'::agtype #> '["a"]');
+SELECT pg_typeof('[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[-1,"5"]');
+
+-- left operand is agtype object, right operand should be an array of strings
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["a"]';
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["c"]';
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '[]';
+SELECT '{"0": true}'::agtype #> '["0"]';
+SELECT '{"a":"b","c":{"d": [1,2,3]}}'::agtype #> '["c", "d"]';
+SELECT '{"a":"b","c":{"d": {"e": -1}}}'::agtype #> '["c", "d", "e"]';
+
+-- left operand is vertex/edge/path, right operand should be an array of strings
+SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype #> '[]';
+SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype #> '["e", "h", -2]';
+SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"a": "xyz", "b" : true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::edge'::agtype #> '[]';
+SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"a": "xyz", "b" : true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::edge'::agtype #> '["i", "k", "l"]';
+
+-- left operand is agtype array, right operand should be an array of integers or valid integer strings
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[0]';
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[4]';
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[]';
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[-2]';
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[-2, -1]';
+SELECT '[[-3, 1]]'::agtype #> '[0, 1]';
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '["0"]';
+SELECT '[[-3, 1]]'::agtype #> '["0", "1"]';
+SELECT '[[-3, 1]]'::agtype #> '["0", 1]';
+SELECT '[[-3, 1]]'::agtype #> '["0", "-1"]';
+
+-- path extraction pattern for arrays nested in object or object nested in array as left operand
+-- having object at top level
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["c",0]';
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["c",-3]';
+SELECT '{"a":["b"],"c":[1,2,3]}'::agtype #> '["a", 0]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3]]]}'::agtype #> '["1", 2, 0, 0]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": true}]]]}'::agtype #> '["1", -1, -1, -1, "a"]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a"]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a", "c"]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a", "b", "d", -2]';
+
+-- having array at top level
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[4,"5"]';
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[-1,"5"]';
+SELECT '[0,1,2,[3,4],{"5":["five", "six"]}]'::agtype #> '[-1,"5",-1]';
+
+-- should return null
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '[0]';
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["c",3]';
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["c","3"]';
+SELECT '{"a":"b","c":[1,2,3,4]}'::agtype #> '["c",4]';
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["c",-4]';
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["a","b"]';
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["a","c"]';
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '["a", []]';
+SELECT '{"a":["b"],"c":[1,2,3]}'::agtype #> '["a", []]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a", "c", "d"]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a", "b", "d", -2, 0]';
+SELECT '{"0": true}'::agtype #> '[0]';
+SELECT '{"a":"b","c":[1,2,3]}'::agtype #> '[null]';
+SELECT '{}'::agtype #> '[null]';
+SELECT '{}'::agtype #> '[{}]';
+
+SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype #> '["id"]';
+SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"a": "xyz", "b" : true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::edge'::agtype #> '["start_id"]';
+
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[4,5]';
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[-1,5]';
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[3, -1, 0]';
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[{}]'::agtype;
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[{"5":"five"}]'::agtype;
+SELECT '[0,1,2,[3,4],{"5":"five"},6,7]'::agtype #> '["6", "7"]'::agtype;
+SELECT '[0,1,2,[3,4],{"5":"five"},6,7]'::agtype #> '[6, 7]'::agtype;
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #> '[null]';
+SELECT '[null]'::agtype #> '[null]';
+SELECT '[]'::agtype #> '[null]';
+SELECT '[[-3, 1]]'::agtype #> '["0", "1.1"]';
+SELECT '[[-3, 1]]'::agtype #> '["0", "true"]';
+SELECT '[[-3, 1]]'::agtype #> '["0", "string"]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a", "b", "d", "false"]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a", "b", "d", "a"]';
+
+-- errors out
+SELECT '{"0": true}'::agtype #> '"0"';
+SELECT '{"n": 1}'::agtype #> '{"n": 1}';
+SELECT '[{"n": 1}]'::agtype #> '{"n": 1}';
+SELECT '[{"n": 100}]'::agtype #> '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex';
+SELECT '-19'::agtype #> '[-1]'::agtype;
+SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype #> '"a"';
+
+/*
+ * #>> operator to return the extracted value as text
+ */
+SELECT pg_typeof('{"a":"b","c":[1,2,3]}'::agtype #>> '["a"]');
+SELECT pg_typeof('[0,1,2,[3,4],{"5":"five"}]'::agtype #>> '[-1,"5"]');
+
+/*
+ * All the tests added for #> are also valid for #>>
+ */
+
+/*
+ * test the combination of #> and #>> operators below
+ * (left and right operands have to be agtype for #> and #>>,
+ * errors out when left operand is a text, i.e., the output of #>> operator)
+ */
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a"]' #> '["b", "d", -1]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a"]' #> '["b", "d", "-1"]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a"]' #>> '["b", "d", -1]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #> '["1", -1, -1, -1, "a"]' #>> '["b", "d", "-1"]';
+
+SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype #> '["e"]' #>> '["h", "-1"]';
+SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype #> '["e"]' #> '["h", "-1"]' #>> '[]';
+
+SELECT '[[-3, [1]]]'::agtype #> '["0", "1"]' #>> '[]';
+SELECT '[[-3, [1]]]'::agtype #> '["0", "1"]' #>> '[-1]';
+
+-- errors out
+SELECT '[0,1,2,[3,4],{"5":"five"}]'::agtype #>> '[-1, "5"]' #> '[]';
+SELECT '{"a":"b","c":[1,2,3], "1" : [{}, {}, [[-3, {"a": {"b": {"d": [-1.9::numeric, false]}, "c": "foo"}}]]]}'::agtype #>> '["1", -1, -1, -1, "a"]' #> '["b", "d", -1]';
+
+--
-- concat || operator
--
SELECT i, pg_typeof(i) FROM (SELECT '[0, 1]'::agtype || '[0, 1]'::agtype as i) a;
@@ -378,6 +504,123 @@
SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& n.json $$) as (a agtype);
--
+-- path extraction #> operator
+--
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', 'c']} AS map
+ RETURN map #> []
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', 'c']} AS map
+ RETURN map #> ['json', 'c', 'd']
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', 'c']} AS map
+ RETURN map #> ['json', 'c', 'd', -1]
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', 'c']} AS map
+ RETURN map #> ['json', 'c', 'd', -1, -1]
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', 'c']} AS map
+ RETURN map #> ['list', "-1"]
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', ['c', 'd']]} AS map
+ RETURN map #> ['list', "-1", "-1"]
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', ['c', 'd']]} AS map
+ RETURN map #> ['list', "-1", -1]
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [[-3, 1]] AS list
+ RETURN list #> []
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [[-3, 1]] AS list
+ RETURN list #> [0]
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [[-3, 1]] AS list
+ RETURN list #> [-1, -1]
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [[-3, 1]] AS list
+ RETURN list #> [-1, -1, -1]
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [[-3, 1]] AS list
+ RETURN list #> [{}]
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [null] AS list
+ RETURN list #> []
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [null] AS list
+ RETURN list #> [-1, -1, -1]
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [] AS list
+ RETURN list #> []
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [] AS list
+ RETURN list #> ["a", 1]
+$$) AS (result agtype);
+
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> []$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json"]$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["jsonb"]$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "a"]$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "a", 0]$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "b"]$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "b", -1]$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "b", "-1"]$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "b", -1, 0]$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "c"]$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "c", "d"]$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ["json", "c", "d", -1]$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ['list', -1]$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> ['list', 4]$$) as (a agtype);
+
+-- errors out
+SELECT * FROM cypher('jsonb_operators',$$MATCH (n) RETURN n #> "json"$$) as (a agtype);
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH {json: {a: 1, b: ['a', 'b'], c: {d: ['a']}}, list: ['a', 'b', 'c']} AS map
+ RETURN map #> 'jsonb'
+$$) AS (result agtype);
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH [[-3, 1]] AS list
+ RETURN list #> 0
+$$) AS (result agtype);
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH 3 AS elem
+ RETURN elem #> [0]
+$$) AS (result agtype);
+SELECT * FROM cypher('jsonb_operators', $$
+ WITH 'string' AS elem
+ RETURN elem #> [0]
+$$) AS (result agtype);
+
+--
-- concat || operator
--
SELECT * FROM cypher('jsonb_operators', $$ RETURN [1,2] || 2 $$) AS (result agtype);
diff --git a/src/backend/parser/ag_scanner.l b/src/backend/parser/ag_scanner.l
index 74887ba..aee021b 100644
--- a/src/backend/parser/ag_scanner.l
+++ b/src/backend/parser/ag_scanner.l
@@ -227,17 +227,18 @@
* These are tokens that are used as operators and language constructs in
* Cypher, and some of them are structural characters in JSON.
*/
-any_exists "?|"
-all_exists "?&"
-concat "||"
-lt_gt "<>"
-lt_eq "<="
-gt_eq ">="
-dot_dot ".."
-plus_eq "+="
-eq_tilde "=~"
-typecast "::"
-self [?%()*+,\-./:;<=>[\]^{|}]
+any_exists "?|"
+all_exists "?&"
+concat "||"
+access_path "#>"
+lt_gt "<>"
+lt_eq "<="
+gt_eq ">="
+dot_dot ".."
+plus_eq "+="
+eq_tilde "=~"
+typecast "::"
+self [?%()*+,\-./:;<=>[\]^{|}]
other .
@@ -653,6 +654,14 @@
return token;
}
+{access_path} {
+ update_location();
+ token.type = AG_TOKEN_ACCESS_PATH;
+ token.value.s = yytext;
+ token.location = get_location();
+ return token;
+}
+
{any_exists} {
update_location();
token.type = AG_TOKEN_ANY_EXISTS;
diff --git a/src/backend/parser/cypher_gram.y b/src/backend/parser/cypher_gram.y
index 884af45..ff06786 100644
--- a/src/backend/parser/cypher_gram.y
+++ b/src/backend/parser/cypher_gram.y
@@ -76,7 +76,7 @@
/* operators that have more than 1 character */
%token NOT_EQ LT_EQ GT_EQ DOT_DOT TYPECAST PLUS_EQ EQ_TILDE CONCAT
-%token ANY_EXISTS ALL_EXISTS
+%token ACCESS_PATH ANY_EXISTS ALL_EXISTS
/* keywords in alphabetical order */
%token <keyword> ALL ANALYZE AND AS ASC ASCENDING
@@ -178,7 +178,7 @@
%right UNARY_MINUS
%nonassoc CONTAINS ENDS EQ_TILDE STARTS
%left '[' ']' '(' ')'
-%left '.'
+%left '.' ACCESS_PATH
%left TYPECAST
/*set operations*/
@@ -1342,6 +1342,10 @@
{
$$ = (Node *)makeSimpleA_Expr(AEXPR_OP, "||", $1, $3, @2);
}
+ | expr ACCESS_PATH expr
+ {
+ $$ = (Node *)makeSimpleA_Expr(AEXPR_OP, "#>", $1, $3, @2);
+ }
| expr '+' expr
{
$$ = (Node *)makeSimpleA_Expr(AEXPR_OP, "+", $1, $3, @2);
diff --git a/src/backend/parser/cypher_parser.c b/src/backend/parser/cypher_parser.c
index f9dfae7..dcd499d 100644
--- a/src/backend/parser/cypher_parser.c
+++ b/src/backend/parser/cypher_parser.c
@@ -47,6 +47,7 @@
TYPECAST,
PLUS_EQ,
EQ_TILDE,
+ ACCESS_PATH,
ANY_EXISTS,
ALL_EXISTS,
CONCAT
@@ -101,6 +102,7 @@
case AG_TOKEN_DOT_DOT:
case AG_TOKEN_PLUS_EQ:
case AG_TOKEN_EQ_TILDE:
+ case AG_TOKEN_ACCESS_PATH:
case AG_TOKEN_ALL_EXISTS:
case AG_TOKEN_ANY_EXISTS:
case AG_TOKEN_CONCAT:
diff --git a/src/backend/utils/adt/agtype_ops.c b/src/backend/utils/adt/agtype_ops.c
index 916f4df..b1e8f66 100644
--- a/src/backend/utils/adt/agtype_ops.c
+++ b/src/backend/utils/adt/agtype_ops.c
@@ -24,6 +24,7 @@
#include "postgres.h"
#include <math.h>
+#include <limits.h>
#include "catalog/pg_type_d.h"
#include "fmgr.h"
@@ -40,6 +41,7 @@
static void concat_to_agtype_string(agtype_value *result, char *lhs, int llen,
char *rhs, int rlen);
static char *get_string_from_agtype_value(agtype_value *agtv, int *length);
+static Datum get_agtype_path_all(FunctionCallInfo fcinfo, bool as_text);
static void concat_to_agtype_string(agtype_value *result, char *lhs, int llen,
char *rhs, int rlen)
@@ -1580,6 +1582,237 @@
return res;
}
+/*
+ * agtype path extraction operator '#>'. The right operand can
+ * either be an array of object keys or array indexes for extracting
+ * agtype sub-object or sub-array from the left operand.
+ */
+PG_FUNCTION_INFO_V1(agtype_extract_path);
+
+Datum agtype_extract_path(PG_FUNCTION_ARGS)
+{
+ return get_agtype_path_all(fcinfo, false);
+}
+
+/*
+ * agtype path extraction operator '#>>' that returns the extracted path
+ * as text.
+ */
+PG_FUNCTION_INFO_V1(agtype_extract_path_text);
+
+Datum agtype_extract_path_text(PG_FUNCTION_ARGS)
+{
+ return get_agtype_path_all(fcinfo, true);
+}
+
+static Datum get_agtype_path_all(FunctionCallInfo fcinfo, bool as_text)
+{
+ agtype *agt = AG_GET_ARG_AGTYPE_P(0);
+ agtype *path = AG_GET_ARG_AGTYPE_P(1);
+ agtype *res;
+ int npath;
+ int i;
+ bool have_object = false, have_array = false;
+ agtype_value *agtvp = NULL;
+ agtype_value tv;
+ agtype_container *container;
+
+ if (AGT_ROOT_IS_SCALAR(path) || AGT_ROOT_IS_OBJECT(path))
+ {
+ ereport(ERROR,(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("right operand must be an array")));
+ }
+
+ if (AGT_ROOT_IS_SCALAR(agt))
+ {
+ agt = agtype_value_to_agtype(extract_entity_properties(agt, true));
+ }
+
+ npath = AGT_ROOT_COUNT(path);
+ container = &agt->root;
+
+ /* Identify whether we have object, array, or scalar at top-level */
+ if (AGT_ROOT_IS_OBJECT(agt))
+ {
+ have_object = true;
+ }
+ else if (AGT_ROOT_IS_ARRAY(agt) && !AGT_ROOT_IS_SCALAR(agt))
+ {
+ have_array = true;
+ }
+ else
+ {
+ Assert(AGT_ROOT_IS_ARRAY(agt) && AGT_ROOT_IS_SCALAR(agt));
+
+ /* Extract the scalar value */
+ if (npath <= 0)
+ {
+ agtvp = get_ith_agtype_value_from_container(container, 0);
+ }
+ }
+
+ /*
+ * If RHS array is empty, return the entire LHS object/array, based on the
+ * assumption that we should not do any field or element extractions. In
+ * case of non-scalar, we can just hand back the agtype without much
+ * work but for the scalar case, fall through and deal with the value
+ * below the loop (This inconsistency arises because there's no easy way to
+ * generate an agtype_value directly for root-level containers)
+ */
+ if (npath <= 0 && agtvp == NULL)
+ {
+ if (as_text)
+ {
+ PG_RETURN_TEXT_P(cstring_to_text(agtype_to_cstring(NULL, container,
+ VARSIZE(agt))));
+ }
+ else
+ {
+ /* not text mode - just hand back the agtype */
+ AG_RETURN_AGTYPE_P(agt);
+ }
+ }
+
+ for (i = 0; i < npath; i++)
+ {
+ agtype_value *cur_key =
+ get_ith_agtype_value_from_container(&path->root, i);
+
+ if (have_object && cur_key->type == AGTV_STRING)
+ {
+ agtvp = find_agtype_value_from_container(container,
+ AGT_FOBJECT,
+ cur_key);
+ }
+ else if (have_array)
+ {
+ long lindex;
+ uint32 index;
+
+ /*
+ * for array on LHS, there should be an integer or a
+ * valid integer string on RHS
+ */
+ if (cur_key->type == AGTV_INTEGER)
+ {
+ lindex = cur_key->val.int_value;
+ }
+ else if (cur_key->type == AGTV_STRING)
+ {
+ /*
+ * extract the integer from the string,
+ * if character other than a digit is found, return null
+ */
+ char* str = NULL;
+ lindex = strtol(cur_key->val.string.val, &str, 10);
+
+ if (strcmp(str, ""))
+ {
+ PG_RETURN_NULL();
+ }
+ }
+ else
+ {
+ PG_RETURN_NULL();
+ }
+
+ if (lindex > INT_MAX || lindex < INT_MIN)
+ {
+ PG_RETURN_NULL();
+ }
+
+ if (lindex >= 0)
+ {
+ index = (uint32) lindex;
+ }
+ else
+ {
+ /* Handle negative subscript */
+ uint32 nelements;
+
+ /* Container must be an array, but make sure */
+ if (!AGTYPE_CONTAINER_IS_ARRAY(container))
+ {
+ elog(ERROR, "not an agtype array");
+ }
+
+ nelements = AGTYPE_CONTAINER_SIZE(container);
+
+ if (-lindex > nelements)
+ {
+ PG_RETURN_NULL();
+ }
+ else
+ {
+ index = nelements + lindex;
+ }
+ }
+
+ agtvp = get_ith_agtype_value_from_container(container, index);
+ }
+ else
+ {
+ PG_RETURN_NULL();
+ }
+
+ if (agtvp == NULL)
+ {
+ PG_RETURN_NULL();
+ }
+ else if (i == npath - 1)
+ {
+ break;
+ }
+
+ if (agtvp->type == AGTV_BINARY)
+ {
+ agtype_iterator_token r;
+ agtype_iterator *it =
+ agtype_iterator_init((agtype_container *)
+ agtvp->val.binary.data);
+
+ r = agtype_iterator_next(&it, &tv, true);
+ container = (agtype_container *) agtvp->val.binary.data;
+ have_object = r == WAGT_BEGIN_OBJECT;
+ have_array = r == WAGT_BEGIN_ARRAY;
+ }
+ else
+ {
+ have_object = agtvp->type == AGTV_OBJECT;
+ have_array = agtvp->type == AGTV_ARRAY;
+ }
+ }
+
+ if (as_text)
+ {
+ /* special-case output for string and null values */
+ if (agtvp->type == AGTV_STRING)
+ {
+ PG_RETURN_TEXT_P(cstring_to_text_with_len(agtvp->val.string.val,
+ agtvp->val.string.len));
+ }
+
+ if (agtvp->type == AGTV_NULL)
+ {
+ PG_RETURN_NULL();
+ }
+ }
+
+ res = agtype_value_to_agtype(agtvp);
+
+ if (as_text)
+ {
+ PG_RETURN_TEXT_P(cstring_to_text(agtype_to_cstring(NULL,
+ &res->root,
+ VARSIZE(res))));
+ }
+ else
+ {
+ /* not text mode - just hand back the agtype */
+ AG_RETURN_AGTYPE_P(res);
+ }
+}
+
static void ereport_op_str(const char *op, agtype *lhs, agtype *rhs)
{
const char *msgfmt;
diff --git a/src/include/parser/ag_scanner.h b/src/include/parser/ag_scanner.h
index cb11b44..edf71b4 100644
--- a/src/include/parser/ag_scanner.h
+++ b/src/include/parser/ag_scanner.h
@@ -46,6 +46,7 @@
AG_TOKEN_TYPECAST,
AG_TOKEN_PLUS_EQ,
AG_TOKEN_EQ_TILDE,
+ AG_TOKEN_ACCESS_PATH,
AG_TOKEN_ANY_EXISTS,
AG_TOKEN_ALL_EXISTS,
AG_TOKEN_CONCAT,
