This is an automated email from the ASF dual-hosted git repository. jgemignani pushed a commit to branch PG15 in repository https://gitbox.apache.org/repos/asf/age.git
commit 04fd72d6e6405cbf68a6813e8f4c805dfbdc3d91 Author: Muhammad Taha Naveed <[email protected]> AuthorDate: Sat Sep 9 01:20:06 2023 +0500 Add exist(?, ?|, ?&) operators for agtype (#1218) - Added exist(?, ?|, ?&) operators for agtype similar to jsonb operators in postgresql. - Allow exist(?, ?|, ?&) operators to be used inside cypher queries. - Added regression tests. --- age--1.4.0.sql | 55 +- regress/expected/agtype.out | 72 --- regress/expected/jsonb_operators.out | 1083 ++++++++++++++++++++++++++++++++++ regress/sql/agtype.sql | 19 - regress/sql/jsonb_operators.sql | 248 ++++++++ src/backend/parser/ag_scanner.l | 36 +- src/backend/parser/cypher_gram.y | 14 + src/backend/parser/cypher_parser.c | 4 + src/backend/utils/adt/agtype.c | 61 +- src/backend/utils/adt/agtype_gin.c | 43 +- src/backend/utils/adt/agtype_ops.c | 159 +++++ src/include/parser/ag_scanner.h | 2 + src/include/utils/agtype.h | 4 + 13 files changed, 1676 insertions(+), 124 deletions(-) diff --git a/age--1.4.0.sql b/age--1.4.0.sql index 213b397f..1d511663 100644 --- a/age--1.4.0.sql +++ b/age--1.4.0.sql @@ -2981,6 +2981,23 @@ CREATE OPERATOR ? ( JOIN = contjoinsel ); +CREATE FUNCTION ag_catalog.agtype_exists_agtype(agtype, agtype) +RETURNS boolean +LANGUAGE c +IMMUTABLE +RETURNS NULL ON NULL INPUT +PARALLEL SAFE +AS 'MODULE_PATHNAME'; + +CREATE OPERATOR ? ( + LEFTARG = agtype, + RIGHTARG = agtype, + FUNCTION = ag_catalog.agtype_exists_agtype, + COMMUTATOR = '?', + RESTRICT = contsel, + JOIN = contjoinsel +); + CREATE FUNCTION ag_catalog.agtype_exists_any(agtype, text[]) RETURNS boolean LANGUAGE c @@ -2997,6 +3014,22 @@ CREATE OPERATOR ?| ( JOIN = contjoinsel ); +CREATE FUNCTION ag_catalog.agtype_exists_any_agtype(agtype, agtype) +RETURNS boolean +LANGUAGE c +IMMUTABLE +RETURNS NULL ON NULL INPUT +PARALLEL SAFE +AS 'MODULE_PATHNAME'; + +CREATE OPERATOR ?| ( + LEFTARG = agtype, + RIGHTARG = agtype, + FUNCTION = ag_catalog.agtype_exists_any_agtype, + RESTRICT = contsel, + JOIN = contjoinsel +); + CREATE FUNCTION ag_catalog.agtype_exists_all(agtype, text[]) RETURNS boolean LANGUAGE c @@ -3013,6 +3046,22 @@ CREATE OPERATOR ?& ( JOIN = contjoinsel ); +CREATE FUNCTION ag_catalog.agtype_exists_all_agtype(agtype, agtype) +RETURNS boolean +LANGUAGE c +IMMUTABLE +RETURNS NULL ON NULL INPUT +PARALLEL SAFE +AS 'MODULE_PATHNAME'; + +CREATE OPERATOR ?& ( + LEFTARG = agtype, + RIGHTARG = agtype, + FUNCTION = ag_catalog.agtype_exists_all_agtype, + RESTRICT = contsel, + JOIN = contjoinsel +); + -- -- agtype GIN support -- @@ -3062,9 +3111,9 @@ PARALLEL SAFE; CREATE OPERATOR CLASS ag_catalog.gin_agtype_ops DEFAULT FOR TYPE agtype USING gin AS OPERATOR 7 @>, - OPERATOR 9 ?(agtype, text), - OPERATOR 10 ?|(agtype, text[]), - OPERATOR 11 ?&(agtype, text[]), + OPERATOR 9 ?(agtype, agtype), + OPERATOR 10 ?|(agtype, agtype), + OPERATOR 11 ?&(agtype, agtype), FUNCTION 1 ag_catalog.gin_compare_agtype(text,text), FUNCTION 2 ag_catalog.gin_extract_agtype(agtype, internal), FUNCTION 3 ag_catalog.gin_extract_agtype_query(agtype, internal, int2, diff --git a/regress/expected/agtype.out b/regress/expected/agtype.out index cf451b43..c418b69d 100644 --- a/regress/expected/agtype.out +++ b/regress/expected/agtype.out @@ -2762,78 +2762,6 @@ SELECT '{"id": 1}'::agtype @> '{"id": 2}'; f (1 row) -SELECT agtype_exists('{"id": 1}','id'); - agtype_exists ---------------- - t -(1 row) - -SELECT agtype_exists('{"id": 1}','not_id'); - agtype_exists ---------------- - f -(1 row) - -SELECT '{"id": 1}'::agtype ? 'id'; - ?column? ----------- - t -(1 row) - -SELECT '{"id": 1}'::agtype ? 'not_id'; - ?column? ----------- - f -(1 row) - -SELECT agtype_exists_any('{"id": 1}', array['id']); - agtype_exists_any -------------------- - t -(1 row) - -SELECT agtype_exists_any('{"id": 1}', array['not_id']); - agtype_exists_any -------------------- - f -(1 row) - -SELECT '{"id": 1}'::agtype ?| array['id']; - ?column? ----------- - t -(1 row) - -SELECT '{"id": 1}'::agtype ?| array['not_id']; - ?column? ----------- - f -(1 row) - -SELECT agtype_exists_all('{"id": 1}', array['id']); - agtype_exists_all -------------------- - t -(1 row) - -SELECT agtype_exists_all('{"id": 1}', array['not_id']); - agtype_exists_all -------------------- - f -(1 row) - -SELECT '{"id": 1}'::agtype ?& array['id']; - ?column? ----------- - t -(1 row) - -SELECT '{"id": 1}'::agtype ?& array['not_id']; - ?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 71e4fe1a..78b2f897 100644 --- a/regress/expected/jsonb_operators.out +++ b/regress/expected/jsonb_operators.out @@ -22,6 +22,789 @@ SET search_path TO ag_catalog; -- jsonb operators in AGE (?, ?&, ?|, ->, ->>, #>, #>>, ||) -- -- +-- Agtype exists operator +-- +-- exists (?) +-- should return 't' +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"n"'; + ?column? +---------- + t +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"a"'; + ?column? +---------- + t +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"b"'; + ?column? +---------- + t +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"d"'; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ? '"label"'; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ? '"n"'; + ?column? +---------- + t +(1 row) + +SELECT '["1","2"]'::agtype ? '"1"'; + ?column? +---------- + t +(1 row) + +SELECT '["hello", "world"]'::agtype ? '"hello"'; + ?column? +---------- + t +(1 row) + +SELECT agtype_exists('{"id": 1}','id'::text); + agtype_exists +--------------- + t +(1 row) + +SELECT '{"id": 1}'::agtype ? 'id'::text; + ?column? +---------- + t +(1 row) + +-- should return 'f' +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"e"'; + ?column? +---------- + f +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"e1"'; + ?column? +---------- + f +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"1"'; + ?column? +---------- + f +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '1'; + ?column? +---------- + f +(1 row) + +SELECT '[{"id": 281474976710658, "label": "", "properties": {"n": 100}}]'::agtype ? '"id"'; + ?column? +---------- + f +(1 row) + +SELECT '[{"id": 281474976710658, "label": "", "properties": {"n": 100}}]'::agtype ? 'null'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ? 'null'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ? 'null'; + ?column? +---------- + f +(1 row) + +SELECT '["hello", "world"]'::agtype ? '"hell"'; + ?column? +---------- + f +(1 row) + +SELECT agtype_exists('{"id": 1}','not_id'::text); + agtype_exists +--------------- + f +(1 row) + +SELECT '{"id": 1}'::agtype ? 'not_id'::text; + ?column? +---------- + f +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '["e1", "n"]'; + ?column? +---------- + f +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '["n"]'; + ?column? +---------- + f +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '["n", "a", "e"]'; + ?column? +---------- + f +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '{"n": null}'; + ?column? +---------- + f +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '{"n": null, "b": true}'; + ?column? +---------- + f +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '["e1"]'; + ?column? +---------- + f +(1 row) + +-- errors out +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? 'e1'; +ERROR: invalid input syntax for type agtype +LINE 1: ...a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? 'e1'; + ^ +DETAIL: Expected agtype value, but found "e1". +CONTEXT: agtype data, line 1: e1 +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? 'e'; +ERROR: invalid input syntax for type agtype +LINE 1: ..."a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? 'e'; + ^ +DETAIL: Expected agtype value, but found "e". +CONTEXT: agtype data, line 1: e +-- Exists any (?|) +-- should return 't' +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["a","b"]'; + ?column? +---------- + t +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["b","a"]'; + ?column? +---------- + t +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["c","a"]'; + ?column? +---------- + t +(1 row) + +SELECT '{"1":null, "b":"qq"}'::agtype ?| '["c","1"]'; + ?column? +---------- + t +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["a","a", "b", "b", "b"]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '[1,2,3]'::agtype ?| '[1,2,3,4]'; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[null,"id"]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '["id",null]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[true,"id"]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[1,"id"]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[null,null,"n"]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '["n",null]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT agtype_exists_any('{"id": 1}', array['id']); + agtype_exists_any +------------------- + t +(1 row) + +SELECT '{"id": 1}'::agtype ?| array['id']; + ?column? +---------- + t +(1 row) + +-- should return 'f' +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["c","d"]'; + ?column? +---------- + f +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["1","2"]'; + ?column? +---------- + f +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["c","1"]'; + ?column? +---------- + f +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?| '[]'; + ?column? +---------- + f +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["c","d"]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[null]'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[]'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[null,null]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[null, "idk"]'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[""]'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex' ?| '[null,"idk"]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?| '[null,null,"idk"]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?| '["idk",null]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[null]'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[]'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[null,null]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[[""]]'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[null,null, "idk"]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[null,null,"idk"]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '["start_idk",null]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?| '[["a"], ["b"]]'; + ?column? +---------- + f +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?| '[["a"], ["b"], ["c"]]'; + ?column? +---------- + f +(1 row) + +SELECT '[null]'::agtype ?| '[null]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT agtype_exists_any('{"id": 1}', array['not_id']); + agtype_exists_any +------------------- + f +(1 row) + +SELECT '{"id": 1}'::agtype ?| array['not_id']; + ?column? +---------- + f +(1 row) + +-- errors out +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ?| '"b"'; +ERROR: invalid agtype value for right operand +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ?| '"d"'; +ERROR: invalid agtype value for right operand +SELECT '{"a":null, "b":"qq"}'::agtype ?| '{"a", "b"}'; +ERROR: invalid input syntax for type agtype +LINE 1: SELECT '{"a":null, "b":"qq"}'::agtype ?| '{"a", "b"}'; + ^ +DETAIL: Expected ":", but found ",". +CONTEXT: agtype data, line 1: {"a",... +SELECT '{"a":null, "b":"qq"}'::agtype ?| ''; +ERROR: invalid input syntax for type agtype +LINE 1: SELECT '{"a":null, "b":"qq"}'::agtype ?| ''; + ^ +DETAIL: The input string ended unexpectedly. +CONTEXT: agtype data, line 1: +SELECT '{"a":null, "b":"qq"}'::agtype ?| '""'; +ERROR: invalid agtype value for right operand +SELECT '{"a":null, "b":"qq"}'::agtype ?| '{""}'; +ERROR: invalid input syntax for type agtype +LINE 1: SELECT '{"a":null, "b":"qq"}'::agtype ?| '{""}'; + ^ +DETAIL: Expected ":", but found "}". +CONTEXT: agtype data, line 1: {""} +SELECT '{"a":null, "b":"qq"}'::agtype ?| '{}'; +ERROR: invalid agtype value for right operand +SELECT '{"a":null, "b":"qq"}'::agtype ?| '0'::agtype; +ERROR: invalid agtype value for right operand +SELECT '{"a":null, "b":"qq"}'::agtype ?| '0'; +ERROR: invalid agtype value for right operand +-- Exists all (?&) +-- should return 't' +SELECT '{"a":null, "b":"qq"}'::agtype ?& '["a","b"]'; + ?column? +---------- + t +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?& '["b","a"]'; + ?column? +---------- + t +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?& '["a","a", "b", "b", "b"]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[null]'; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[]'; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[null,null]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[null,null,"id"]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '["id",null]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '[null]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '[]'; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '[null,null]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '[null,null,"n"]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '["n",null]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[null]'; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[]'; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[null,null]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[null,null,"n"]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '["n",null]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT '[1,2,3]'::agtype ?& '[1,2,3]'; + ?column? +---------- + t +(1 row) + +SELECT '[1,2,3]'::agtype ?& '[1,2,3,null]'; + ?column? +---------- + t +(1 row) + +SELECT '[1,2,3]'::agtype ?& '[null, null]'; + ?column? +---------- + t +(1 row) + +SELECT '[1,2,3]'::agtype ?& '[null, null, null]'; + ?column? +---------- + t +(1 row) + +SELECT '[1,2,3]'::agtype ?& '[]'; + ?column? +---------- + t +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?& '[]'; + ?column? +---------- + t +(1 row) + +SELECT '[null]'::agtype ?& '[null]'::agtype; + ?column? +---------- + t +(1 row) + +SELECT agtype_exists_all('{"id": 1}', array['id']); + agtype_exists_all +------------------- + t +(1 row) + +SELECT '{"id": 1}'::agtype ?& array['id']; + ?column? +---------- + t +(1 row) + +-- should return 'f' +SELECT '{"a":null, "b":"qq"}'::agtype ?& '["c","a"]'; + ?column? +---------- + f +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?& '["a","b", "c"]'; + ?column? +---------- + f +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?& '["c","d"]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '[1,2,3]'::agtype ?& '[1,2,3,4]'; + ?column? +---------- + f +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?& '[["a"]]'; + ?column? +---------- + f +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?& '[["a"], ["b"]]'; + ?column? +---------- + f +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?& '[["a"], ["b"], ["c"]]'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[null, "idk"]'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[""]'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex' ?& '[null,"idk"]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '[null,null,"idk"]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '["idk",null]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[1,"id"]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[true,"id"]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[null, "idk"]'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[[""]]'; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[null,null, "idk"]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[null,null,"idk"]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '["start_idk",null]'::agtype; + ?column? +---------- + f +(1 row) + +SELECT '{"a":null, "b":"qq"}'::agtype ?& '[null, "c", "a"]'; + ?column? +---------- + f +(1 row) + +SELECT agtype_exists_all('{"id": 1}', array['not_id']); + agtype_exists_all +------------------- + f +(1 row) + +SELECT '{"id": 1}'::agtype ?& array['not_id']; + ?column? +---------- + f +(1 row) + +-- errors out +SELECT '{"a":null, "b":"qq"}'::agtype ?& '"d"'; +ERROR: invalid agtype value for right operand +SELECT '{"a":null, "b":"qq"}'::agtype ?& '"a"'; +ERROR: invalid agtype value for right operand +SELECT '{"a":null, "b":"qq"}'::agtype ?& '" "'; +ERROR: invalid agtype value for right operand +SELECT '{"a":null, "b":"qq"}'::agtype ?& '""'; +ERROR: invalid agtype value for right operand +SELECT '{"a":null, "b":"qq"}'::agtype ?& '"null"'; +ERROR: invalid agtype value for right operand +SELECT '{"a":null, "b":"qq"}'::agtype ?& '{"a", "b", "c"}'; +ERROR: invalid input syntax for type agtype +LINE 1: SELECT '{"a":null, "b":"qq"}'::agtype ?& '{"a", "b", "c"}'; + ^ +DETAIL: Expected ":", but found ",". +CONTEXT: agtype data, line 1: {"a",... +SELECT '{"a":null, "b":"qq"}'::agtype ?& '{}'; +ERROR: invalid agtype value for right operand +SELECT '{"a":null, "b":"qq"}'::agtype ?& '{""}'; +ERROR: invalid input syntax for type agtype +LINE 1: SELECT '{"a":null, "b":"qq"}'::agtype ?& '{""}'; + ^ +DETAIL: Expected ":", but found "}". +CONTEXT: agtype data, line 1: {""} +SELECT '{"a":null, "b":"qq"}'::agtype ?& '{null}'; +ERROR: invalid input syntax for type agtype +LINE 1: SELECT '{"a":null, "b":"qq"}'::agtype ?& '{null}'; + ^ +DETAIL: Expected string or "}", but found "null". +CONTEXT: agtype data, line 1: {null... +SELECT '{"a":null, "b":"qq"}'::agtype ?& '{"null"}'; +ERROR: invalid input syntax for type agtype +LINE 1: SELECT '{"a":null, "b":"qq"}'::agtype ?& '{"null"}'; + ^ +DETAIL: Expected ":", but found "}". +CONTEXT: agtype data, line 1: {"null"} +-- -- concat || operator -- SELECT i, pg_typeof(i) FROM (SELECT '[0, 1]'::agtype || '[0, 1]'::agtype as i) a; @@ -464,6 +1247,306 @@ SELECT * FROM cypher('jsonb_operators',$$CREATE ({list:['a', 'b', 'c'], json:{a: --- (0 rows) +/* + * ?, ?|, ?& key existence operators + */ +-- Exists (?) +-- should return true +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ? 'list' $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json ? 'a' $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.list ? 'c' $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ? 'a' $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.c ? 'd' $$) as (a agtype); + a +------ + true +(1 row) + +-- should return false +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ? 'a' $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json ? 'd' $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.list ? 'd' $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ? 'c' $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.c ? 'e' $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ? [] $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ? ['d'] $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ? {d: 'e'} $$) as (a agtype); + a +------- + false +(1 row) + +-- Exists (?|) +-- should return true +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| ['list'] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| ['list', 'd'] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| ['json', 'a'] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| ['list', 'json'] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ?| ['a'] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ?| ['a', 'b'] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.c ?| ['d'] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.c ?| ['d', 'e'] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| keys(n) $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json ?| keys(n.json) $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return [n.json ?| keys(n.json)] ?| [true] $$) as (a agtype); + a +------ + true +(1 row) + +-- should return false +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| [] $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| ['a', 'b'] $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ?| [] $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.c ?| ['c'] $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| [['list']] $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| keys(n.json) $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json ?| keys(n) $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return [n.json ?| keys(n.json)] ?| [false] $$) as (a agtype); + a +------- + false +(1 row) + +-- errors out +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| 'list' $$) as (a agtype); +ERROR: invalid agtype value for right operand +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| n $$) as (a agtype); +ERROR: invalid agtype value for right operand +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& 1 $$) as (a agtype); +ERROR: invalid agtype value for right operand +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '' $$) as (a agtype); +ERROR: invalid agtype value for right operand +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '1' $$) as (a agtype); +ERROR: invalid agtype value for right operand +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '{}' $$) as (a agtype); +ERROR: invalid agtype value for right operand +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& n.json $$) as (a agtype); +ERROR: invalid agtype value for right operand +-- Exists (?&) +-- should return true +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& ['list', 'json'] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ?& ['a', 'b'] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.c ?& ['d'] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& keys(n) $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json ?& keys(n.json) $$) as (a agtype); + a +------ + true +(1 row) + +-- should return false +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& [] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& ['a', 'b'] $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ?& [] $$) as (a agtype); + a +------ + true +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ?& ['a', 'b', 'c'] $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.c ?& ['d', 'e'] $$) as (a agtype); + a +------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& [['list']] $$) as (a agtype); + a +------- + false +(1 row) + +-- errors out +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& 'list' $$) as (a agtype); +ERROR: invalid agtype value for right operand +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& 1 $$) as (a agtype); +ERROR: invalid agtype value for right operand +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '' $$) as (a agtype); +ERROR: invalid agtype value for right operand +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '1' $$) as (a agtype); +ERROR: invalid agtype value for right operand +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& n $$) as (a agtype); +ERROR: invalid agtype value for right operand +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '{}' $$) as (a agtype); +ERROR: invalid agtype value for right operand +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& n.json $$) as (a agtype); +ERROR: invalid agtype value for right operand -- -- concat || operator -- diff --git a/regress/sql/agtype.sql b/regress/sql/agtype.sql index 0e53df2c..b8f08d9e 100644 --- a/regress/sql/agtype.sql +++ b/regress/sql/agtype.sql @@ -803,25 +803,6 @@ SELECT agtype_contains('{"id": 1}','{"id": 2}'); SELECT '{"id": 1}'::agtype @> '{"id": 1}'; SELECT '{"id": 1}'::agtype @> '{"id": 2}'; -SELECT agtype_exists('{"id": 1}','id'); -SELECT agtype_exists('{"id": 1}','not_id'); - -SELECT '{"id": 1}'::agtype ? 'id'; -SELECT '{"id": 1}'::agtype ? 'not_id'; - -SELECT agtype_exists_any('{"id": 1}', array['id']); -SELECT agtype_exists_any('{"id": 1}', array['not_id']); - -SELECT '{"id": 1}'::agtype ?| array['id']; -SELECT '{"id": 1}'::agtype ?| array['not_id']; - - -SELECT agtype_exists_all('{"id": 1}', array['id']); -SELECT agtype_exists_all('{"id": 1}', array['not_id']); - -SELECT '{"id": 1}'::agtype ?& array['id']; -SELECT '{"id": 1}'::agtype ?& array['not_id']; - -- -- Test STARTS WITH, ENDS WITH, and CONTAINS -- diff --git a/regress/sql/jsonb_operators.sql b/regress/sql/jsonb_operators.sql index 6fbb872a..689c148a 100644 --- a/regress/sql/jsonb_operators.sql +++ b/regress/sql/jsonb_operators.sql @@ -24,6 +24,170 @@ SET search_path TO ag_catalog; -- jsonb operators in AGE (?, ?&, ?|, ->, ->>, #>, #>>, ||) -- +-- +-- Agtype exists operator +-- + +-- exists (?) + +-- should return 't' +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"n"'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"a"'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"b"'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"d"'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ? '"label"'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ? '"n"'; +SELECT '["1","2"]'::agtype ? '"1"'; +SELECT '["hello", "world"]'::agtype ? '"hello"'; +SELECT agtype_exists('{"id": 1}','id'::text); +SELECT '{"id": 1}'::agtype ? 'id'::text; + +-- should return 'f' +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"e"'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"e1"'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '"1"'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '1'; +SELECT '[{"id": 281474976710658, "label": "", "properties": {"n": 100}}]'::agtype ? '"id"'; +SELECT '[{"id": 281474976710658, "label": "", "properties": {"n": 100}}]'::agtype ? 'null'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ? 'null'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ? 'null'; +SELECT '["hello", "world"]'::agtype ? '"hell"'; +SELECT agtype_exists('{"id": 1}','not_id'::text); +SELECT '{"id": 1}'::agtype ? 'not_id'::text; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '["e1", "n"]'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '["n"]'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '["n", "a", "e"]'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '{"n": null}'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '{"n": null, "b": true}'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? '["e1"]'; + +-- errors out +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? 'e1'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ? 'e'; + +-- Exists any (?|) + +-- should return 't' +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["a","b"]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["b","a"]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["c","a"]'; +SELECT '{"1":null, "b":"qq"}'::agtype ?| '["c","1"]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["a","a", "b", "b", "b"]'::agtype; +SELECT '[1,2,3]'::agtype ?| '[1,2,3,4]'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[null,"id"]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '["id",null]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[true,"id"]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[1,"id"]'::agtype; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[null,null,"n"]'::agtype; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '["n",null]'::agtype; +SELECT agtype_exists_any('{"id": 1}', array['id']); +SELECT '{"id": 1}'::agtype ?| array['id']; + +-- should return 'f' +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["c","d"]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["1","2"]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["c","1"]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '[]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '["c","d"]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[null]'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[]'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[null,null]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[null, "idk"]'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?| '[""]'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex' ?| '[null,"idk"]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?| '[null,null,"idk"]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?| '["idk",null]'::agtype; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[null]'; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[]'; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[null,null]'::agtype; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[[""]]'; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[null,null, "idk"]'::agtype; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '[null,null,"idk"]'::agtype; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?| '["start_idk",null]'::agtype; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '[["a"], ["b"]]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '[["a"], ["b"], ["c"]]'; +SELECT '[null]'::agtype ?| '[null]'::agtype; +SELECT agtype_exists_any('{"id": 1}', array['not_id']); +SELECT '{"id": 1}'::agtype ?| array['not_id']; + +-- errors out +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ?| '"b"'; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ?| '"d"'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '{"a", "b"}'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| ''; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '""'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '{""}'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '{}'; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '0'::agtype; +SELECT '{"a":null, "b":"qq"}'::agtype ?| '0'; + +-- Exists all (?&) + +-- should return 't' +SELECT '{"a":null, "b":"qq"}'::agtype ?& '["a","b"]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '["b","a"]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '["a","a", "b", "b", "b"]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[null]'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[]'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[null,null]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[null,null,"id"]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '["id",null]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '[null]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '[]'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '[null,null]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '[null,null,"n"]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '["n",null]'::agtype; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[null]'; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[]'; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[null,null]'::agtype; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[null,null,"n"]'::agtype; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '["n",null]'::agtype; +SELECT '[1,2,3]'::agtype ?& '[1,2,3]'; +SELECT '[1,2,3]'::agtype ?& '[1,2,3,null]'; +SELECT '[1,2,3]'::agtype ?& '[null, null]'; +SELECT '[1,2,3]'::agtype ?& '[null, null, null]'; +SELECT '[1,2,3]'::agtype ?& '[]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '[]'; +SELECT '[null]'::agtype ?& '[null]'::agtype; +SELECT agtype_exists_all('{"id": 1}', array['id']); +SELECT '{"id": 1}'::agtype ?& array['id']; + +-- should return 'f' +SELECT '{"a":null, "b":"qq"}'::agtype ?& '["c","a"]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '["a","b", "c"]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '["c","d"]'::agtype; +SELECT '[1,2,3]'::agtype ?& '[1,2,3,4]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '[["a"]]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '[["a"], ["b"]]'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '[["a"], ["b"], ["c"]]'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[null, "idk"]'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[""]'; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex' ?& '[null,"idk"]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '[null,null,"idk"]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}::vertex'::agtype ?& '["idk",null]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[1,"id"]'::agtype; +SELECT '{"id": 281474976710658, "label": "", "properties": {"n": 100}}'::agtype ?& '[true,"id"]'::agtype; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[null, "idk"]'; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[[""]]'; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[null,null, "idk"]'::agtype; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '[null,null,"idk"]'::agtype; +SELECT '{"id": 1688849860263937, "label": "EDGE", "end_id": 1970324836974593, "start_id": 1407374883553281, "properties": {"n": 100}}::edge'::agtype ?& '["start_idk",null]'::agtype; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '[null, "c", "a"]'; +SELECT agtype_exists_all('{"id": 1}', array['not_id']); +SELECT '{"id": 1}'::agtype ?& array['not_id']; + +-- errors out +SELECT '{"a":null, "b":"qq"}'::agtype ?& '"d"'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '"a"'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '" "'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '""'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '"null"'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '{"a", "b", "c"}'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '{}'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '{""}'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '{null}'; +SELECT '{"a":null, "b":"qq"}'::agtype ?& '{"null"}'; + -- -- concat || operator -- @@ -129,6 +293,90 @@ SELECT create_graph('jsonb_operators'); SELECT * FROM cypher('jsonb_operators',$$CREATE ({list:['a', 'b', 'c'], json:{a:1, b:['a', 'b'], c:{d:'a'}}})$$) as (a agtype); +/* + * ?, ?|, ?& key existence operators + */ + +-- Exists (?) + +-- should return true +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ? 'list' $$) 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.list ? 'c' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ? 'a' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.c ? 'd' $$) as (a agtype); + +-- should return false +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ? 'a' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json ? 'd' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.list ? 'd' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ? 'c' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.c ? 'e' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ? [] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ? ['d'] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ? {d: 'e'} $$) as (a agtype); + +-- Exists (?|) + +-- should return true +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| ['list'] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| ['list', 'd'] $$) 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 ?| ['list', 'json'] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ?| ['a'] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ?| ['a', 'b'] $$) 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', 'e'] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| keys(n) $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json ?| keys(n.json) $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return [n.json ?| keys(n.json)] ?| [true] $$) as (a agtype); + +-- should return false +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| [] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| ['a', 'b'] $$) 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.c ?| ['c'] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| [['list']] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| keys(n.json) $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json ?| keys(n) $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return [n.json ?| keys(n.json)] ?| [false] $$) as (a agtype); + +-- errors out +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| 'list' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?| n $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& 1 $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '1' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '{}' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& n.json $$) as (a agtype); + + +-- Exists (?&) + +-- should return true +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& ['list', 'json'] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.b ?& ['a', 'b'] $$) 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 ?& keys(n) $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json ?& keys(n.json) $$) as (a agtype); + +-- should return false +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& [] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& ['a', 'b'] $$) 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 ?& ['a', 'b', 'c'] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n.json.c ?& ['d', 'e'] $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& [['list']] $$) as (a agtype); + +-- errors out +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& 'list' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& 1 $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '1' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& n $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '{}' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& n.json $$) as (a agtype); + -- -- concat || operator -- diff --git a/src/backend/parser/ag_scanner.l b/src/backend/parser/ag_scanner.l index ffc6673c..74887ba4 100644 --- a/src/backend/parser/ag_scanner.l +++ b/src/backend/parser/ag_scanner.l @@ -227,15 +227,17 @@ param \${id} * These are tokens that are used as operators and language constructs in * Cypher, and some of them are structural characters in JSON. */ -concat "||" -lt_gt "<>" -lt_eq "<=" -gt_eq ">=" -dot_dot ".." -plus_eq "+=" -eq_tilde "=~" -typecast "::" -self [%()*+,\-./:;<=>[\]^{|}] +any_exists "?|" +all_exists "?&" +concat "||" +lt_gt "<>" +lt_eq "<=" +gt_eq ">=" +dot_dot ".." +plus_eq "+=" +eq_tilde "=~" +typecast "::" +self [?%()*+,\-./:;<=>[\]^{|}] other . @@ -651,6 +653,22 @@ ag_token token; return token; } +{any_exists} { + update_location(); + token.type = AG_TOKEN_ANY_EXISTS; + token.value.s = yytext; + token.location = get_location(); + return token; +} + +{all_exists} { + update_location(); + token.type = AG_TOKEN_ALL_EXISTS; + token.value.s = yytext; + token.location = get_location(); + return token; +} + {lt_gt} { update_location(); token.type = AG_TOKEN_LT_GT; diff --git a/src/backend/parser/cypher_gram.y b/src/backend/parser/cypher_gram.y index 130b56f9..884af455 100644 --- a/src/backend/parser/cypher_gram.y +++ b/src/backend/parser/cypher_gram.y @@ -76,6 +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 /* keywords in alphabetical order */ %token <keyword> ALL ANALYZE AND AS ASC ASCENDING @@ -169,6 +170,7 @@ %left XOR %right NOT %left '=' NOT_EQ '<' LT_EQ '>' GT_EQ +%left '|' '&' '?' ANY_EXISTS ALL_EXISTS %left '+' '-' CONCAT %left '*' '/' '%' %left '^' @@ -1324,6 +1326,18 @@ expr: { $$ = build_comparison_expression($1, $3, ">=", @2); } + | expr '?' expr %prec '.' + { + $$ = (Node *)makeSimpleA_Expr(AEXPR_OP, "?", $1, $3, @2); + } + | expr ANY_EXISTS expr + { + $$ = (Node *)makeSimpleA_Expr(AEXPR_OP, "?|", $1, $3, @2); + } + | expr ALL_EXISTS expr + { + $$ = (Node *)makeSimpleA_Expr(AEXPR_OP, "?&", $1, $3, @2); + } | expr CONCAT 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 407d6c07..f9dfae71 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, + ANY_EXISTS, + ALL_EXISTS, CONCAT }; @@ -99,6 +101,8 @@ int cypher_yylex(YYSTYPE *lvalp, YYLTYPE *llocp, ag_scanner_t scanner) case AG_TOKEN_DOT_DOT: case AG_TOKEN_PLUS_EQ: case AG_TOKEN_EQ_TILDE: + case AG_TOKEN_ALL_EXISTS: + case AG_TOKEN_ANY_EXISTS: case AG_TOKEN_CONCAT: break; case AG_TOKEN_TYPECAST: diff --git a/src/backend/utils/adt/agtype.c b/src/backend/utils/adt/agtype.c index 2d025a8f..2f93983e 100644 --- a/src/backend/utils/adt/agtype.c +++ b/src/backend/utils/adt/agtype.c @@ -175,9 +175,6 @@ static int64 get_int64_from_int_datums(Datum d, Oid type, char *funcname, static agtype_iterator *get_next_object_key(agtype_iterator *it, agtype_container *agtc, agtype_value *key); -static agtype_iterator *get_next_list_element(agtype_iterator *it, - agtype_container *agtc, - agtype_value *elem); static int extract_variadic_args_min(FunctionCallInfo fcinfo, int variadic_start, bool convert_unknown, Datum **args, Oid **types, bool **nulls, @@ -6411,7 +6408,7 @@ Datum age_tostringlist(PG_FUNCTION_ARGS) PG_RETURN_POINTER(agtype_value_to_agtype(agis_result.res)); } -static agtype_iterator *get_next_list_element(agtype_iterator *it, +agtype_iterator *get_next_list_element(agtype_iterator *it, agtype_container *agtc, agtype_value *elem) { agtype_iterator_token itok; @@ -10163,6 +10160,62 @@ agtype_value *get_agtype_value(char *funcname, agtype *agt_arg, return agtv_value; } +/* + * Returns properties of an entity (vertex or edge) or NULL if there are none. + * If the object passed is not a scalar, an error is thrown. + * If the object is a scalar and error_on_scalar is false, the scalar is + * returned, otherwise an error is thrown. + */ +agtype_value *extract_entity_properties(agtype *object, bool error_on_scalar) +{ + agtype_value *scalar_value = NULL; + agtype_value *return_value = NULL; + + if (!AGT_ROOT_IS_SCALAR(object)) + { + ereport(ERROR,(errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("expected a scalar value"))); + } + + /* unpack the scalar */ + scalar_value = get_ith_agtype_value_from_container(&object->root, 0); + + /* get the properties depending on the type or fail */ + if (scalar_value->type == AGTV_VERTEX) + { + return_value = &scalar_value->val.object.pairs[2].value; + } + else if (scalar_value->type == AGTV_EDGE) + { + return_value = &scalar_value->val.object.pairs[4].value; + } + else if (scalar_value->type == AGTV_PATH) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot extract properties from an agtype path"))); + } + else if (error_on_scalar) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("scalar object must be a vertex or edge"))); + } + else + { + return_value = scalar_value; + } + + /* if the properties are NULL, return NULL */ + if (return_value == NULL || return_value->type == AGTV_NULL) + { + return NULL; + } + + /* set the object_value to the property_value. */ + return return_value; +} + PG_FUNCTION_INFO_V1(age_eq_tilde); /* * Execution function for =~ aka regular expression comparisons diff --git a/src/backend/utils/adt/agtype_gin.c b/src/backend/utils/adt/agtype_gin.c index c0a2a028..1a1f267a 100644 --- a/src/backend/utils/adt/agtype_gin.c +++ b/src/backend/utils/adt/agtype_gin.c @@ -224,33 +224,42 @@ Datum gin_extract_agtype_query(PG_FUNCTION_ARGS) strategy == AGTYPE_EXISTS_ALL_STRATEGY_NUMBER) { /* Query is a text array; each element is treated as a key */ - ArrayType *query = PG_GETARG_ARRAYTYPE_P(0); - Datum *key_datums; - bool *key_nulls; - int key_count; - int i, j; - - deconstruct_array(query, TEXTOID, -1, false, 'i', - &key_datums, &key_nulls, &key_count); + agtype *agt = AG_GET_ARG_AGTYPE_P(0); + agtype_iterator *it = NULL; + agtype_value elem; + agtype_iterator_token itok; + int key_count = AGTYPE_CONTAINER_SIZE(&agt->root); + int index = 0; + + if (AGTYPE_CONTAINER_IS_SCALAR(&agt->root) || + !AGTYPE_CONTAINER_IS_ARRAY(&agt->root)) + { + elog(ERROR, "GIN query requires an agtype array"); + } entries = (Datum *) palloc(sizeof(Datum) * key_count); + it = agtype_iterator_init(&agt->root); - for (i = 0, j = 0; i < key_count; i++) + /* it should be WAGT_BEGIN_ARRAY */ + itok = agtype_iterator_next(&it, &elem, true); + Assert(itok == WAGT_BEGIN_ARRAY); + + while (WAGT_END_ARRAY != agtype_iterator_next(&it, &elem, true)) { - /* Nulls in the array are ignored */ - if (key_nulls[i]) + if (elem.type != AGTV_STRING) { - continue; + elog(ERROR, "unsupport agtype for GIN lookup: %d", elem.type); } - entries[j++] = make_text_key(AGT_GIN_FLAG_KEY, - VARDATA(key_datums[i]), - VARSIZE(key_datums[i]) - VARHDRSZ); + entries[index++] = make_text_key(AGT_GIN_FLAG_KEY, + elem.val.string.val, + elem.val.string.len); } - *nentries = j; + *nentries = index; + /* ExistsAll with no keys should match everything */ - if (j == 0 && strategy == AGTYPE_EXISTS_ALL_STRATEGY_NUMBER) + if (index == 0 && strategy == AGTYPE_EXISTS_ALL_STRATEGY_NUMBER) { *searchMode = GIN_SEARCH_MODE_ALL; } diff --git a/src/backend/utils/adt/agtype_ops.c b/src/backend/utils/adt/agtype_ops.c index 829483a4..238259af 100644 --- a/src/backend/utils/adt/agtype_ops.c +++ b/src/backend/utils/adt/agtype_ops.c @@ -996,6 +996,165 @@ Datum agtype_any_ge(PG_FUNCTION_ARGS) PG_RETURN_BOOL(result); } +PG_FUNCTION_INFO_V1(agtype_exists_agtype); +/* + * ? operator for agtype. Returns true if the string exists as top-level keys + */ +Datum agtype_exists_agtype(PG_FUNCTION_ARGS) +{ + agtype *agt = AG_GET_ARG_AGTYPE_P(0); + agtype *key = AG_GET_ARG_AGTYPE_P(1); + agtype_value *aval; + agtype_value *v = NULL; + + if (AGT_ROOT_IS_SCALAR(agt)) + { + agt = agtype_value_to_agtype(extract_entity_properties(agt, false)); + } + + if (AGT_ROOT_IS_SCALAR(key)) + { + aval = get_ith_agtype_value_from_container(&key->root, 0); + } + else + { + PG_RETURN_BOOL(false); + } + + if (AGT_ROOT_IS_OBJECT(agt) && + aval->type == AGTV_STRING) + { + v = find_agtype_value_from_container(&agt->root, + AGT_FOBJECT, + aval); + } + else if (AGT_ROOT_IS_ARRAY(agt) && + !aval->type == AGTV_NULL) + { + v = find_agtype_value_from_container(&agt->root, + AGT_FARRAY, + aval); + } + + PG_RETURN_BOOL(v != NULL); +} + +PG_FUNCTION_INFO_V1(agtype_exists_any_agtype); +/* + * ?| operator for agtype. Returns true if any of the array strings exist as + * top-level keys + */ +Datum agtype_exists_any_agtype(PG_FUNCTION_ARGS) +{ + agtype *agt = AG_GET_ARG_AGTYPE_P(0); + agtype *keys = AG_GET_ARG_AGTYPE_P(1); + agtype_value elem; + agtype_iterator *it = NULL; + + if (AGT_ROOT_IS_SCALAR(agt)) + { + agt = agtype_value_to_agtype(extract_entity_properties(agt, true)); + } + + if (!AGT_ROOT_IS_SCALAR(keys) && !AGT_ROOT_IS_OBJECT(keys)) + { + while ((it = get_next_list_element(it, &keys->root, &elem))) + { + if (IS_A_AGTYPE_SCALAR(&elem)) + { + if (AGT_ROOT_IS_OBJECT(agt) && + (&elem)->type == AGTV_STRING && + find_agtype_value_from_container(&agt->root, + AGT_FOBJECT, + &elem)) + { + PG_RETURN_BOOL(true); + } + else if (AGT_ROOT_IS_ARRAY(agt) && + !(&elem)->type == AGTV_NULL && + find_agtype_value_from_container(&agt->root, + AGT_FARRAY, + &elem)) + { + PG_RETURN_BOOL(true); + } + } + else + { + PG_RETURN_BOOL(false); + } + } + } + else + { + ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid agtype value for right operand"))); + } + + PG_RETURN_BOOL(false); +} + +PG_FUNCTION_INFO_V1(agtype_exists_all_agtype); +/* + * ?& operator for agtype. Returns true if all of the array strings exist as + * top-level keys + */ +Datum agtype_exists_all_agtype(PG_FUNCTION_ARGS) +{ + agtype *agt = AG_GET_ARG_AGTYPE_P(0); + agtype *keys = AG_GET_ARG_AGTYPE_P(1); + agtype_value elem; + agtype_iterator *it = NULL; + + if (AGT_ROOT_IS_SCALAR(agt)) + { + agt = agtype_value_to_agtype(extract_entity_properties(agt, true)); + } + + if (!AGT_ROOT_IS_SCALAR(keys) && !AGT_ROOT_IS_OBJECT(keys)) + { + while ((it = get_next_list_element(it, &keys->root, &elem))) + { + if (IS_A_AGTYPE_SCALAR(&elem)) + { + if ((&elem)->type == AGTV_NULL) + { + continue; + } + else if (AGT_ROOT_IS_OBJECT(agt) && + (&elem)->type == AGTV_STRING && + find_agtype_value_from_container(&agt->root, + AGT_FOBJECT, + &elem)) + { + continue; + } + else if (AGT_ROOT_IS_ARRAY(agt) && + find_agtype_value_from_container(&agt->root, + AGT_FARRAY, + &elem)) + { + continue; + } + else + { + PG_RETURN_BOOL(false); + } + } + else + { + PG_RETURN_BOOL(false); + } + } + } + else + { + ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid agtype value for right operand"))); + } + + PG_RETURN_BOOL(true); +} PG_FUNCTION_INFO_V1(agtype_contains); /* diff --git a/src/include/parser/ag_scanner.h b/src/include/parser/ag_scanner.h index 01087dc1..cb11b445 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_ANY_EXISTS, + AG_TOKEN_ALL_EXISTS, AG_TOKEN_CONCAT, AG_TOKEN_CHAR, } ag_token_type; diff --git a/src/include/utils/agtype.h b/src/include/utils/agtype.h index 7020ac8e..75712dd7 100644 --- a/src/include/utils/agtype.h +++ b/src/include/utils/agtype.h @@ -550,6 +550,10 @@ agtype_value *string_to_agtype_value(char *s); agtype_value *integer_to_agtype_value(int64 int_value); void add_agtype(Datum val, bool is_null, agtype_in_state *result, Oid val_type, bool key_scalar); +agtype_value *extract_entity_properties(agtype *object, bool error_on_scalar); +agtype_iterator *get_next_list_element(agtype_iterator *it, + agtype_container *agtc, + agtype_value *elem); void pfree_agtype_value(agtype_value* value); void pfree_agtype_value_content(agtype_value* value); void pfree_agtype_in_state(agtype_in_state* value);
