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 7b6c108faa353caaea2bc7a149e688c3bce5aa22 Author: Zainab Saad <[email protected]> AuthorDate: Tue Oct 3 23:30:59 2023 +0500 Extend access(->, ->>), addition and subtraction operators (#1258) Original work was done by Josh Innis for issue#282. The changes included in this PR are as follows: - Enable the use of access operator with agtype as the RHS operand, allowing this operator to be used within cypher queries - Extend addition operator to concatenate in case any or both operands are non-scalar or scalar vertex/edge/path - Extend subtract operator to delete specified keys from object or elements at specified indexes from array - Add and modify regression tests relevant to the above changes --- age--1.4.0.sql | 30 ++ regress/expected/agtype.out | 587 ++++++++++++++++++++++--- regress/expected/expr.out | 40 ++ regress/expected/jsonb_operators.out | 826 +++++++++++++++++++++++++++++++++++ regress/sql/agtype.sql | 132 +++++- regress/sql/expr.sql | 24 + regress/sql/jsonb_operators.sql | 237 ++++++++++ src/backend/parser/cypher_gram.y | 4 + src/backend/utils/adt/agtype.c | 210 ++++++--- src/backend/utils/adt/agtype_ops.c | 308 +++++++++++-- 10 files changed, 2230 insertions(+), 168 deletions(-) diff --git a/age--1.4.0.sql b/age--1.4.0.sql index c9e73455..47e8163c 100644 --- a/age--1.4.0.sql +++ b/age--1.4.0.sql @@ -2894,6 +2894,36 @@ CREATE OPERATOR ->> ( FUNCTION = ag_catalog.agtype_object_field_text ); +CREATE FUNCTION ag_catalog.agtype_object_field_agtype(agtype, agtype) +RETURNS agtype +LANGUAGE c +IMMUTABLE +RETURNS NULL ON NULL INPUT +PARALLEL SAFE +AS 'MODULE_PATHNAME'; + +-- get agtype object field or array element +CREATE OPERATOR -> ( + LEFTARG = agtype, + RIGHTARG = agtype, + FUNCTION = ag_catalog.agtype_object_field_agtype +); + +CREATE FUNCTION ag_catalog.agtype_object_field_text_agtype(agtype, agtype) +RETURNS text +LANGUAGE c +IMMUTABLE +RETURNS NULL ON NULL INPUT +PARALLEL SAFE +AS 'MODULE_PATHNAME'; + +-- get agtype object field or array element as text +CREATE OPERATOR ->> ( + LEFTARG = agtype, + RIGHTARG = agtype, + FUNCTION = ag_catalog.agtype_object_field_text_agtype +); + CREATE FUNCTION ag_catalog.agtype_array_element(agtype, int4) RETURNS agtype LANGUAGE c diff --git a/regress/expected/agtype.out b/regress/expected/agtype.out index c418b69d..9ee0073a 100644 --- a/regress/expected/agtype.out +++ b/regress/expected/agtype.out @@ -214,6 +214,18 @@ SELECT agtype_sub('1::numeric', '-1.0::numeric'); 2.0::numeric (1 row) +SELECT agtype_sub('[1, 2, 3]', '1'); + agtype_sub +------------ + [1, 3] +(1 row) + +SELECT agtype_sub('{"a": 1, "b": 2, "c": 3}', '"a"'); + agtype_sub +------------------ + {"b": 2, "c": 3} +(1 row) + SELECT agtype_neg('-1'); agtype_neg ------------ @@ -692,6 +704,521 @@ SELECT '3.14::numeric'::agtype + '3.14::numeric'::agtype; 6.28::numeric (1 row) +-- +-- Test operator - for extended functionality +-- +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '"a"'; + ?column? +------------------ + {"b": 2, "c": 3} +(1 row) + +SELECT '{"a":null , "b":2, "c":3}'::agtype - '"a"'; + ?column? +------------------ + {"b": 2, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '"b"'; + ?column? +------------------ + {"a": 1, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '"c"'; + ?column? +------------------ + {"a": 1, "b": 2} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '"d"'; + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '""'; + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '"1"'; + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3, "1": 4}'::agtype - '"1"'; + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - age_tostring('a'); + ?column? +------------------ + {"b": 2, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - age_tostring(1); + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3, "1": 4}'::agtype - age_tostring(1); + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{}'::agtype - '"a"'; + ?column? +---------- + {} +(1 row) + +SELECT '["a","b","c"]'::agtype - 3; + ?column? +----------------- + ["a", "b", "c"] +(1 row) + +SELECT '["a","b","c"]'::agtype - 2; + ?column? +------------ + ["a", "b"] +(1 row) + +SELECT '["a","b","c"]'::agtype - 1; + ?column? +------------ + ["a", "c"] +(1 row) + +SELECT '["a","b","c"]'::agtype - 0; + ?column? +------------ + ["b", "c"] +(1 row) + +SELECT '["a","b","c"]'::agtype - -1; + ?column? +------------ + ["a", "b"] +(1 row) + +SELECT '["a","b","c"]'::agtype - -2; + ?column? +------------ + ["a", "c"] +(1 row) + +SELECT '["a","b","c"]'::agtype - -3; + ?column? +------------ + ["b", "c"] +(1 row) + +SELECT '["a","b","c"]'::agtype - -4; + ?column? +----------------- + ["a", "b", "c"] +(1 row) + +SELECT '["a","b","c"]'::agtype - '2'; + ?column? +------------ + ["a", "b"] +(1 row) + +SELECT '["a","b","c"]'::agtype - -(true::int); + ?column? +------------ + ["a", "b"] +(1 row) + +SELECT '[]'::agtype - 1; + ?column? +---------- + [] +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '["b"]'::agtype; + ?column? +------------------ + {"a": 1, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '["c","b"]'::agtype; + ?column? +---------- + {"a": 1} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '[]'::agtype; + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '["a","b","c"]'::agtype - '[]'; + ?column? +----------------- + ["a", "b", "c"] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[1]'; + ?column? +------------ + ["a", "c"] +(1 row) + +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[9]'; + ?column? +-------------------- + [1, 2, 3, 4, 5, 6] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[1, -1]'; + ?column? +---------- + ["a"] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[1, -1, 3, 4]'; + ?column? +---------- + ["a"] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[1, -1, 3, 4, 0]'; + ?column? +---------- + [] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[-1, 1, 3, 4, 1]'; + ?column? +---------- + ["a"] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[-1, 1, 3, 4, 0]'; + ?column? +---------- + [] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[1, 1]'; + ?column? +------------ + ["a", "c"] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[1, 1, 1]'; + ?column? +------------ + ["a", "c"] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[1, 1, -1]'; + ?column? +---------- + ["a"] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[1, 1, -1, -1]'; + ?column? +---------- + ["a"] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[-2, -4, -5, -1]'; + ?column? +---------- + ["a"] +(1 row) + +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[0, 4, 3, 2]'; + ?column? +---------- + [2, 6] +(1 row) + +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[0, 4, 3, 2, -1]'; + ?column? +---------- + [2] +(1 row) + +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[3, 3, 4, 4, 6, 8, 9]'; + ?column? +-------------- + [1, 2, 3, 6] +(1 row) + +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[8, 9, -7, -6]'; + ?column? +----------------- + [2, 3, 4, 5, 6] +(1 row) + +-- multiple sub operations +SELECT '{"a":1 , "b":2, "c":3, "1": 4}'::agtype - age_tostring(1) - age_tostring(1); + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3, "1": 4}'::agtype - age_tostring(1) - age_tostring('a'); + ?column? +------------------ + {"b": 2, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3, "1": 4}'::agtype - age_tostring(1) - age_tostring('a') - age_tostring('e') - age_tostring('c'); + ?column? +---------- + {"b": 2} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '["c","b"]' - '["a"]'; + ?column? +---------- + {} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '["c","b"]' - '["e"]' - '["a"]'; + ?column? +---------- + {} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '["c","b"]' - '[]'; + ?column? +---------- + {"a": 1} +(1 row) + +SELECT '["a","b","c"]'::agtype - '[-1]' - '[-1]'; + ?column? +---------- + ["a"] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[-1]' - '[-2]' - '[-2]'; + ?column? +---------- + ["b"] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[-1]' - '[]' - '[-2]'; + ?column? +---------- + ["b"] +(1 row) + +SELECT '["a","b","c"]'::agtype - '[-1]' - '[4]' - '[-2]'; + ?column? +---------- + ["b"] +(1 row) + +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[8, 9, -7, -6]' - '1'; + ?column? +-------------- + [2, 4, 5, 6] +(1 row) + +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[8, 9, -7, -6]' - '[1, 0]'; + ?column? +----------- + [4, 5, 6] +(1 row) + +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[8, 9, -7, -6]' - 3 - '[]'; + ?column? +-------------- + [2, 3, 4, 6] +(1 row) + +-- errors out +SELECT '["a","b","c"]'::agtype - '["1"]'; +ERROR: expected agtype integer, not agtype string +SELECT '["a","b","c"]'::agtype - '[null]'; +ERROR: expected agtype integer, not agtype NULL +SELECT '["a","b","c"]'::agtype - '"1"'; +ERROR: expected agtype integer, not agtype string +SELECT '["a","b","c"]'::agtype - 'null'; +ERROR: expected agtype integer, not agtype NULL +SELECT '["a","b","c"]'::agtype - '[-1]' - '["-2"]' - '[-2]'; +ERROR: expected agtype integer, not agtype string +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '[1]'; +ERROR: expected agtype string, not agtype integer +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '[null]'; +ERROR: expected agtype string, not agtype NULL +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '1'; +ERROR: expected agtype string, not agtype integer +SELECT '{"a":1 , "b":2, "c":3}'::agtype - 'null'; +ERROR: expected agtype string, not agtype NULL +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '["c","b"]' - '[1]' - '["a"]'; +ERROR: expected agtype string, not agtype integer +SELECT 'null'::agtype - '1'; +ERROR: Invalid input parameter types for agtype_sub +SELECT 'null'::agtype - '[1]'; +ERROR: must be object or array, not a scalar value +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype - '"a"'; +ERROR: Invalid input parameter types for agtype_sub +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype - '["a"]'; +ERROR: must be object or array, not a scalar value +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype - '[1]'; +ERROR: must be object or array, not a scalar value +SELECT '{"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge'::agtype - '{"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge'::agtype; +ERROR: Invalid input parameter types for agtype_sub +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype - '[]'; +ERROR: must be object or array, not a scalar value +-- +-- Test operator + for extended functionality +-- +SELECT '[1, 2, 3]'::agtype + '[4, 5]'::agtype; + ?column? +----------------- + [1, 2, 3, 4, 5] +(1 row) + +SELECT '[1, 2, true, "string", 1.4::numeric]'::agtype + '[4.5, -5::numeric, {"a": true}]'::agtype; + ?column? +--------------------------------------------------------------------- + [1, 2, true, "string", 1.4::numeric, 4.5, -5::numeric, {"a": true}] +(1 row) + +SELECT '[{"a":1 , "b":2, "c":3}]'::agtype + '[]'; + ?column? +---------------------------- + [{"a": 1, "b": 2, "c": 3}] +(1 row) + +SELECT '[{"a":1 , "b":2, "c":3}]'::agtype + '[{"d": 4}]'; + ?column? +-------------------------------------- + [{"a": 1, "b": 2, "c": 3}, {"d": 4}] +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype + '["b", 2, {"d": 4}]'::agtype; + ?column? +---------------------------------------------- + [{"a": 1, "b": 2, "c": 3}, "b", 2, {"d": 4}] +(1 row) + +SELECT '["b", 2, {"d": 4}]'::agtype + '{"a":1 , "b":2, "c":3}'::agtype; + ?column? +---------------------------------------------- + ["b", 2, {"d": 4}, {"a": 1, "b": 2, "c": 3}] +(1 row) + +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype + '[1]'; + ?column? +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + [{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex, 1] +(1 row) + +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex'::agtype + '[1, "e", true]'; + ?column? +-------------------------------------------------------------------------------------------------------------------------- + [{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex, 1, "e", true] +(1 row) + +SELECT '[]'::agtype + '{}'; + ?column? +---------- + [{}] +(1 row) + +SELECT '[]'::agtype + '{"a": 1}'; + ?column? +------------ + [{"a": 1}] +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype + '{"b": 2}'; + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype + '{"": 2}'; + ?column? +--------------------------------- + {"": 2, "a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype + '{"a":1 , "b":2, "c":3}'; + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{"a":1 , "b":2, "c":3}'::agtype + '{}'; + ?column? +-------------------------- + {"a": 1, "b": 2, "c": 3} +(1 row) + +SELECT '{}'::agtype + '{}'; + ?column? +---------- + {} +(1 row) + +SELECT '1'::agtype + '[{"a":1 , "b":2, "c":3}]'::agtype; + ?column? +------------------------------- + [1, {"a": 1, "b": 2, "c": 3}] +(1 row) + +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex'::agtype + '{"d": 4}'; + ?column? +---------------------------------------------------------------------------------------------------------------------- + [{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex, {"d": 4}] +(1 row) + +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex'::agtype + '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex'::agtype; + ?column? +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + [{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex, {"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex] +(1 row) + +SELECT '[{"id": 1688849860263938, "label": "v2", "properties": {"id": "middle"}}::vertex, {"id": 1970324836974594, "label": "e2", "end_id": 1688849860263937, "start_id": 1688849860263938, "properties": {}}::edge, {"id": 1688849860263937, "label": "v2", "properties": {"id": "initial"}}::vertex]::path'::agtype + ' [{"id": 1688849860263938, "label": "v2", "properties": {"id": "middle"}}::vertex, {"id": 1970324836974594, "label": "e2", "end_id": 1688849860263937, "start_id": 1688849860263938 [...] + ?column? [...] +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ [...] + [[{"id": 1688849860263938, "label": "v2", "properties": {"id": "middle"}}::vertex, {"id": 1970324836974594, "label": "e2", "end_id": 1688849860263937, "start_id": 1688849860263938, "properties": {}}::edge, {"id": 1688849860263937, "label": "v2", "properties": {"id": "initial"}}::vertex]::path, [{"id": 1688849860263938, "label": "v2", "properties": {"id": "middle"}}::vertex, {"id": 1970324836974594, "label": "e2", "end_id": 1688849860263937, "start_id": 1688849860263938, "properties": {} [...] +(1 row) + +SELECT '[{"id": 1688849860263938, "label": "v2", "properties": {"id": "middle"}}::vertex, {"id": 1970324836974594, "label": "e2", "end_id": 1688849860263937, "start_id": 1688849860263938, "properties": {}}::edge, {"id": 1688849860263937, "label": "v2", "properties": {"id": "initial"}}::vertex]::path'::agtype + '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}} [...] + ?column? +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + [[{"id": 1688849860263938, "label": "v2", "properties": {"id": "middle"}}::vertex, {"id": 1970324836974594, "label": "e2", "end_id": 1688849860263937, "start_id": 1688849860263938, "properties": {}}::edge, {"id": 1688849860263937, "label": "v2", "properties": {"id": "initial"}}::vertex]::path, {"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex] +(1 row) + +SELECT '{"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge'::agtype + '{"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge'::agtype; + ?column? +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + [{"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge, {"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge] +(1 row) + +SELECT '{"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge'::agtype + '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex'::agtype; + ?column? +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + [{"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge, {"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex] +(1 row) + +-- errors out +SELECT '1'::agtype + '{"a":1 , "b":2, "c":3}'::agtype; +ERROR: invalid left operand for agtype concatenation +SELECT '{"a":1 , "b":2, "c":3}'::agtype + '"string"'; +ERROR: invalid right operand for agtype concatenation -- -- Test overloaded agytype any operators +, -, *, /, % -- @@ -2351,66 +2878,6 @@ SELECT agtype_access_operator('{"bool":false, "int":3, "float":3.14}', '2'); ERROR: AGTV_INTEGER is not a valid key type SELECT agtype_access_operator('{"bool":false, "int":3, "float":3.14}', '2.0'); ERROR: AGTV_FLOAT is not a valid key type -SELECT i, pg_typeof(i) FROM (SELECT '{"bool":true, "array":[1,3,{"bool":false, "int":3, "float":3.14},7], "float":3.14}'::agtype->'array'->2->'float' as i) a; - i | pg_typeof -------+----------- - 3.14 | agtype -(1 row) - -SELECT i, pg_typeof(i) FROM (SELECT '{"bool":true, "array":[1,3,{"bool":false, "int":3, "float":3.14},7], "float":3.14}'::agtype->'array'->2->>'float' as i) a; - i | pg_typeof -------+----------- - 3.14 | text -(1 row) - -SELECT i, pg_typeof(i) FROM (SELECT '{}'::agtype->'array' as i) a; - i | pg_typeof ----+----------- - | agtype -(1 row) - -SELECT i, pg_typeof(i) FROM (SELECT '[]'::agtype->0 as i) a; - i | pg_typeof ----+----------- - | agtype -(1 row) - -SELECT i, pg_typeof(i) FROM (SELECT '[0, 1]'::agtype->2 as i) a; - i | pg_typeof ----+----------- - | agtype -(1 row) - -SELECT i, pg_typeof(i) FROM (SELECT '[0, 1]'::agtype->3 as i) a; - i | pg_typeof ----+----------- - | agtype -(1 row) - -SELECT i, pg_typeof(i) FROM (SELECT '{"bool":false, "int":3, "float":3.14}'::agtype->'true' as i) a; - i | pg_typeof ----+----------- - | agtype -(1 row) - -SELECT i, pg_typeof(i) FROM (SELECT '{"bool":false, "int":3, "float":3.14}'::agtype->2 as i) a; - i | pg_typeof ----+----------- - | agtype -(1 row) - -SELECT i, pg_typeof(i) FROM (SELECT '{"bool":false, "int":3, "float":3.14}'::agtype->'true'->2 as i) a; - i | pg_typeof ----+----------- - | agtype -(1 row) - -SELECT i, pg_typeof(i) FROM (SELECT '{"bool":false, "int":3, "float":3.14}'::agtype->'true'->>2 as i) a; - i | pg_typeof ----+----------- - | text -(1 row) - -- Test duplicate keys and null value -- expected: only the latest key, among duplicates, will be kept; and null will be removed SELECT * FROM create_graph('agtype_null_duplicate_test'); diff --git a/regress/expected/expr.out b/regress/expected/expr.out index 01424de6..63d7a482 100644 --- a/regress/expected/expr.out +++ b/regress/expected/expr.out @@ -1069,6 +1069,46 @@ $$) AS r(result agtype); 3.14 (1 row) +SELECT * +FROM cypher('expr', $$ + WITH {bool_val: false, int_val: 3, float_val: 3.14, array: [1, 2, 3]} as a + RETURN a - 'array' +$$) as (a agtype); + a +------------------------------------------------------ + {"int_val": 3, "bool_val": false, "float_val": 3.14} +(1 row) + +SELECT * +FROM cypher('expr', $$ + WITH {bool_val: false, int_val: 3, float_val: 3.14, array: [1, 2, 3]} as a + RETURN a - 'int_val' +$$) as (a agtype); + a +------------------------------------------------------------ + {"array": [1, 2, 3], "bool_val": false, "float_val": 3.14} +(1 row) + +SELECT * +FROM cypher('expr', $$ + WITH {bool_val: false, int_val: 3, float_val: 3.14, array: [1, 2, 3]} as a + RETURN a.array - 1 +$$) as (a agtype); + a +-------- + [1, 3] +(1 row) + +SELECT * +FROM cypher('expr', $$ + WITH {bool_val: false, int_val: 3, float_val: 3.14, array: [1, 2, 3]} as a + RETURN a.array + 1 +$$) as (a agtype); + a +-------------- + [1, 2, 3, 1] +(1 row) + -- -- Test STARTS WITH, ENDS WITH, and CONTAINS transform logic -- diff --git a/regress/expected/jsonb_operators.out b/regress/expected/jsonb_operators.out index a665bd66..3657d95f 100644 --- a/regress/expected/jsonb_operators.out +++ b/regress/expected/jsonb_operators.out @@ -805,6 +805,587 @@ LINE 1: SELECT '{"a":null, "b":"qq"}'::agtype ?& '{"null"}'; DETAIL: Expected ":", but found "}". CONTEXT: agtype data, line 1: {"null"} -- +-- Agtype access operators (->, ->>) +-- +SELECT i, pg_typeof(i) FROM (SELECT '{"bool":true, "array":[1,3,{"bool":false, "int":3, "float":3.14},7], "float":3.14}'::agtype->'array'::text->2->'float'::text as i) a; + i | pg_typeof +------+----------- + 3.14 | agtype +(1 row) + +SELECT i, pg_typeof(i) FROM (SELECT '{"bool":true, "array":[1,3,{"bool":false, "int":3, "float":3.14},7], "float":3.14}'::agtype->'array'::text->2->>'float'::text as i) a; + i | pg_typeof +------+----------- + 3.14 | text +(1 row) + +SELECT i, pg_typeof(i) FROM (SELECT '[0, 1]'::agtype->0 as i) a; + i | pg_typeof +---+----------- + 0 | agtype +(1 row) + +SELECT i, pg_typeof(i) FROM (SELECT '[0, 1]'::agtype->>0 as i) a; + i | pg_typeof +---+----------- + 0 | text +(1 row) + +/* + * access agtype object field or array element (->) + */ +-- LHS is an object +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"n"'::agtype; + ?column? +---------- + null +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"a"'::agtype; + ?column? +---------- + 1 +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"b"'::agtype; + ?column? +---------- + [1, 2] +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"c"'::agtype; + ?column? +---------- + {"1": 2} +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"d"'::agtype; + ?column? +--------------- + {"1": [2, 3]} +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"d"'::agtype -> '"1"'::agtype; + ?column? +---------- + [2, 3] +(1 row) + +SELECT '{"a": [-1, -2, -3]}'::agtype -> 'a'::text; + ?column? +-------------- + [-1, -2, -3] +(1 row) + +SELECT '{"a": 9, "b": 11, "c": {"ca": [[], {}, null]}, "d": true, "1": false}'::agtype -> '"1"'::text::agtype; + ?column? +---------- + false +(1 row) + +SELECT '{"a": true, "b": null, "c": -1.99, "d": {"e": []}, "1": [{}, [[[]]]], " ": [{}]}'::agtype -> ' '::text; + ?column? +---------- + [{}] +(1 row) + +SELECT '{"a": true, "b": null, "c": -1.99, "d": {"e": []}, "1": [{}, [[[]]]], " ": [{}]}'::agtype -> '" "'::agtype; + ?column? +---------- + [{}] +(1 row) + +-- should return null +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"e"'::agtype; + ?column? +---------- + +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}, "1": -19}'::agtype -> '1'::agtype; + ?column? +---------- + +(1 row) + +SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype -> '""'::agtype; + ?column? +---------- + +(1 row) + +SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype -> null::text; + ?column? +---------- + +(1 row) + +SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype -> null::int; + ?column? +---------- + +(1 row) + +SELECT '{"a": [-1, -2, -3]}'::agtype -> '"a"'::text; + ?column? +---------- + +(1 row) + +SELECT '{"a": 9, "b": 11, "c": {"ca": [[], {}, null]}, "d": true, "1": false}'::agtype -> '1'::text::agtype; + ?column? +---------- + +(1 row) + +-- LHS is an array +SELECT '["a","b","c",[1,2],null]'::agtype -> '0'::agtype; + ?column? +---------- + "a" +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype -> 1; + ?column? +---------- + "b" +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype -> '2'::agtype; + ?column? +---------- + "c" +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype -> 3; + ?column? +---------- + [1, 2] +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype -> '3'::agtype -> '1'::agtype; + ?column? +---------- + 2 +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype -> '3'::agtype -> '-1'::agtype; + ?column? +---------- + 2 +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype -> '3'::agtype -> -1; + ?column? +---------- + 2 +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype -> 4; + ?column? +---------- + null +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype -> '-1'::agtype; + ?column? +---------- + null +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype -> '-5'::agtype; + ?column? +---------- + "a" +(1 row) + +SELECT '[1, 2, 3]'::agtype -> '1'::text::agtype; + ?column? +---------- + 2 +(1 row) + +SELECT '[1, 2, 3]'::agtype -> '1'::text::agtype::int; + ?column? +---------- + 2 +(1 row) + +SELECT '[1, 2, 3]'::agtype -> '1'::text::agtype::int::bool::int; + ?column? +---------- + 2 +(1 row) + +SELECT '[1, 2, 3]'::agtype -> '1'::text::int; + ?column? +---------- + 2 +(1 row) + +SELECT '[1, "e", {"a": 1}, {}, [{}, {}, -9]]'::agtype -> true::int; + ?column? +---------- + "e" +(1 row) + +SELECT '[1, "e", {"a": 1}, {}, [{}, {}, -9]]'::agtype -> 1.9::int; + ?column? +---------- + {"a": 1} +(1 row) + +SELECT '[1, "e", {"a": 1}, {}, [{}, {}, -9]]'::agtype -> 1.1::int; + ?column? +---------- + "e" +(1 row) + +SELECT 'true'::agtype -> 0.1::int; + ?column? +---------- + true +(1 row) + +SELECT 'true'::agtype -> 0; + ?column? +---------- + true +(1 row) + +SELECT 'true'::agtype -> false::int; + ?column? +---------- + true +(1 row) + +SELECT 'true'::agtype -> 0.1::int::bool::int; + ?column? +---------- + true +(1 row) + +SELECT '[1, 9]'::agtype -> -1.2::int; + ?column? +---------- + 9 +(1 row) + +SELECT 'true'::agtype -> 0.1::bigint::int; + ?column? +---------- + true +(1 row) + +-- should return null +SELECT '["a","b","c",[1,2],null]'::agtype -> '5'::agtype; + ?column? +---------- + +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype -> '-6'::agtype; + ?column? +---------- + +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype -> '"a"'::agtype; + ?column? +---------- + +(1 row) + +SELECT '"foo"'::agtype -> '1'::agtype; + ?column? +---------- + +(1 row) + +SELECT '[1, 2, 3, "string", {"a": 1}, {"a": []}]'::agtype -> '5'::text; + ?column? +---------- + +(1 row) + +SELECT '[1, 2, 3]'::agtype -> '[1]'; + ?column? +---------- + +(1 row) + +SELECT '[1, "e", {"a": 1}, {}, [{}, {}, -9]]'::agtype -> '{"a": 1}'::agtype; + ?column? +---------- + +(1 row) + +SELECT 'true'::agtype -> 0::text; + ?column? +---------- + +(1 row) + +SELECT 'true'::agtype -> false::int::text; + ?column? +---------- + +(1 row) + +-- LHS is vertex/edge/path +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype -> '"a"'; + ?column? +---------- + "xyz" +(1 row) + +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype -> '"e"' -> 'g'::text; + ?column? +---------- + {} +(1 row) + +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype -> '"e"' -> 'h'::text -> -1; + ?column? +---------- + {} +(1 row) + +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype -> '"e"' -> 'h'::text -> -1 -> 0; + ?column? +---------- + +(1 row) + +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype -> '"id"'; + ?column? +---------- + +(1 row) + +-- access array element nested inside object or object field nested inside array on LHS +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"d"'::agtype -> '"1"'::agtype -> '1'::agtype; + ?column? +---------- + 3 +(1 row) + +SELECT '{"a": 9, "b": 11, "c": {"ca": [[], {}, null]}, "d": true}'::agtype -> 'c'::text -> '"ca"'::agtype -> 0; + ?column? +---------- + [] +(1 row) + +SELECT '{"a":[1, 2, 3], "b": {"c": ["cc", "cd"]}}'::agtype -> '"b"'::agtype -> 'c'::text -> 1 -> 0; + ?column? +---------- + "cd" +(1 row) + +SELECT '{"a":[1, 2, 3], "b": {"c": 1}}'::agtype -> '"b"'::agtype -> 1; + ?column? +---------- + +(1 row) + +/* + * access agtype object field or array element as text (->>) + */ +-- LHS is an object +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ->> '"a"'::agtype; + ?column? +---------- + 1 +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ->> '"d"'::agtype; + ?column? +--------------- + {"1": [2, 3]} +(1 row) + +SELECT '{"1": -1.99, "a": 1, "b": 2, "c": {"d": [{}, [[[], [9]]]]}, "1": true}'::agtype ->> '"1"'; + ?column? +---------- + true +(1 row) + +SELECT '{"1": -1.99, "a": 1, "b": 2, "c": {"d": [{}, [[[], [9]]]]}, "1": true}'::agtype ->> '1'::text; + ?column? +---------- + true +(1 row) + +-- should return null +SELECT '{"1": -1.99, "a": 1, "b": 2, "c": {"d": [{}, [[[], [9]]]]}, "1": true}'::agtype ->> 1; + ?column? +---------- + +(1 row) + +SELECT '{"1": -1.99, "a": 1, "b": 2, "c": {"d": [{}, [[[], [9]]]]}, "1": true}'::agtype ->> '1'; + ?column? +---------- + +(1 row) + +SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype ->> null::text; + ?column? +---------- + +(1 row) + +SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype ->> null::int; + ?column? +---------- + +(1 row) + +-- LHS is an array +SELECT '["a","b","c",[1,2],null]'::agtype ->> 0; + ?column? +---------- + a +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype ->> '1'::agtype; + ?column? +---------- + b +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype ->> '2'::int; + ?column? +---------- + c +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype ->> 3::int; + ?column? +---------- + [1, 2] +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype ->> '4'::agtype; + ?column? +---------- + +(1 row) + +SELECT '[1, 2, "e", true, null, {"a": true, "b": {}, "c": [{}, [[], {}]]}, null]'::agtype ->> 0.1::float::bigint::int; + ?column? +---------- + 1 +(1 row) + +SELECT '"foo"'::agtype ->> '0'::agtype; + ?column? +---------- + foo +(1 row) + +SELECT '[false, {}]'::agtype ->> 1::bool::int::bigint::int; + ?column? +---------- + {} +(1 row) + +-- should return null +SELECT '["a","b","c",[1,2],null]'::agtype ->> '-1'::agtype; + ?column? +---------- + +(1 row) + +SELECT '[1, 2, "e", true, null, {"a": true, "b": {}, "c": [{}, [[], {}]]}, null]'::agtype ->> 2::text; + ?column? +---------- + +(1 row) + +SELECT '[1, 2, "e", true, null, {"a": true, "b": {}, "c": [{}, [[], {}]]}, null]'::agtype ->> '2'::text; + ?column? +---------- + +(1 row) + +SELECT '[1, 2, "e", true, null, {"a": true, "b": {}, "c": [{}, [[], {}]]}, null]'::agtype ->> 'null'::text; + ?column? +---------- + +(1 row) + +SELECT '"foo"'::agtype ->> '1'::agtype; + ?column? +---------- + +(1 row) + +-- LHS is vertex/edge/path +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype ->> '"a"'; + ?column? +---------- + xyz +(1 row) + +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype ->> '"id"'; + ?column? +---------- + +(1 row) + +-- using -> and ->> in single query +SELECT '{"1": -1.99, "a": 1, "b": 2, "c": {"d": [{}, [[[], [9]]]]}}'::agtype -> '"1"' ->> '0'; + ?column? +---------- + -1.99 +(1 row) + +SELECT '["a","b","c",[1,2],null]'::agtype -> '3'::agtype ->> '1'::agtype; + ?column? +---------- + 2 +(1 row) + +SELECT ('["a","b","c",[1,2],null]'::agtype -> '3'::agtype) ->> 0; + ?column? +---------- + 1 +(1 row) + +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"d"'::agtype ->> '1'::agtype; + ?column? +---------- + +(1 row) + +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype -> '"e"' -> 'h'::text -> -1 ->> 0; + ?column? +---------- + +(1 row) + +-- should give error +SELECT '["a","b","c",[1,2],null]'::agtype ->> '3'::agtype ->> '1'::agtype; +ERROR: operator does not exist: text ->> agtype +LINE 1: ...["a","b","c",[1,2],null]'::agtype ->> '3'::agtype ->> '1'::a... + ^ +HINT: No operator matches the given name and argument types. You might need to add explicit type casts. +SELECT '["a","b","c",[1,2],null]'::agtype ->> '3'::agtype -> '1'::agtype; +ERROR: operator does not exist: text -> agtype +LINE 1: ...["a","b","c",[1,2],null]'::agtype ->> '3'::agtype -> '1'::ag... + ^ +HINT: No operator matches the given name and argument types. You might need to add explicit type casts. +SELECT '[1, 2, "e", true, null, {"a": true, "b": {}, "c": [{}, [[], {}]]}, null]'::agtype ->> 0.1::float::bigint; +ERROR: operator does not exist: agtype ->> bigint +LINE 1: ...ue, "b": {}, "c": [{}, [[], {}]]}, null]'::agtype ->> 0.1::f... + ^ +HINT: No operator matches the given name and argument types. You might need to add explicit type casts. +SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype ->> "'z'"::agtype; +ERROR: column "'z'" does not exist +LINE 1: ...CT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype ->> "'z'"::agt... + ^ +-- -- Agtype path extraction operators (#>, #>>) -- /* @@ -2045,6 +2626,251 @@ SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& '{}' $$) as (a ag 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 +-- +-- agtype access operator (->) +-- +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-1 +$$) AS r(result agtype); + result +-------- + 9 +(1 row) + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->1 +$$) AS r(result agtype); + result +-------------------------------------------------------------------------------- + {"int": 3, "bool": true, "array": [9, 11, {"bool": false, "float": 3.14}, 13]} +(1 row) + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-4 +$$) AS r(result agtype); + result +-------------------------------------------------------------------------------- + {"int": 3, "bool": true, "array": [9, 11, {"bool": false, "float": 3.14}, 13]} +(1 row) + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-4->'array' +$$) AS r(result agtype); + result +--------------------------------------------- + [9, 11, {"bool": false, "float": 3.14}, 13] +(1 row) + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-4->'array'->-2->'bool' +$$) AS r(result agtype); + result +-------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-4->'array'->-2->'bool'->-1 +$$) AS r(result agtype); + result +-------- + false +(1 row) + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->(size(lst)-1) +$$) AS r(result agtype); + result +-------- + 9 +(1 row) + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->(size(lst)%size(lst)) +$$) AS r(result agtype); + result +-------- + 1 +(1 row) + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-4->'array'->-2->'float' +$$) AS r(result agtype); + result +-------- + 3.14 +(1 row) + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-4->'array'->-2->'float'->0 +$$) AS r(result agtype); + result +-------- + 3.14 +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json' $$) as (a agtype); + a +-------------------------------------------- + {"a": 1, "b": ["a", "b"], "c": {"d": "a"}} +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'a' $$) as (a agtype); + a +--- + 1 +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'a'->-1 $$) as (a agtype); + a +--- + 1 +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'b' $$) as (a agtype); + a +------------ + ["a", "b"] +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'b'->-2 $$) as (a agtype); + a +----- + "a" +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'b'->1 $$) as (a agtype); + a +----- + "b" +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'c' $$) as (a agtype); + a +------------ + {"d": "a"} +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'c'->0 $$) as (a agtype); + a +--- + +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'c'->'d' $$) as (a agtype); + a +----- + "a" +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'c'->'d'->-1->0 $$) as (a agtype); + a +----- + "a" +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'list' $$) as (a agtype); + a +----------------- + ["a", "b", "c"] +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'list'->-1 $$) as (a agtype); + a +----- + "c" +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'list'->2->0 $$) as (a agtype); + a +----- + "c" +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) WITH n->'list' AS lst RETURN lst $$) as (a agtype); + a +----------------- + ["a", "b", "c"] +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) WITH n->'list' AS lst RETURN lst->0 $$) as (a agtype); + a +----- + "a" +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) WITH n->'list' AS lst RETURN lst->(size(lst)-1) $$) as (a agtype); + a +----- + "c" +(1 row) + +-- should return null +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->(size(lst)) +$$) AS r(result agtype); + result +-------- + +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'d' $$) as (a agtype); + a +--- + +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'list'->'c' $$) as (a agtype); + a +--- + +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) WITH n->'list' AS lst RETURN lst->(size(lst)) $$) as (a agtype); + a +--- + +(1 row) + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'b'->'a' $$) as (a agtype); + a +--- + +(1 row) + -- -- path extraction #> operator -- diff --git a/regress/sql/agtype.sql b/regress/sql/agtype.sql index b8f08d9e..aa23651d 100644 --- a/regress/sql/agtype.sql +++ b/regress/sql/agtype.sql @@ -108,7 +108,8 @@ SELECT agtype_sub('-1.0', '-1.0'); SELECT agtype_sub('1', '-1.0::numeric'); SELECT agtype_sub('1.0', '-1.0::numeric'); SELECT agtype_sub('1::numeric', '-1.0::numeric'); - +SELECT agtype_sub('[1, 2, 3]', '1'); +SELECT agtype_sub('{"a": 1, "b": 2, "c": 3}', '"a"'); SELECT agtype_neg('-1'); SELECT agtype_neg('-1.0'); @@ -229,6 +230,124 @@ SELECT '3'::agtype + '3.14::numeric'::agtype; SELECT '3.14'::agtype + '3.14::numeric'::agtype; SELECT '3.14::numeric'::agtype + '3.14::numeric'::agtype; +-- +-- Test operator - for extended functionality +-- +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '"a"'; +SELECT '{"a":null , "b":2, "c":3}'::agtype - '"a"'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '"b"'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '"c"'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '"d"'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '""'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '"1"'; +SELECT '{"a":1 , "b":2, "c":3, "1": 4}'::agtype - '"1"'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - age_tostring('a'); +SELECT '{"a":1 , "b":2, "c":3}'::agtype - age_tostring(1); +SELECT '{"a":1 , "b":2, "c":3, "1": 4}'::agtype - age_tostring(1); +SELECT '{}'::agtype - '"a"'; + +SELECT '["a","b","c"]'::agtype - 3; +SELECT '["a","b","c"]'::agtype - 2; +SELECT '["a","b","c"]'::agtype - 1; +SELECT '["a","b","c"]'::agtype - 0; +SELECT '["a","b","c"]'::agtype - -1; +SELECT '["a","b","c"]'::agtype - -2; +SELECT '["a","b","c"]'::agtype - -3; +SELECT '["a","b","c"]'::agtype - -4; +SELECT '["a","b","c"]'::agtype - '2'; +SELECT '["a","b","c"]'::agtype - -(true::int); +SELECT '[]'::agtype - 1; + +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '["b"]'::agtype; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '["c","b"]'::agtype; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '[]'::agtype; +SELECT '["a","b","c"]'::agtype - '[]'; +SELECT '["a","b","c"]'::agtype - '[1]'; +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[9]'; +SELECT '["a","b","c"]'::agtype - '[1, -1]'; +SELECT '["a","b","c"]'::agtype - '[1, -1, 3, 4]'; +SELECT '["a","b","c"]'::agtype - '[1, -1, 3, 4, 0]'; +SELECT '["a","b","c"]'::agtype - '[-1, 1, 3, 4, 1]'; +SELECT '["a","b","c"]'::agtype - '[-1, 1, 3, 4, 0]'; +SELECT '["a","b","c"]'::agtype - '[1, 1]'; +SELECT '["a","b","c"]'::agtype - '[1, 1, 1]'; +SELECT '["a","b","c"]'::agtype - '[1, 1, -1]'; +SELECT '["a","b","c"]'::agtype - '[1, 1, -1, -1]'; +SELECT '["a","b","c"]'::agtype - '[-2, -4, -5, -1]'; +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[0, 4, 3, 2]'; +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[0, 4, 3, 2, -1]'; +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[3, 3, 4, 4, 6, 8, 9]'; +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[8, 9, -7, -6]'; + +-- multiple sub operations +SELECT '{"a":1 , "b":2, "c":3, "1": 4}'::agtype - age_tostring(1) - age_tostring(1); +SELECT '{"a":1 , "b":2, "c":3, "1": 4}'::agtype - age_tostring(1) - age_tostring('a'); +SELECT '{"a":1 , "b":2, "c":3, "1": 4}'::agtype - age_tostring(1) - age_tostring('a') - age_tostring('e') - age_tostring('c'); +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '["c","b"]' - '["a"]'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '["c","b"]' - '["e"]' - '["a"]'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '["c","b"]' - '[]'; +SELECT '["a","b","c"]'::agtype - '[-1]' - '[-1]'; +SELECT '["a","b","c"]'::agtype - '[-1]' - '[-2]' - '[-2]'; +SELECT '["a","b","c"]'::agtype - '[-1]' - '[]' - '[-2]'; +SELECT '["a","b","c"]'::agtype - '[-1]' - '[4]' - '[-2]'; +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[8, 9, -7, -6]' - '1'; +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[8, 9, -7, -6]' - '[1, 0]'; +SELECT '[1, 2, 3, 4, 5, 6]'::agtype - '[8, 9, -7, -6]' - 3 - '[]'; + +-- errors out +SELECT '["a","b","c"]'::agtype - '["1"]'; +SELECT '["a","b","c"]'::agtype - '[null]'; +SELECT '["a","b","c"]'::agtype - '"1"'; +SELECT '["a","b","c"]'::agtype - 'null'; +SELECT '["a","b","c"]'::agtype - '[-1]' - '["-2"]' - '[-2]'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '[1]'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '[null]'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '1'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - 'null'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype - '["c","b"]' - '[1]' - '["a"]'; +SELECT 'null'::agtype - '1'; +SELECT 'null'::agtype - '[1]'; +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype - '"a"'; +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype - '["a"]'; +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype - '[1]'; +SELECT '{"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge'::agtype - '{"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge'::agtype; +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype - '[]'; + +-- +-- Test operator + for extended functionality +-- +SELECT '[1, 2, 3]'::agtype + '[4, 5]'::agtype; +SELECT '[1, 2, true, "string", 1.4::numeric]'::agtype + '[4.5, -5::numeric, {"a": true}]'::agtype; +SELECT '[{"a":1 , "b":2, "c":3}]'::agtype + '[]'; +SELECT '[{"a":1 , "b":2, "c":3}]'::agtype + '[{"d": 4}]'; + +SELECT '{"a":1 , "b":2, "c":3}'::agtype + '["b", 2, {"d": 4}]'::agtype; +SELECT '["b", 2, {"d": 4}]'::agtype + '{"a":1 , "b":2, "c":3}'::agtype; +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype + '[1]'; +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex'::agtype + '[1, "e", true]'; +SELECT '[]'::agtype + '{}'; +SELECT '[]'::agtype + '{"a": 1}'; + +SELECT '{"a":1 , "b":2, "c":3}'::agtype + '{"b": 2}'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype + '{"": 2}'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype + '{"a":1 , "b":2, "c":3}'; +SELECT '{"a":1 , "b":2, "c":3}'::agtype + '{}'; +SELECT '{}'::agtype + '{}'; + +SELECT '1'::agtype + '[{"a":1 , "b":2, "c":3}]'::agtype; + +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex'::agtype + '{"d": 4}'; + +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex'::agtype + '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex'::agtype; +SELECT '[{"id": 1688849860263938, "label": "v2", "properties": {"id": "middle"}}::vertex, {"id": 1970324836974594, "label": "e2", "end_id": 1688849860263937, "start_id": 1688849860263938, "properties": {}}::edge, {"id": 1688849860263937, "label": "v2", "properties": {"id": "initial"}}::vertex]::path'::agtype + ' [{"id": 1688849860263938, "label": "v2", "properties": {"id": "middle"}}::vertex, {"id": 1970324836974594, "label": "e2", "end_id": 1688849860263937, "start_id": 1688849860263938 [...] +SELECT '[{"id": 1688849860263938, "label": "v2", "properties": {"id": "middle"}}::vertex, {"id": 1970324836974594, "label": "e2", "end_id": 1688849860263937, "start_id": 1688849860263938, "properties": {}}::edge, {"id": 1688849860263937, "label": "v2", "properties": {"id": "initial"}}::vertex]::path'::agtype + '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}} [...] +SELECT '{"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge'::agtype + '{"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge'::agtype; +SELECT '{"id": 1688849860263951, "label": "e_var", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {"id": 0}}::edge'::agtype + '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888}}::vertex'::agtype; + +-- errors out +SELECT '1'::agtype + '{"a":1 , "b":2, "c":3}'::agtype; +SELECT '{"a":1 , "b":2, "c":3}'::agtype + '"string"'; + -- -- Test overloaded agytype any operators +, -, *, /, % -- @@ -609,17 +728,6 @@ SELECT agtype_access_operator('{"bool":false, "int":3, "float":3.14}', 'true'); SELECT agtype_access_operator('{"bool":false, "int":3, "float":3.14}', '2'); SELECT agtype_access_operator('{"bool":false, "int":3, "float":3.14}', '2.0'); -SELECT i, pg_typeof(i) FROM (SELECT '{"bool":true, "array":[1,3,{"bool":false, "int":3, "float":3.14},7], "float":3.14}'::agtype->'array'->2->'float' as i) a; -SELECT i, pg_typeof(i) FROM (SELECT '{"bool":true, "array":[1,3,{"bool":false, "int":3, "float":3.14},7], "float":3.14}'::agtype->'array'->2->>'float' as i) a; -SELECT i, pg_typeof(i) FROM (SELECT '{}'::agtype->'array' as i) a; -SELECT i, pg_typeof(i) FROM (SELECT '[]'::agtype->0 as i) a; -SELECT i, pg_typeof(i) FROM (SELECT '[0, 1]'::agtype->2 as i) a; -SELECT i, pg_typeof(i) FROM (SELECT '[0, 1]'::agtype->3 as i) a; -SELECT i, pg_typeof(i) FROM (SELECT '{"bool":false, "int":3, "float":3.14}'::agtype->'true' as i) a; -SELECT i, pg_typeof(i) FROM (SELECT '{"bool":false, "int":3, "float":3.14}'::agtype->2 as i) a; -SELECT i, pg_typeof(i) FROM (SELECT '{"bool":false, "int":3, "float":3.14}'::agtype->'true'->2 as i) a; -SELECT i, pg_typeof(i) FROM (SELECT '{"bool":false, "int":3, "float":3.14}'::agtype->'true'->>2 as i) a; - -- Test duplicate keys and null value -- expected: only the latest key, among duplicates, will be kept; and null will be removed SELECT * FROM create_graph('agtype_null_duplicate_test'); diff --git a/regress/sql/expr.sql b/regress/sql/expr.sql index b74df4bb..326b22e5 100644 --- a/regress/sql/expr.sql +++ b/regress/sql/expr.sql @@ -499,6 +499,30 @@ RETURN [ ][1].array[2]["float"] $$) AS r(result agtype); +SELECT * +FROM cypher('expr', $$ + WITH {bool_val: false, int_val: 3, float_val: 3.14, array: [1, 2, 3]} as a + RETURN a - 'array' +$$) as (a agtype); + +SELECT * +FROM cypher('expr', $$ + WITH {bool_val: false, int_val: 3, float_val: 3.14, array: [1, 2, 3]} as a + RETURN a - 'int_val' +$$) as (a agtype); + +SELECT * +FROM cypher('expr', $$ + WITH {bool_val: false, int_val: 3, float_val: 3.14, array: [1, 2, 3]} as a + RETURN a.array - 1 +$$) as (a agtype); + +SELECT * +FROM cypher('expr', $$ + WITH {bool_val: false, int_val: 3, float_val: 3.14, array: [1, 2, 3]} as a + RETURN a.array + 1 +$$) as (a agtype); + -- -- Test STARTS WITH, ENDS WITH, and CONTAINS transform logic -- diff --git a/regress/sql/jsonb_operators.sql b/regress/sql/jsonb_operators.sql index e8ed4880..d0ebb9ee 100644 --- a/regress/sql/jsonb_operators.sql +++ b/regress/sql/jsonb_operators.sql @@ -188,6 +188,140 @@ SELECT '{"a":null, "b":"qq"}'::agtype ?& '{""}'; SELECT '{"a":null, "b":"qq"}'::agtype ?& '{null}'; SELECT '{"a":null, "b":"qq"}'::agtype ?& '{"null"}'; +-- +-- Agtype access operators (->, ->>) +-- + +SELECT i, pg_typeof(i) FROM (SELECT '{"bool":true, "array":[1,3,{"bool":false, "int":3, "float":3.14},7], "float":3.14}'::agtype->'array'::text->2->'float'::text as i) a; +SELECT i, pg_typeof(i) FROM (SELECT '{"bool":true, "array":[1,3,{"bool":false, "int":3, "float":3.14},7], "float":3.14}'::agtype->'array'::text->2->>'float'::text as i) a; + +SELECT i, pg_typeof(i) FROM (SELECT '[0, 1]'::agtype->0 as i) a; +SELECT i, pg_typeof(i) FROM (SELECT '[0, 1]'::agtype->>0 as i) a; + +/* + * access agtype object field or array element (->) + */ + +-- LHS is an object +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"n"'::agtype; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"a"'::agtype; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"b"'::agtype; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"c"'::agtype; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"d"'::agtype; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"d"'::agtype -> '"1"'::agtype; +SELECT '{"a": [-1, -2, -3]}'::agtype -> 'a'::text; +SELECT '{"a": 9, "b": 11, "c": {"ca": [[], {}, null]}, "d": true, "1": false}'::agtype -> '"1"'::text::agtype; +SELECT '{"a": true, "b": null, "c": -1.99, "d": {"e": []}, "1": [{}, [[[]]]], " ": [{}]}'::agtype -> ' '::text; +SELECT '{"a": true, "b": null, "c": -1.99, "d": {"e": []}, "1": [{}, [[[]]]], " ": [{}]}'::agtype -> '" "'::agtype; + +-- should return null +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"e"'::agtype; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}, "1": -19}'::agtype -> '1'::agtype; +SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype -> '""'::agtype; +SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype -> null::text; +SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype -> null::int; +SELECT '{"a": [-1, -2, -3]}'::agtype -> '"a"'::text; +SELECT '{"a": 9, "b": 11, "c": {"ca": [[], {}, null]}, "d": true, "1": false}'::agtype -> '1'::text::agtype; + +-- LHS is an array +SELECT '["a","b","c",[1,2],null]'::agtype -> '0'::agtype; +SELECT '["a","b","c",[1,2],null]'::agtype -> 1; +SELECT '["a","b","c",[1,2],null]'::agtype -> '2'::agtype; +SELECT '["a","b","c",[1,2],null]'::agtype -> 3; +SELECT '["a","b","c",[1,2],null]'::agtype -> '3'::agtype -> '1'::agtype; +SELECT '["a","b","c",[1,2],null]'::agtype -> '3'::agtype -> '-1'::agtype; +SELECT '["a","b","c",[1,2],null]'::agtype -> '3'::agtype -> -1; +SELECT '["a","b","c",[1,2],null]'::agtype -> 4; +SELECT '["a","b","c",[1,2],null]'::agtype -> '-1'::agtype; +SELECT '["a","b","c",[1,2],null]'::agtype -> '-5'::agtype; +SELECT '[1, 2, 3]'::agtype -> '1'::text::agtype; +SELECT '[1, 2, 3]'::agtype -> '1'::text::agtype::int; +SELECT '[1, 2, 3]'::agtype -> '1'::text::agtype::int::bool::int; +SELECT '[1, 2, 3]'::agtype -> '1'::text::int; +SELECT '[1, "e", {"a": 1}, {}, [{}, {}, -9]]'::agtype -> true::int; +SELECT '[1, "e", {"a": 1}, {}, [{}, {}, -9]]'::agtype -> 1.9::int; +SELECT '[1, "e", {"a": 1}, {}, [{}, {}, -9]]'::agtype -> 1.1::int; +SELECT 'true'::agtype -> 0.1::int; +SELECT 'true'::agtype -> 0; +SELECT 'true'::agtype -> false::int; +SELECT 'true'::agtype -> 0.1::int::bool::int; +SELECT '[1, 9]'::agtype -> -1.2::int; +SELECT 'true'::agtype -> 0.1::bigint::int; + +-- should return null +SELECT '["a","b","c",[1,2],null]'::agtype -> '5'::agtype; +SELECT '["a","b","c",[1,2],null]'::agtype -> '-6'::agtype; +SELECT '["a","b","c",[1,2],null]'::agtype -> '"a"'::agtype; +SELECT '"foo"'::agtype -> '1'::agtype; +SELECT '[1, 2, 3, "string", {"a": 1}, {"a": []}]'::agtype -> '5'::text; +SELECT '[1, 2, 3]'::agtype -> '[1]'; +SELECT '[1, "e", {"a": 1}, {}, [{}, {}, -9]]'::agtype -> '{"a": 1}'::agtype; +SELECT 'true'::agtype -> 0::text; +SELECT 'true'::agtype -> false::int::text; + +-- LHS is vertex/edge/path +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype -> '"a"'; +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype -> '"e"' -> 'g'::text; +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype -> '"e"' -> 'h'::text -> -1; +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype -> '"e"' -> 'h'::text -> -1 -> 0; +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype -> '"id"'; + +-- access array element nested inside object or object field nested inside array on LHS +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"d"'::agtype -> '"1"'::agtype -> '1'::agtype; +SELECT '{"a": 9, "b": 11, "c": {"ca": [[], {}, null]}, "d": true}'::agtype -> 'c'::text -> '"ca"'::agtype -> 0; +SELECT '{"a":[1, 2, 3], "b": {"c": ["cc", "cd"]}}'::agtype -> '"b"'::agtype -> 'c'::text -> 1 -> 0; +SELECT '{"a":[1, 2, 3], "b": {"c": 1}}'::agtype -> '"b"'::agtype -> 1; + +/* + * access agtype object field or array element as text (->>) + */ + +-- LHS is an object +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ->> '"a"'::agtype; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype ->> '"d"'::agtype; +SELECT '{"1": -1.99, "a": 1, "b": 2, "c": {"d": [{}, [[[], [9]]]]}, "1": true}'::agtype ->> '"1"'; +SELECT '{"1": -1.99, "a": 1, "b": 2, "c": {"d": [{}, [[[], [9]]]]}, "1": true}'::agtype ->> '1'::text; + +-- should return null +SELECT '{"1": -1.99, "a": 1, "b": 2, "c": {"d": [{}, [[[], [9]]]]}, "1": true}'::agtype ->> 1; +SELECT '{"1": -1.99, "a": 1, "b": 2, "c": {"d": [{}, [[[], [9]]]]}, "1": true}'::agtype ->> '1'; +SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype ->> null::text; +SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype ->> null::int; + +-- LHS is an array +SELECT '["a","b","c",[1,2],null]'::agtype ->> 0; +SELECT '["a","b","c",[1,2],null]'::agtype ->> '1'::agtype; +SELECT '["a","b","c",[1,2],null]'::agtype ->> '2'::int; +SELECT '["a","b","c",[1,2],null]'::agtype ->> 3::int; +SELECT '["a","b","c",[1,2],null]'::agtype ->> '4'::agtype; +SELECT '[1, 2, "e", true, null, {"a": true, "b": {}, "c": [{}, [[], {}]]}, null]'::agtype ->> 0.1::float::bigint::int; +SELECT '"foo"'::agtype ->> '0'::agtype; +SELECT '[false, {}]'::agtype ->> 1::bool::int::bigint::int; + +-- should return null +SELECT '["a","b","c",[1,2],null]'::agtype ->> '-1'::agtype; +SELECT '[1, 2, "e", true, null, {"a": true, "b": {}, "c": [{}, [[], {}]]}, null]'::agtype ->> 2::text; +SELECT '[1, 2, "e", true, null, {"a": true, "b": {}, "c": [{}, [[], {}]]}, null]'::agtype ->> '2'::text; +SELECT '[1, 2, "e", true, null, {"a": true, "b": {}, "c": [{}, [[], {}]]}, null]'::agtype ->> 'null'::text; +SELECT '"foo"'::agtype ->> '1'::agtype; + +-- LHS is vertex/edge/path +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype ->> '"a"'; +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype ->> '"id"'; + +-- using -> and ->> in single query +SELECT '{"1": -1.99, "a": 1, "b": 2, "c": {"d": [{}, [[[], [9]]]]}}'::agtype -> '"1"' ->> '0'; +SELECT '["a","b","c",[1,2],null]'::agtype -> '3'::agtype ->> '1'::agtype; +SELECT ('["a","b","c",[1,2],null]'::agtype -> '3'::agtype) ->> 0; +SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]}}'::agtype -> '"d"'::agtype ->> '1'::agtype; +SELECT '{"id": 1125899906842625, "label": "Vertex", "properties": {"a": "xyz", "b": true, "c": -19.888, "e": {"f": "abcdef", "g": {}, "h": [[], {}]}, "i": {"j": 199, "k": {"l": "mnopq"}}}}::vertex'::agtype -> '"e"' -> 'h'::text -> -1 ->> 0; + +-- should give error +SELECT '["a","b","c",[1,2],null]'::agtype ->> '3'::agtype ->> '1'::agtype; +SELECT '["a","b","c",[1,2],null]'::agtype ->> '3'::agtype -> '1'::agtype; +SELECT '[1, 2, "e", true, null, {"a": true, "b": {}, "c": [{}, [[], {}]]}, null]'::agtype ->> 0.1::float::bigint; +SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::agtype ->> "'z'"::agtype; + -- -- Agtype path extraction operators (#>, #>>) -- @@ -503,6 +637,109 @@ SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n ?& n $$) as (a agtyp 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); +-- +-- agtype access operator (->) +-- +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-1 +$$) AS r(result agtype); + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->1 +$$) AS r(result agtype); + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-4 +$$) AS r(result agtype); + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-4->'array' +$$) AS r(result agtype); + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-4->'array'->-2->'bool' +$$) AS r(result agtype); + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-4->'array'->-2->'bool'->-1 +$$) AS r(result agtype); + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->(size(lst)-1) +$$) AS r(result agtype); + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->(size(lst)%size(lst)) +$$) AS r(result agtype); + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-4->'array'->-2->'float' +$$) AS r(result agtype); + +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->-4->'array'->-2->'float'->0 +$$) AS r(result agtype); + +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'a' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'a'->-1 $$) 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'->-2 $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'b'->1 $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'c' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'c'->0 $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'c'->'d' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'c'->'d'->-1->0 $$) 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->'list'->-1 $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'list'->2->0 $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) WITH n->'list' AS lst RETURN lst $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) WITH n->'list' AS lst RETURN lst->0 $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) WITH n->'list' AS lst RETURN lst->(size(lst)-1) $$) as (a agtype); + +-- should return null +SELECT * FROM cypher('jsonb_operators', $$ + WITH [1, + {bool: true, int: 3, array: [9, 11, {bool: false, float: 3.14}, 13]}, + 5, 7, 9] as lst + RETURN lst->(size(lst)) +$$) AS r(result 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'->'c' $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) WITH n->'list' AS lst RETURN lst->(size(lst)) $$) as (a agtype); +SELECT * FROM cypher('jsonb_operators',$$MATCH (n) return n->'json'->'b'->'a' $$) as (a agtype); + -- -- path extraction #> operator -- diff --git a/src/backend/parser/cypher_gram.y b/src/backend/parser/cypher_gram.y index ff067864..94054324 100644 --- a/src/backend/parser/cypher_gram.y +++ b/src/backend/parser/cypher_gram.y @@ -1550,6 +1550,10 @@ expr: ag_scanner_errposition(@1, scanner))); } } + | expr '-' '>' expr %prec '.' + { + $$ = (Node *)makeSimpleA_Expr(AEXPR_OP, "->", $1, $4, @2); + } | expr TYPECAST symbolic_name { $$ = make_typecast_expr($1, $3, @2); diff --git a/src/backend/utils/adt/agtype.c b/src/backend/utils/adt/agtype.c index 2f93983e..84eed592 100644 --- a/src/backend/utils/adt/agtype.c +++ b/src/backend/utils/adt/agtype.c @@ -145,10 +145,19 @@ static agtype_value *execute_array_access_operator_internal(agtype *array, static agtype_value *execute_map_access_operator(agtype *map, agtype_value* map_value, agtype *key); -static agtype_value *execute_map_access_operator_internal( - agtype *map, agtype_value *map_value, char *key, int key_len); -Datum agtype_object_field_impl(FunctionCallInfo fcinfo, bool as_text); -Datum agtype_array_element_impl(FunctionCallInfo fcinfo, bool as_text); +static agtype_value *execute_map_access_operator_internal(agtype *map, + agtype_value *map_value, + char *key, + int key_len); +static Datum agtype_object_field_impl(FunctionCallInfo fcinfo, + agtype *agtype_in, + char *key, int key_len, bool as_text); +static Datum agtype_array_element_impl(FunctionCallInfo fcinfo, + agtype *agtype_in, int element, + bool as_text); +static Datum process_access_operator_result(FunctionCallInfo fcinfo, + agtype_value *agtv, + bool as_text); /* typecast functions */ static void agtype_typecast_object(agtype_in_state *state, char *annotation); static void agtype_typecast_array(agtype_in_state *state, char *annotation); @@ -3352,28 +3361,32 @@ static int extract_variadic_args_min(FunctionCallInfo fcinfo, return nargs; } -/* - * get agtype object field - */ -Datum agtype_object_field_impl(FunctionCallInfo fcinfo, bool as_text) +static Datum process_access_operator_result(FunctionCallInfo fcinfo, + agtype_value *agtv, + bool as_text) { - agtype *agtype_in = AG_GET_ARG_AGTYPE_P(0); - text *key = PG_GETARG_TEXT_PP(1); - agtype_value *v; - - if (!AGT_ROOT_IS_OBJECT(agtype_in)) - { - PG_RETURN_NULL(); - } - - v = execute_map_access_operator_internal(agtype_in, NULL, VARDATA_ANY(key), - VARSIZE_ANY_EXHDR(key)); - - if (v != NULL) + if (agtv != NULL) { if (as_text) { - text *result = agtype_value_to_text(v, false); + text *result; + + if (agtv->type == AGTV_BINARY) + { + StringInfo out = makeStringInfo(); + agtype_container *agtc = + (agtype_container *)agtv->val.binary.data; + char *str; + + str = agtype_to_cstring_worker(out, agtc, + agtv->val.binary.len, + false); + result = cstring_to_text(str); + } + else + { + result = agtype_value_to_text(agtv, false); + } if (result) { @@ -3382,20 +3395,16 @@ Datum agtype_object_field_impl(FunctionCallInfo fcinfo, bool as_text) } else { - AG_RETURN_AGTYPE_P(agtype_value_to_agtype(v)); + AG_RETURN_AGTYPE_P(agtype_value_to_agtype(agtv)); } } PG_RETURN_NULL(); } -/* - * get agtype array element - */ -Datum agtype_array_element_impl(FunctionCallInfo fcinfo, bool as_text) +Datum agtype_array_element_impl(FunctionCallInfo fcinfo, agtype *agtype_in, + int element, bool as_text) { - agtype *agtype_in = AG_GET_ARG_AGTYPE_P(0); - int element = PG_GETARG_INT32(1); agtype_value *v; if (!AGT_ROOT_IS_ARRAY(agtype_in)) @@ -3403,55 +3412,148 @@ Datum agtype_array_element_impl(FunctionCallInfo fcinfo, bool as_text) PG_RETURN_NULL(); } - if (element < 0) + v = execute_array_access_operator_internal(agtype_in, NULL, element); + + return process_access_operator_result(fcinfo, v, as_text); +} + +Datum agtype_object_field_impl(FunctionCallInfo fcinfo, agtype *agtype_in, + char *key, int key_len, bool as_text) +{ + agtype_value *v; + agtype* process_agtype; + + if (AGT_ROOT_IS_SCALAR(agtype_in)) + { + process_agtype = + agtype_value_to_agtype(extract_entity_properties(agtype_in, + false)); + } + else + { + process_agtype = agtype_in; + } + + if (!AGT_ROOT_IS_OBJECT(process_agtype)) { PG_RETURN_NULL(); } - v = execute_array_access_operator_internal(agtype_in, NULL, element); + v = execute_map_access_operator_internal(process_agtype, NULL, + key, key_len); + + return process_access_operator_result(fcinfo, v, as_text); +} + +PG_FUNCTION_INFO_V1(agtype_object_field_agtype); - if (v != NULL) +Datum agtype_object_field_agtype(PG_FUNCTION_ARGS) +{ + agtype *agt = AG_GET_ARG_AGTYPE_P(0); + agtype *key = AG_GET_ARG_AGTYPE_P(1); + agtype_value *key_value; + + if (!AGT_ROOT_IS_SCALAR(key)) { - if (as_text) - { - text *result = agtype_value_to_text(v, false); + PG_RETURN_NULL(); + } - if (result) - { - PG_RETURN_TEXT_P(result); - } - } - else - { - AG_RETURN_AGTYPE_P(agtype_value_to_agtype(v)); - } + key_value = get_ith_agtype_value_from_container(&key->root, 0); + + if (key_value->type == AGTV_INTEGER) + { + PG_RETURN_TEXT_P(agtype_array_element_impl(fcinfo, agt, + key_value->val.int_value, + false)); + } + else if (key_value->type == AGTV_STRING) + { + AG_RETURN_AGTYPE_P(agtype_object_field_impl(fcinfo, agt, + key_value->val.string.val, + key_value->val.string.len, + false)); + } + else + { + PG_RETURN_NULL(); } +} - PG_RETURN_NULL(); +PG_FUNCTION_INFO_V1(agtype_object_field_text_agtype); + +Datum agtype_object_field_text_agtype(PG_FUNCTION_ARGS) +{ + agtype *agt = AG_GET_ARG_AGTYPE_P(0); + agtype *key = AG_GET_ARG_AGTYPE_P(1); + agtype_value *key_value; + + if (!AGT_ROOT_IS_SCALAR(key)) + { + PG_RETURN_NULL(); + } + + key_value = get_ith_agtype_value_from_container(&key->root, 0); + + if (key_value->type == AGTV_INTEGER) + { + PG_RETURN_TEXT_P(agtype_array_element_impl(fcinfo, agt, + key_value->val.int_value, + true)); + } + else if (key_value->type == AGTV_STRING) + { + AG_RETURN_AGTYPE_P(agtype_object_field_impl(fcinfo, agt, + key_value->val.string.val, + key_value->val.string.len, + true)); + } + else + { + PG_RETURN_NULL(); + } } PG_FUNCTION_INFO_V1(agtype_object_field); + Datum agtype_object_field(PG_FUNCTION_ARGS) { - return agtype_object_field_impl(fcinfo, false); + agtype *agt = AG_GET_ARG_AGTYPE_P(0); + text *key = PG_GETARG_TEXT_PP(1); + + AG_RETURN_AGTYPE_P(agtype_object_field_impl(fcinfo, agt, VARDATA_ANY(key), + VARSIZE_ANY_EXHDR(key), + false)); } PG_FUNCTION_INFO_V1(agtype_object_field_text); + Datum agtype_object_field_text(PG_FUNCTION_ARGS) { - return agtype_object_field_impl(fcinfo, true); + agtype *agt = AG_GET_ARG_AGTYPE_P(0); + text *key = PG_GETARG_TEXT_PP(1); + + PG_RETURN_TEXT_P(agtype_object_field_impl(fcinfo, agt, VARDATA_ANY(key), + VARSIZE_ANY_EXHDR(key), true)); } PG_FUNCTION_INFO_V1(agtype_array_element); + Datum agtype_array_element(PG_FUNCTION_ARGS) { - return agtype_array_element_impl(fcinfo, false); + agtype *agt = AG_GET_ARG_AGTYPE_P(0); + int elem = PG_GETARG_INT32(1); + + AG_RETURN_AGTYPE_P(agtype_array_element_impl(fcinfo, agt, elem, false)); } PG_FUNCTION_INFO_V1(agtype_array_element_text); + Datum agtype_array_element_text(PG_FUNCTION_ARGS) { - return agtype_array_element_impl(fcinfo, true); + agtype *agt = AG_GET_ARG_AGTYPE_P(0); + int elem = PG_GETARG_INT32(1); + + PG_RETURN_TEXT_P(agtype_array_element_impl(fcinfo, agt, elem, true)); } PG_FUNCTION_INFO_V1(agtype_access_operator); @@ -6440,13 +6542,15 @@ agtype_iterator *get_next_list_element(agtype_iterator *it, Assert(itok == WAGT_ELEM || itok == WAGT_END_ARRAY); /* if this is the end of the array return NULL */ - if (itok == WAGT_END_ARRAY) { + if (itok == WAGT_END_ARRAY) + { return NULL; } /* this should be the element, copy it */ - if (itok == WAGT_ELEM) { - memcpy(elem, &tmp, sizeof(agtype_value)); + if (itok == WAGT_ELEM) + { + *elem = tmp; } return it; @@ -10323,7 +10427,7 @@ static agtype_iterator *get_next_object_key(agtype_iterator *it, /* this should be the key, copy it */ if (itok == WAGT_KEY) { - memcpy(key, &tmp, sizeof(agtype_value)); + *key = tmp; } /* diff --git a/src/backend/utils/adt/agtype_ops.c b/src/backend/utils/adt/agtype_ops.c index b5c633c9..289c6949 100644 --- a/src/backend/utils/adt/agtype_ops.c +++ b/src/backend/utils/adt/agtype_ops.c @@ -33,7 +33,6 @@ #include "utils/agtype.h" -static void ereport_op_str(const char *op, agtype *lhs, agtype *rhs); static agtype *agtype_concat_impl(agtype *agt1, agtype *agt2); static agtype_value *iterator_concat(agtype_iterator **it1, agtype_iterator **it2, @@ -42,6 +41,8 @@ static void concat_to_agtype_string(agtype_value *result, char *lhs, int llen, char *rhs, int rlen); static char *get_string_from_agtype_value(agtype_value *agtv, int *length); static Datum get_agtype_path_all(FunctionCallInfo fcinfo, bool as_text); +static agtype *delete_from_object(agtype *agt, char *keyptr, int keylen); +static agtype *delete_from_array(agtype *agt, agtype* indexes); static void concat_to_agtype_string(agtype_value *result, char *lhs, int llen, char *rhs, int rlen) @@ -137,7 +138,10 @@ bool is_numeric_result(agtype_value *lhs, agtype_value *rhs) (lhs->type == AGTV_INTEGER || lhs->type == AGTV_FLOAT || rhs->type == AGTV_INTEGER || rhs->type == AGTV_FLOAT )) || (lhs->type == AGTV_NUMERIC && rhs->type == AGTV_NUMERIC)) + { return true; + } + return false; } @@ -155,16 +159,7 @@ Datum agtype_add(PG_FUNCTION_ARGS) /* If both are not scalars */ if (!(AGT_ROOT_IS_SCALAR(lhs) && AGT_ROOT_IS_SCALAR(rhs))) { - Datum agt; - - /* It can't be a scalar and an object */ - if ((AGT_ROOT_IS_SCALAR(lhs) && AGT_ROOT_IS_OBJECT(rhs)) || - (AGT_ROOT_IS_OBJECT(lhs) && AGT_ROOT_IS_SCALAR(rhs)) || - /* It can't be two objects */ - (AGT_ROOT_IS_OBJECT(lhs) && AGT_ROOT_IS_OBJECT(rhs))) - ereport_op_str("+", lhs, rhs); - - agt = AGTYPE_P_GET_DATUM(agtype_concat_impl(lhs, rhs)); + Datum agt = AGTYPE_P_GET_DATUM(agtype_concat_impl(lhs, rhs)); PG_RETURN_DATUM(agt); } @@ -230,10 +225,20 @@ Datum agtype_add(PG_FUNCTION_ARGS) agtv_result.type = AGTV_NUMERIC; agtv_result.val.numeric = DatumGetNumeric(numd); } + /* if both operands are scalar(vertex/edge/path), concat the two */ + else if (AGT_ROOT_IS_SCALAR(lhs) && AGT_ROOT_IS_SCALAR(rhs)) + { + Datum agt = AGTYPE_P_GET_DATUM(agtype_concat_impl(lhs, rhs)); + + PG_RETURN_DATUM(agt); + } else + { /* Not a covered case, error out */ - ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("Invalid input parameter types for agtype_add"))); + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Invalid input parameter types for agtype_add"))); + } AG_RETURN_AGTYPE_P(agtype_value_to_agtype(&agtv_result)); } @@ -261,6 +266,147 @@ Datum agtype_any_add(PG_FUNCTION_ARGS) AG_RETURN_AGTYPE_P(DATUM_GET_AGTYPE_P(result)); } +/* + * For the given indexes array, delete elements at those indexes + * from the passed in agtype array. + */ +static agtype *delete_from_array(agtype *agt, agtype *indexes) +{ + agtype_parse_state *state = NULL; + agtype_iterator *it, *it_indexes = NULL; + uint32 i = 0, n; + agtype_value v, *res = NULL; + agtype_iterator_token r; + + if (!AGT_ROOT_IS_ARRAY(agt) || AGT_ROOT_IS_SCALAR(agt)) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot delete from scalar or object" + "using integer index"))); + } + + // array is empty, pass the original array + if (AGT_ROOT_COUNT(agt) == 0) + { + return agt; + } + + // start buidiling the result agtype array + it = agtype_iterator_init(&agt->root); + + r = agtype_iterator_next(&it, &v, false); + Assert(r == WAGT_BEGIN_ARRAY); + + n = v.val.array.num_elems; + + push_agtype_value(&state, r, NULL); + + while ((r = agtype_iterator_next(&it, &v, true)) != WAGT_DONE) + { + if (r == WAGT_ELEM) + { + /* + * use logic similar to agtype_contains to check + * if the current index (itself or in inverted form) + * is contained in the indexes array, + * if yes, skip the element at that index in agt array + * else add the element in result agtype array + */ + agtype_value cur_idx, neg_idx; + agtype *cur_idx_agt, *neg_idx_agt; + agtype_iterator *it_cur_idx, *it_neg_idx; + bool contains_idx, contains_neg_idx; + + cur_idx.type = AGTV_INTEGER; + cur_idx.val.int_value = i++; + cur_idx_agt = agtype_value_to_agtype(&cur_idx); + + neg_idx.type = AGTV_INTEGER; + neg_idx.val.int_value = cur_idx.val.int_value - n; + neg_idx_agt = agtype_value_to_agtype(&neg_idx); + + it_cur_idx = agtype_iterator_init(&cur_idx_agt->root); + it_neg_idx = agtype_iterator_init(&neg_idx_agt->root); + + it_indexes = agtype_iterator_init(&indexes->root); + contains_idx = agtype_deep_contains(&it_indexes, &it_cur_idx); + + // re-initialize indexes array iterator + it_indexes = agtype_iterator_init(&indexes->root); + contains_neg_idx = agtype_deep_contains(&it_indexes, &it_neg_idx); + + if (contains_idx || contains_neg_idx) + { + continue; + } + } + + res = push_agtype_value(&state, r, r < WAGT_BEGIN_ARRAY ? &v : NULL); + } + + Assert(res != NULL); + + return agtype_value_to_agtype(res); +} + +/* + * For the given key delete that property from the passed in agtype + * object. + */ +static agtype *delete_from_object(agtype *agt, char *keyptr, int keylen) +{ + agtype_parse_state *state = NULL; + agtype_iterator *it; + agtype_value v, *res = NULL; + bool skipNested = false; + agtype_iterator_token r; + + if (!AGT_ROOT_IS_OBJECT(agt)) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot delete from scalar or array" + "using string key"))); + } + + if (AGT_ROOT_COUNT(agt) == 0) + { + return agt; + } + + it = agtype_iterator_init(&agt->root); + + while ((r = agtype_iterator_next(&it, &v, skipNested)) != WAGT_DONE) + { + skipNested = true; + + /* + * Checks the key to compare against the passed in key to be + * deleted. do not add the key and value to the new agtype being + * constructed. + */ + if ((r == WAGT_ELEM || r == WAGT_KEY) && + (v.type == AGTV_STRING && keylen == v.val.string.len && + memcmp(keyptr, v.val.string.val, keylen) == 0)) + { + /* skip corresponding value as well */ + if (r == WAGT_KEY) + { + (void) agtype_iterator_next(&it, &v, true); + } + + continue; + } + + res = push_agtype_value(&state, r, r < WAGT_BEGIN_ARRAY ? &v : NULL); + } + + Assert(res != NULL); + + return agtype_value_to_agtype(res); +} + PG_FUNCTION_INFO_V1(agtype_sub); /* @@ -274,12 +420,109 @@ Datum agtype_sub(PG_FUNCTION_ARGS) agtype_value *agtv_rhs; agtype_value agtv_result; - if (!(AGT_ROOT_IS_SCALAR(lhs)) || !(AGT_ROOT_IS_SCALAR(rhs))) + /* + * Logic to handle when the rhs is a non scalar array. In this + * case; + * 1. if the lhs is an object, the values in the rhs array + * are string keys to be removed from the object. + * 2. if the lhs is an array, the values in the rhs array + * are integer indexes at which values should be removed from array. + * otherwise throw an error + */ + if (AGT_ROOT_IS_ARRAY(rhs) && !AGT_ROOT_IS_SCALAR(rhs)) { - ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("must be scalar value, not array or object"))); + agtype_iterator *it = NULL; + agtype_value elem; - PG_RETURN_NULL(); + if (AGT_ROOT_IS_OBJECT(lhs)) + { + /* + * if rhs array contains any non-string element, error out + * else delete the given keys in the rhs array from lhs object + */ + while ((it = get_next_list_element(it, &rhs->root, &elem))) + { + if (elem.type == AGTV_STRING) + { + lhs = delete_from_object(lhs, elem.val.string.val, + elem.val.string.len); + } + else + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("expected agtype string, not agtype %s", + agtype_value_type_to_string(elem.type)))); + } + } + } + else if (AGT_ROOT_IS_ARRAY(lhs) && !(AGT_ROOT_IS_SCALAR(lhs))) + { + /* + * if rhs array contains any non-integer element, error out + * else delete the values at the given indexes in rhs array + * from the lhs array + */ + while ((it = get_next_list_element(it, &rhs->root, &elem))) + { + if (elem.type != AGTV_INTEGER) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("expected agtype integer, not agtype %s", + agtype_value_type_to_string(elem.type)))); + } + } + + lhs = delete_from_array(lhs, rhs); + } + else + { + ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("must be object or array, not a scalar value"))); + } + + AG_RETURN_AGTYPE_P(lhs); + } + + /* + * When the lhs is an object and rhs is a string, remove the key from + * the object. + * When the lhs is an array and the rhs is an integer then + * remove the value at that index from the array, + * otherwise give an error + */ + if(!AGT_ROOT_IS_SCALAR(lhs)) + { + agtype_value *key; + key = get_ith_agtype_value_from_container(&rhs->root, 0); + + if (AGT_ROOT_IS_OBJECT(lhs) && key->type == AGTV_STRING) + { + AG_RETURN_AGTYPE_P(delete_from_object(lhs, key->val.string.val, + key->val.string.len)); + } + else if (AGT_ROOT_IS_ARRAY(lhs) && key->type == AGTV_INTEGER) + { + AG_RETURN_AGTYPE_P(delete_from_array(lhs, rhs)); + } + else + { + if (AGT_ROOT_IS_OBJECT(lhs)) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("expected agtype string, not agtype %s", + agtype_value_type_to_string(key->type)))); + } + else if (AGT_ROOT_IS_ARRAY(lhs)) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("expected agtype integer, not agtype %s", + agtype_value_type_to_string(key->type)))); + } + } } agtv_lhs = get_ith_agtype_value_from_container(&lhs->root, 0); @@ -322,8 +565,11 @@ Datum agtype_sub(PG_FUNCTION_ARGS) agtv_result.val.numeric = DatumGetNumeric(numd); } else - ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("Invalid input parameter types for agtype_sub"))); + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Invalid input parameter types for agtype_sub"))); + } AG_RETURN_AGTYPE_P(agtype_value_to_agtype(&agtv_result)); } @@ -1812,27 +2058,3 @@ static Datum get_agtype_path_all(FunctionCallInfo fcinfo, bool as_text) AG_RETURN_AGTYPE_P(res); } } - -static void ereport_op_str(const char *op, agtype *lhs, agtype *rhs) -{ - const char *msgfmt; - const char *lstr; - const char *rstr; - - AssertArg(rhs != NULL); - - if (lhs == NULL) - { - msgfmt = "invalid expression: %s%s%s"; - lstr = ""; - } - else - { - msgfmt = "invalid expression: %s %s %s"; - lstr = agtype_to_cstring(NULL, &lhs->root, VARSIZE(lhs)); - } - rstr = agtype_to_cstring(NULL, &rhs->root, VARSIZE(rhs)); - - ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg(msgfmt, lstr, op, rstr))); -}
