This is an automated email from the ASF dual-hosted git repository.
jgemignani pushed a commit to branch PG14
in repository https://gitbox.apache.org/repos/asf/age.git
The following commit(s) were added to refs/heads/PG14 by this push:
new 1ddb52f2 Extend agtype containment operators (@>, <@) (#1285) (#1418)
1ddb52f2 is described below
commit 1ddb52f2e5ddddf172291d19ed36b9a10d72d5f7
Author: Zainab Saad <[email protected]>
AuthorDate: Wed Nov 29 01:14:10 2023 +0500
Extend agtype containment operators (@>, <@) (#1285) (#1418)
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
---
age--1.4.0.sql | 3 +-
regress/expected/agtype.out | 24 --
regress/expected/jsonb_operators.out | 529 ++++++++++++++++++++++++++++++++++-
regress/sql/agtype.sql | 6 -
regress/sql/jsonb_operators.sql | 217 +++++++++++++-
src/backend/parser/ag_scanner.l | 42 ++-
src/backend/parser/cypher_gram.y | 12 +-
src/backend/parser/cypher_parser.c | 4 +
src/backend/utils/adt/agtype_ops.c | 32 +++
src/include/parser/ag_scanner.h | 2 +
10 files changed, 824 insertions(+), 47 deletions(-)
diff --git a/age--1.4.0.sql b/age--1.4.0.sql
index 74ab360f..61286e5d 100644
--- a/age--1.4.0.sql
+++ b/age--1.4.0.sql
@@ -3140,7 +3140,8 @@ PARALLEL SAFE;
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 d6e70f1c..5b5c74a2 100644
--- a/regress/expected/agtype.out
+++ b/regress/expected/agtype.out
@@ -3058,30 +3058,6 @@ SELECT age_end_id(agtype_in('null'));
(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 a665bd66..2248f5ee 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
@@ -1730,6 +1730,348 @@ ERROR: operator does not exist: agtype || boolean
LINE 1: SELECT '3'::agtype || true;
^
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
--
@@ -2399,6 +2741,191 @@ SELECT * FROM cypher('jsonb_operators', $$ WITH 'b' AS
m WITH m, m || {a: 1} AS
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 f7a905af..2122340c 100644
--- a/regress/sql/agtype.sql
+++ b/regress/sql/agtype.sql
@@ -878,12 +878,6 @@ SELECT age_id(agtype_in('null'));
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 e8ed4880..74e50275 100644
--- a/regress/sql/jsonb_operators.sql
+++ b/regress/sql/jsonb_operators.sql
@@ -21,7 +21,7 @@ LOAD 'age';
SET search_path TO ag_catalog;
--
--- jsonb operators in AGE (?, ?&, ?|, ->, ->>, #>, #>>, ||)
+-- jsonb operators in AGE (?, ?&, ?|, ->, ->>, #>, #>>, ||, @>, <@)
--
--
@@ -412,6 +412,99 @@ SELECT ('{"a": "5"}'::agtype || '{"a": {}}'::agtype ||
'5') || '[5]'::agtype;
SELECT '3'::agtype || 4;
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
--
@@ -646,5 +739,127 @@ SELECT * FROM cypher('jsonb_operators', $$ RETURN true ||
{a: 'string'} || true
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 aee021b1..a71d17c6 100644
--- a/src/backend/parser/ag_scanner.l
+++ b/src/backend/parser/ag_scanner.l
@@ -227,18 +227,20 @@ param \${id}
* 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 @@ ag_token token;
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 0fd1a0da..7feaf733 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 '^'
@@ -1415,6 +1415,14 @@ expr:
{
$$ = 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 dcd499db..14122950 100644
--- a/src/backend/parser/cypher_parser.c
+++ b/src/backend/parser/cypher_parser.c
@@ -47,6 +47,8 @@ int cypher_yylex(YYSTYPE *lvalp, YYLTYPE *llocp, ag_scanner_t
scanner)
TYPECAST,
PLUS_EQ,
EQ_TILDE,
+ LEFT_CONTAINS,
+ RIGHT_CONTAINS,
ACCESS_PATH,
ANY_EXISTS,
ALL_EXISTS,
@@ -105,6 +107,8 @@ int cypher_yylex(YYSTYPE *lvalp, YYLTYPE *llocp,
ag_scanner_t scanner)
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 efc07911..98a84f04 100644
--- a/src/backend/utils/adt/agtype_ops.c
+++ b/src/backend/utils/adt/agtype_ops.c
@@ -1174,6 +1174,22 @@ Datum agtype_contains(PG_FUNCTION_ARGS)
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);
@@ -1204,6 +1220,22 @@ Datum agtype_contained_by(PG_FUNCTION_ARGS)
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 edf71b46..16f7f9c8 100644
--- a/src/include/parser/ag_scanner.h
+++ b/src/include/parser/ag_scanner.h
@@ -46,6 +46,8 @@ typedef enum ag_token_type
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,
