prepare

  • AgensGraph installed
  • graph path anything. in console
SET graph_path to t_graph
  • t_graph : graph path name (anything all)
    • if graph path is not invaild, CREATE GRAPH …. (…. is anything e.g. CREATE GRAPH test)

AgensGraph Graph WHERE clause

CREATE TABLE t_name (
    name text,
    age int
);
insert into t_name values ('jhs', '100');
insert into t_name values ('khan', '101');

CREATE (a:person {name:['jhs','hsjeon'], age:102});

-- hybrid query
SELECT *
FROM   t_name
WHERE  name IN (MATCH (a:person) WHERE a.age = 102 RETURN a.name);
  • maybe error message
ERROR:  syntax error at or near "MATCH"
LINE 3: WHERE t_name IN (MATCH (a:person) WHERE age = 102 RETURN a.n...
  • cypher clause is not executed in clause

solution

  • subquery + inlineview query
    • string_to_array
    • unnest
    • regexp_replace
SELECT * 
FROM t_name 
WHERE name IN (SELECT regexp_replace(unnest(string_to_array(t1.name::text, ',')),'[^a-zA-Z0-9]', '', 'g')
               FROM (MATCH (a:person) WHERE a.age = 102 RETURN a.name) t1)
  • graph array property data -> string
    • string_to_array(t1.name::text, ‘,’)
  • array to unnest
    • unnest(string_to_array(t1.name::text, ‘,’))
  • regular expression replace
    • regexp_replace(unnest(string_to_array(t1.name::text, ‘,’)),’[^a-zA-Z0-9]’, ‘’, ‘g’)

result

SELECT * FROM t_name WHERE name IN (SELECT regexp_replace(unnest(string_to_array(t1.name::text, ',')),'[^a-zA-Z0-9]', '', 'g')
FROM (MATCH (a:person) WHERE a.age = 102 RETURN a.name) t1);
 name
------
 jhs
(1 row)