Extend agtype containment operators (@>, <@) (#1285)
This PR is part of the patch originally authored by Josh Innis
for issue # 282. The work included in this PR is as follows:
- Enable the usage of left and right containment operators inside
the cypher queries
- Extend this operator to be used with scalars
- Add relevant regression tests
diff --git a/age--1.4.0.sql b/age--1.4.0.sql
index caebb22..440c680 100644
--- a/age--1.4.0.sql
+++ b/age--1.4.0.sql
@@ -3170,7 +3170,8 @@
CREATE OPERATOR CLASS ag_catalog.gin_agtype_ops
DEFAULT FOR TYPE agtype USING gin AS
- OPERATOR 7 @>,
+ OPERATOR 7 @>(agtype, agtype),
+ OPERATOR 8 <@(agtype, agtype),
OPERATOR 9 ?(agtype, agtype),
OPERATOR 10 ?|(agtype, agtype),
OPERATOR 11 ?&(agtype, agtype),
diff --git a/regress/expected/agtype.out b/regress/expected/agtype.out
index 9ee0073..f8860b6 100644
--- a/regress/expected/agtype.out
+++ b/regress/expected/agtype.out
@@ -3205,30 +3205,6 @@
(1 row)
-SELECT agtype_contains('{"id": 1}','{"id": 1}');
- agtype_contains
------------------
- t
-(1 row)
-
-SELECT agtype_contains('{"id": 1}','{"id": 2}');
- agtype_contains
------------------
- f
-(1 row)
-
-SELECT '{"id": 1}'::agtype @> '{"id": 1}';
- ?column?
-----------
- t
-(1 row)
-
-SELECT '{"id": 1}'::agtype @> '{"id": 2}';
- ?column?
-----------
- f
-(1 row)
-
--
-- Test STARTS WITH, ENDS WITH, and CONTAINS
--
diff --git a/regress/expected/jsonb_operators.out b/regress/expected/jsonb_operators.out
index 3657d95..45807bc 100644
--- a/regress/expected/jsonb_operators.out
+++ b/regress/expected/jsonb_operators.out
@@ -19,7 +19,7 @@
LOAD 'age';
SET search_path TO ag_catalog;
--
--- jsonb operators in AGE (?, ?&, ?|, ->, ->>, #>, #>>, ||)
+-- jsonb operators in AGE (?, ?&, ?|, ->, ->>, #>, #>>, ||, @>, <@)
--
--
-- Agtype exists operator
@@ -2312,6 +2312,348 @@
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
--
+-- Agtype containment operator
+--
+/*
+ * right contains @> operator
+ */
+-- returns true
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"a":"b"}';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"a":"b", "c":null}';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{}';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"name": "Bob", "tags": ["enim", "qui"]}'::agtype @> '{"tags":["qui"]}';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"name": "Bob", "tags": ["enim", "qui"]}'::agtype @> '{"tags":[]}';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '[1,2]'::agtype @> '[1,2,2]'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '[1,1,2]'::agtype @> '[1,2,2]'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '[[1,2]]'::agtype @> '[[1,2,2]]'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '[1,2,2]'::agtype @> '[]'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '[[1,2]]'::agtype @> '[[]]'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '[[1,2]]'::agtype @> '[[1,2,2], []]'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype @> '{"name": "A"}';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"name": "A"}' @> '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype @> '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT agtype_contains('{"id": 1}','{"id": 1}');
+ agtype_contains
+-----------------
+ t
+(1 row)
+
+SELECT agtype_contains('[1, 2, 3]','[3, 3]');
+ agtype_contains
+-----------------
+ t
+(1 row)
+
+-- In general, one thing should always contain itself
+SELECT '["9", ["7", "3"], 1]'::agtype @> '["9", ["7", "3"], 1]'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"a":"b", "b":1, "c":null}';
+ ?column?
+----------
+ t
+(1 row)
+
+-- returns false
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"a":"b", "g":null}';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"g":null}';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"a":"c"}';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"a":"b", "c":"q"}';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '[]';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '{"name": "Bob", "tags": ["enim", "qui"]}'::agtype @> '{"tags":{}}';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '[1,1,2]'::agtype @> '[1,2,[2]]'::agtype;
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '[1,2,2]'::agtype @> '{}'::agtype;
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '[[1,2]]'::agtype @> '[[{}]]'::agtype;
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '[[1,2]]'::agtype @> '[[1,2,2, []], []]'::agtype;
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '[[1,2]]'::agtype @> '[[1,2,2, []], [[]]]'::agtype;
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT agtype_contains('{"id": 1}','{"id": 2}');
+ agtype_contains
+-----------------
+ f
+(1 row)
+
+SELECT agtype_contains('[1, 2, 3]','[3, 3, []]');
+ agtype_contains
+-----------------
+ f
+(1 row)
+
+-- Raw scalar may contain another raw scalar, array may contain a raw scalar
+SELECT '[5]'::agtype @> '[5]';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '5'::agtype @> '5';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '[5]'::agtype @> '5';
+ ?column?
+----------
+ t
+(1 row)
+
+-- But a raw scalar cannot contain an array
+SELECT '5'::agtype @> '[5]';
+ ?column?
+----------
+ f
+(1 row)
+
+-- object/array containment is different from agtype_string_match_contains
+SELECT '{ "name": "Bob", "tags": [ "enim", "qui"]}'::agtype @> '{"tags":["qu"]}';
+ ?column?
+----------
+ f
+(1 row)
+
+/*
+ * left contains <@ operator
+ */
+-- returns true
+SELECT '{"a":"b"}'::agtype <@ '{"a":"b", "b":1, "c":null}';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"a":"b", "c":null}'::agtype <@ '{"a":"b", "b":1, "c":null}';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '[1,2,2]'::agtype <@ '[1,2]'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '[1,2,2]'::agtype <@ '[1,1,2]'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '[[1,2,2]]'::agtype <@ '[[1,2]]'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '[]'::agtype <@ '[1,2,2]'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"name": "A"}' <@ '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype <@ '{"name": "A"}';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype <@ '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT agtype_contained_by('{"id": 1}','{"id": 1}');
+ agtype_contained_by
+---------------------
+ t
+(1 row)
+
+-- returns false
+SELECT '[1,2,2]'::agtype <@ '[]'::agtype;
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '{"a":"b", "g":null}'::agtype <@ '{"a":"b", "b":1, "c":null}';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '{"g":null}'::agtype <@ '{"a":"b", "b":1, "c":null}';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '{"a":"c"}'::agtype <@ '{"a":"b", "b":1, "c":null}';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype <@ '{"id": 281474976710657, "label": "", "properties": {"name": "B"}}::vertex';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT agtype_contained_by('{"id": 1}','{"id": 2}');
+ agtype_contained_by
+---------------------
+ f
+(1 row)
+
+-- In general, one thing should always contain itself
+SELECT '["9", ["7", "3"], ["1"]]'::agtype <@ '["9", ["7", "3"], ["1"]]'::agtype;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '{"a":"b", "b":1, "c":null}'::agtype <@ '{"a":"b", "b":1, "c":null}';
+ ?column?
+----------
+ t
+(1 row)
+
+--
-- jsonb operators inside cypher queries
--
SELECT create_graph('jsonb_operators');
@@ -3225,6 +3567,191 @@
ERROR: invalid left operand for agtype concatenation
SELECT * FROM cypher('jsonb_operators', $$ MATCH (n) RETURN n.json || 1 $$) AS (result agtype);
ERROR: invalid right operand for agtype concatenation
+/*
+ * @> and <@ contains operators
+ */
+-- right contains @> operator
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n @> {json: {a: 1, b: ["a", "b"], c: {d: "a"}}, list: ["a", "b", "c"]}
+ RETURN n
+$$) as (a agtype);
+ a
+-------------------------------------------------------------------------------------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"json": {"a": 1, "b": ["a", "b"], "c": {"d": "a"}}, "list": ["a", "b", "c"]}}::vertex
+(1 row)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.json @> {c: {d: "a"}}
+ RETURN n
+$$) as (a agtype);
+ a
+-------------------------------------------------------------------------------------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"json": {"a": 1, "b": ["a", "b"], "c": {"d": "a"}}, "list": ["a", "b", "c"]}}::vertex
+(1 row)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.json @> {c: {}}
+ RETURN n
+$$) as (a agtype);
+ a
+-------------------------------------------------------------------------------------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"json": {"a": 1, "b": ["a", "b"], "c": {"d": "a"}}, "list": ["a", "b", "c"]}}::vertex
+(1 row)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.json @> {b: ["a"]}
+ RETURN n
+$$) as (a agtype);
+ a
+-------------------------------------------------------------------------------------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"json": {"a": 1, "b": ["a", "b"], "c": {"d": "a"}}, "list": ["a", "b", "c"]}}::vertex
+(1 row)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.json @> {b: ["a", "a"]}
+ RETURN n
+$$) as (a agtype);
+ a
+-------------------------------------------------------------------------------------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"json": {"a": 1, "b": ["a", "b"], "c": {"d": "a"}}, "list": ["a", "b", "c"]}}::vertex
+(1 row)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.list @> []
+ RETURN n
+$$) as (a agtype);
+ a
+-------------------------------------------------------------------------------------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"json": {"a": 1, "b": ["a", "b"], "c": {"d": "a"}}, "list": ["a", "b", "c"]}}::vertex
+(1 row)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.list[2] @> "c"
+ RETURN n
+$$) as (a agtype);
+ a
+-------------------------------------------------------------------------------------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"json": {"a": 1, "b": ["a", "b"], "c": {"d": "a"}}, "list": ["a", "b", "c"]}}::vertex
+(1 row)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n @> {}
+ RETURN n
+$$) as (a agtype);
+ a
+-------------------------------------------------------------------------------------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"json": {"a": 1, "b": ["a", "b"], "c": {"d": "a"}}, "list": ["a", "b", "c"]}}::vertex
+(1 row)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ RETURN properties(n).json @> {c: {d: "a"}}
+$$) as (a agtype);
+ a
+------
+ true
+(1 row)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ RETURN properties(n).json @> {c: {d: "b"}}
+$$) as (a agtype);
+ a
+-------
+ false
+(1 row)
+
+ SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.json @> {b: ["e"]}
+ RETURN n
+$$) as (a agtype);
+ a
+---
+(0 rows)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.list[2] @> []
+ RETURN n
+$$) as (a agtype);
+ a
+---
+(0 rows)
+
+-- left contains <@ operator
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ RETURN {c: {d: "a"}} <@ properties(n).json
+$$) as (a agtype);
+ a
+------
+ true
+(1 row)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE {c: {d: "a"}} <@ n.json
+ RETURN n
+$$) as (a agtype);
+ a
+-------------------------------------------------------------------------------------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"json": {"a": 1, "b": ["a", "b"], "c": {"d": "a"}}, "list": ["a", "b", "c"]}}::vertex
+(1 row)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE [] <@ n.list
+ RETURN n
+$$) as (a agtype);
+ a
+-------------------------------------------------------------------------------------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"json": {"a": 1, "b": ["a", "b"], "c": {"d": "a"}}, "list": ["a", "b", "c"]}}::vertex
+(1 row)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE {c: {d: "b"}} <@ n.json
+ RETURN n
+$$) as (a agtype);
+ a
+---
+(0 rows)
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE [] <@ n.json
+ RETURN n
+$$) as (a agtype);
+ a
+---
+(0 rows)
+
-- clean up
SELECT drop_graph('jsonb_operators', true);
NOTICE: drop cascades to 2 other objects
diff --git a/regress/sql/agtype.sql b/regress/sql/agtype.sql
index aa23651..718e623 100644
--- a/regress/sql/agtype.sql
+++ b/regress/sql/agtype.sql
@@ -905,12 +905,6 @@
SELECT age_start_id(agtype_in('null'));
SELECT age_end_id(agtype_in('null'));
-SELECT agtype_contains('{"id": 1}','{"id": 1}');
-SELECT agtype_contains('{"id": 1}','{"id": 2}');
-
-SELECT '{"id": 1}'::agtype @> '{"id": 1}';
-SELECT '{"id": 1}'::agtype @> '{"id": 2}';
-
--
-- Test STARTS WITH, ENDS WITH, and CONTAINS
--
diff --git a/regress/sql/jsonb_operators.sql b/regress/sql/jsonb_operators.sql
index d0ebb9e..aa79753 100644
--- a/regress/sql/jsonb_operators.sql
+++ b/regress/sql/jsonb_operators.sql
@@ -21,7 +21,7 @@
SET search_path TO ag_catalog;
--
--- jsonb operators in AGE (?, ?&, ?|, ->, ->>, #>, #>>, ||)
+-- jsonb operators in AGE (?, ?&, ?|, ->, ->>, #>, #>>, ||, @>, <@)
--
--
@@ -547,6 +547,99 @@
SELECT '3'::agtype || true;
--
+-- Agtype containment operator
+--
+
+/*
+ * right contains @> operator
+ */
+-- returns true
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"a":"b"}';
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"a":"b", "c":null}';
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{}';
+SELECT '{"name": "Bob", "tags": ["enim", "qui"]}'::agtype @> '{"tags":["qui"]}';
+SELECT '{"name": "Bob", "tags": ["enim", "qui"]}'::agtype @> '{"tags":[]}';
+
+SELECT '[1,2]'::agtype @> '[1,2,2]'::agtype;
+SELECT '[1,1,2]'::agtype @> '[1,2,2]'::agtype;
+SELECT '[[1,2]]'::agtype @> '[[1,2,2]]'::agtype;
+SELECT '[1,2,2]'::agtype @> '[]'::agtype;
+SELECT '[[1,2]]'::agtype @> '[[]]'::agtype;
+SELECT '[[1,2]]'::agtype @> '[[1,2,2], []]'::agtype;
+
+SELECT '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype @> '{"name": "A"}';
+SELECT '{"name": "A"}' @> '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype;
+SELECT '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype @> '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex';
+
+SELECT agtype_contains('{"id": 1}','{"id": 1}');
+SELECT agtype_contains('[1, 2, 3]','[3, 3]');
+
+-- In general, one thing should always contain itself
+SELECT '["9", ["7", "3"], 1]'::agtype @> '["9", ["7", "3"], 1]'::agtype;
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"a":"b", "b":1, "c":null}';
+
+-- returns false
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"a":"b", "g":null}';
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"g":null}';
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"a":"c"}';
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '{"a":"b", "c":"q"}';
+SELECT '{"a":"b", "b":1, "c":null}'::agtype @> '[]';
+SELECT '{"name": "Bob", "tags": ["enim", "qui"]}'::agtype @> '{"tags":{}}';
+
+SELECT '[1,1,2]'::agtype @> '[1,2,[2]]'::agtype;
+SELECT '[1,2,2]'::agtype @> '{}'::agtype;
+SELECT '[[1,2]]'::agtype @> '[[{}]]'::agtype;
+SELECT '[[1,2]]'::agtype @> '[[1,2,2, []], []]'::agtype;
+SELECT '[[1,2]]'::agtype @> '[[1,2,2, []], [[]]]'::agtype;
+
+SELECT agtype_contains('{"id": 1}','{"id": 2}');
+SELECT agtype_contains('[1, 2, 3]','[3, 3, []]');
+
+-- Raw scalar may contain another raw scalar, array may contain a raw scalar
+SELECT '[5]'::agtype @> '[5]';
+SELECT '5'::agtype @> '5';
+SELECT '[5]'::agtype @> '5';
+
+-- But a raw scalar cannot contain an array
+SELECT '5'::agtype @> '[5]';
+
+-- object/array containment is different from agtype_string_match_contains
+SELECT '{ "name": "Bob", "tags": [ "enim", "qui"]}'::agtype @> '{"tags":["qu"]}';
+
+/*
+ * left contains <@ operator
+ */
+-- returns true
+SELECT '{"a":"b"}'::agtype <@ '{"a":"b", "b":1, "c":null}';
+SELECT '{"a":"b", "c":null}'::agtype <@ '{"a":"b", "b":1, "c":null}';
+
+SELECT '[1,2,2]'::agtype <@ '[1,2]'::agtype;
+SELECT '[1,2,2]'::agtype <@ '[1,1,2]'::agtype;
+SELECT '[[1,2,2]]'::agtype <@ '[[1,2]]'::agtype;
+SELECT '[]'::agtype <@ '[1,2,2]'::agtype;
+
+SELECT '{"name": "A"}' <@ '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype;
+SELECT '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype <@ '{"name": "A"}';
+SELECT '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype <@ '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex';
+
+SELECT agtype_contained_by('{"id": 1}','{"id": 1}');
+
+-- returns false
+SELECT '[1,2,2]'::agtype <@ '[]'::agtype;
+
+SELECT '{"a":"b", "g":null}'::agtype <@ '{"a":"b", "b":1, "c":null}';
+SELECT '{"g":null}'::agtype <@ '{"a":"b", "b":1, "c":null}';
+SELECT '{"a":"c"}'::agtype <@ '{"a":"b", "b":1, "c":null}';
+
+SELECT '{"id": 281474976710657, "label": "", "properties": {"name": "A"}}::vertex'::agtype <@ '{"id": 281474976710657, "label": "", "properties": {"name": "B"}}::vertex';
+
+SELECT agtype_contained_by('{"id": 1}','{"id": 2}');
+
+-- In general, one thing should always contain itself
+SELECT '["9", ["7", "3"], ["1"]]'::agtype <@ '["9", ["7", "3"], ["1"]]'::agtype;
+SELECT '{"a":"b", "b":1, "c":null}'::agtype <@ '{"a":"b", "b":1, "c":null}';
+
+--
-- jsonb operators inside cypher queries
--
SELECT create_graph('jsonb_operators');
@@ -883,5 +976,127 @@
SELECT * FROM cypher('jsonb_operators', $$ WITH 'b' AS m WITH m, m || {a: 1} AS n RETURN n $$) AS (result agtype);
SELECT * FROM cypher('jsonb_operators', $$ MATCH (n) RETURN n.json || 1 $$) AS (result agtype);
+/*
+ * @> and <@ contains operators
+ */
+
+-- right contains @> operator
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n @> {json: {a: 1, b: ["a", "b"], c: {d: "a"}}, list: ["a", "b", "c"]}
+ RETURN n
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.json @> {c: {d: "a"}}
+ RETURN n
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.json @> {c: {}}
+ RETURN n
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.json @> {b: ["a"]}
+ RETURN n
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.json @> {b: ["a", "a"]}
+ RETURN n
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.list @> []
+ RETURN n
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.list[2] @> "c"
+ RETURN n
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n @> {}
+ RETURN n
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ RETURN properties(n).json @> {c: {d: "a"}}
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ RETURN properties(n).json @> {c: {d: "b"}}
+$$) as (a agtype);
+
+ SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.json @> {b: ["e"]}
+ RETURN n
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE n.list[2] @> []
+ RETURN n
+$$) as (a agtype);
+
+-- left contains <@ operator
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ RETURN {c: {d: "a"}} <@ properties(n).json
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE {c: {d: "a"}} <@ n.json
+ RETURN n
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE [] <@ n.list
+ RETURN n
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE {c: {d: "b"}} <@ n.json
+ RETURN n
+$$) as (a agtype);
+
+SELECT *
+FROM cypher('jsonb_operators', $$
+ MATCH (n)
+ WHERE [] <@ n.json
+ RETURN n
+$$) as (a agtype);
+
-- clean up
SELECT drop_graph('jsonb_operators', true);
\ No newline at end of file
diff --git a/src/backend/parser/ag_scanner.l b/src/backend/parser/ag_scanner.l
index aee021b..a71d17c 100644
--- a/src/backend/parser/ag_scanner.l
+++ b/src/backend/parser/ag_scanner.l
@@ -227,18 +227,20 @@
* 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 "||"
-access_path "#>"
-lt_gt "<>"
-lt_eq "<="
-gt_eq ">="
-dot_dot ".."
-plus_eq "+="
-eq_tilde "=~"
-typecast "::"
-self [?%()*+,\-./:;<=>[\]^{|}]
+left_contains "<@"
+right_contains "@>"
+any_exists "?|"
+all_exists "?&"
+concat "||"
+access_path "#>"
+lt_gt "<>"
+lt_eq "<="
+gt_eq ">="
+dot_dot ".."
+plus_eq "+="
+eq_tilde "=~"
+typecast "::"
+self [?%()*+,\-./:;<=>[\]^{|}]
other .
@@ -670,6 +672,22 @@
return token;
}
+{left_contains} {
+ update_location();
+ token.type = AG_TOKEN_LEFT_CONTAINS;
+ token.value.s = yytext;
+ token.location = get_location();
+ return token;
+}
+
+{right_contains} {
+ update_location();
+ token.type = AG_TOKEN_RIGHT_CONTAINS;
+ token.value.s = yytext;
+ token.location = get_location();
+ return token;
+}
+
{all_exists} {
update_location();
token.type = AG_TOKEN_ALL_EXISTS;
diff --git a/src/backend/parser/cypher_gram.y b/src/backend/parser/cypher_gram.y
index b722da1..7a71160 100644
--- a/src/backend/parser/cypher_gram.y
+++ b/src/backend/parser/cypher_gram.y
@@ -77,7 +77,7 @@
/* operators that have more than 1 character */
%token NOT_EQ LT_EQ GT_EQ DOT_DOT TYPECAST PLUS_EQ EQ_TILDE CONCAT
-%token ACCESS_PATH ANY_EXISTS ALL_EXISTS
+%token ACCESS_PATH LEFT_CONTAINS RIGHT_CONTAINS ANY_EXISTS ALL_EXISTS
/* keywords in alphabetical order */
%token <keyword> ALL ANALYZE AND AS ASC ASCENDING
@@ -171,7 +171,7 @@
%left XOR
%right NOT
%left '=' NOT_EQ '<' LT_EQ '>' GT_EQ
-%left '|' '&' '?' ANY_EXISTS ALL_EXISTS
+%left '@' '|' '&' '?' LEFT_CONTAINS RIGHT_CONTAINS ANY_EXISTS ALL_EXISTS
%left '+' '-' CONCAT
%left '*' '/' '%'
%left '^'
@@ -1406,6 +1406,14 @@
{
$$ = build_comparison_expression($1, $3, ">=", @2);
}
+ | expr LEFT_CONTAINS expr
+ {
+ $$ = (Node *)makeSimpleA_Expr(AEXPR_OP, "<@", $1, $3, @2);
+ }
+ | expr RIGHT_CONTAINS expr
+ {
+ $$ = (Node *)makeSimpleA_Expr(AEXPR_OP, "@>", $1, $3, @2);
+ }
| expr '?' expr %prec '.'
{
$$ = (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 dcd499d..1412295 100644
--- a/src/backend/parser/cypher_parser.c
+++ b/src/backend/parser/cypher_parser.c
@@ -47,6 +47,8 @@
TYPECAST,
PLUS_EQ,
EQ_TILDE,
+ LEFT_CONTAINS,
+ RIGHT_CONTAINS,
ACCESS_PATH,
ANY_EXISTS,
ALL_EXISTS,
@@ -105,6 +107,8 @@
case AG_TOKEN_ACCESS_PATH:
case AG_TOKEN_ALL_EXISTS:
case AG_TOKEN_ANY_EXISTS:
+ case AG_TOKEN_LEFT_CONTAINS:
+ case AG_TOKEN_RIGHT_CONTAINS:
case AG_TOKEN_CONCAT:
break;
case AG_TOKEN_TYPECAST:
diff --git a/src/backend/utils/adt/agtype_ops.c b/src/backend/utils/adt/agtype_ops.c
index 289c694..0e4d4a1 100644
--- a/src/backend/utils/adt/agtype_ops.c
+++ b/src/backend/utils/adt/agtype_ops.c
@@ -1424,6 +1424,22 @@
properties = AG_GET_ARG_AGTYPE_P(0);
constraints = AG_GET_ARG_AGTYPE_P(1);
+ if (AGT_ROOT_IS_SCALAR(properties)
+ && AGTE_IS_AGTYPE(properties->root.children[0]))
+ {
+ properties =
+ agtype_value_to_agtype(extract_entity_properties(properties,
+ false));
+ }
+
+ if (AGT_ROOT_IS_SCALAR(constraints)
+ && AGTE_IS_AGTYPE(constraints->root.children[0]))
+ {
+ constraints =
+ agtype_value_to_agtype(extract_entity_properties(constraints,
+ false));
+ }
+
if (AGT_ROOT_IS_OBJECT(properties) != AGT_ROOT_IS_OBJECT(constraints))
{
PG_RETURN_BOOL(false);
@@ -1454,6 +1470,22 @@
properties = AG_GET_ARG_AGTYPE_P(0);
constraints = AG_GET_ARG_AGTYPE_P(1);
+ if (AGT_ROOT_IS_SCALAR(properties)
+ && AGTE_IS_AGTYPE(properties->root.children[0]))
+ {
+ properties =
+ agtype_value_to_agtype(extract_entity_properties(properties,
+ false));
+ }
+
+ if (AGT_ROOT_IS_SCALAR(constraints)
+ && AGTE_IS_AGTYPE(constraints->root.children[0]))
+ {
+ constraints =
+ agtype_value_to_agtype(extract_entity_properties(constraints,
+ false));
+ }
+
constraint_it = agtype_iterator_init(&constraints->root);
property_it = agtype_iterator_init(&properties->root);
diff --git a/src/include/parser/ag_scanner.h b/src/include/parser/ag_scanner.h
index edf71b4..16f7f9c 100644
--- a/src/include/parser/ag_scanner.h
+++ b/src/include/parser/ag_scanner.h
@@ -46,6 +46,8 @@
AG_TOKEN_TYPECAST,
AG_TOKEN_PLUS_EQ,
AG_TOKEN_EQ_TILDE,
+ AG_TOKEN_LEFT_CONTAINS,
+ AG_TOKEN_RIGHT_CONTAINS,
AG_TOKEN_ACCESS_PATH,
AG_TOKEN_ANY_EXISTS,
AG_TOKEN_ALL_EXISTS,
