Advanced Cypher Queries

All queries so far have followed the same pattern: a SELECT clause followed by a single Cypher call in the FROM clause. However, a Cypher query can be used in many other ways. This section highlights some more advanced ways of using the Cypher call within a more complex SQL/Cypher Hybrid Query.

CTE Clause

There are no restrictions to using Cypher with CTEs.

Query:

WITH graph_query as ( SELECT * FROM cypher('graph_name', $$ MATCH (n) RETURN n.name, n.age $$) as (name agtype, age agtype) )SELECT * FROM graph_query;

Results:

name	age
‘Andres’	36
‘Tobias’	25
‘Peter’	35
3 row(s) returned

Join Clause

A Cypher query can be part of a JOIN clause.

Developers NoteCypher queries using the CREATE, SET, REMOVE clauses cannot be used in sql queries with Joins, as they affect the Postgres transaction system. One possible solution is to protect the query with CTEs. See the subsection Using CTEs with CREATE, REMOVE, and SET for a solution to this problem.

Query:

-->

SELECT id,

graph_query.name = t.name as names_match,

graph_query.age = t.age as ages_matchFROM schema_name.sql_person AS t

JOIN cypher('graph_name', $$ MATCH (n:Person) RETURN n.name, n.age, id(n) $$) as graph_query(name agtype, age agtype, id agtype)ON t.person_id = graph_query.id

Results:

id	names_match	ages_match
1	True	True
2	False	True
3	True	False
3 row(s) returned

Using CTEs with CREATE, REMOVE, and SET

Prior to the rest of the SQL query running, all CTEs are run and the results are cached and then referenced. This functionality makes it safe to run an Cypher query with a CREATE, SET, or REMOVE clause in the CTE and join to other tables or cypher queries.

WITH graph_query_1 as ( SELECT * FROM cypher('graph_name', $$ MATCH (n:Person), (m:Person)

WHERE n.name = 'Tobias' AND m.name = 'Peter' CREATE (n)-[:FRIEND {id: n.name + '->' + m.name}]->(m) RETURN m.name $$) as (Tobias_new_friend agtype) ),

graph_query_2 as (

SELECT * FROM cypher('graph_name', $$ MATCH (n:Person)-[:FRIEND]->(m:Person)

WHERE n.name = 'Tobias' AND m.name <> 'Peter' RETURN m.name $$) as (Tobias_old_friend agtype)

)SELECT * FROM graph_query_1, graph_query_2;

Results:

Tobias_new_friend	Tobias_old_friend
'Peter'	‘Andres’
1 row(s) returned

Using Cypher with expressions

Cypher cannot be used in an expression, the query must exists in the FROM clause of a query. However, if the cypher query is placed in a Subquery, it will behave as any SQL style query.

Using Cypher with '='

When writing a cypher query that is known to return 1 column and 1 row, the '=' comparison operator may be used.

SELECT t.name FROM schema_name.sql_person AS twhere t.name = ( SELECT a FROM cypher('graph_name', $$ MATCH (v) RETURN v.name $$) as (name varchar(50)) ORDER BY name LIMIT 1);

Results:

name	age
‘Andres’	36
3 row(s) returned

Working with Postgres's IN Clause

When writing a cypher query that is known to return 1 column, but may have multiple rows. The IN operator may be used.

Query:

SELECT t.name, t.age FROM schema_name.sql_person as t

where t.name in (

SELECT *

FROM cypher('graph_name', $$

MATCH (v:Person)

RETURN v.name

$$) as (a agtype));

Results:

name	age
‘Andres’	36
‘Tobias’	25
‘Peter’	35
3 row(s) returned

Working with Postgres EXISTS Clause

When writing a cypher query that may have more than 1 column and row returned. The EXISTS operator may be used.

Query:

SELECT t.name, t.ageFROM schema_name.sql_person as tWHERE EXISTS ( SELECT * FROM cypher('graph_name', $$ MATCH (v:Person) RETURN v.name, v.age $$) as (name agtype, age agtype) WHERE name = t.name AND age = t.age );

Results:

name	age
‘Andres’	36
‘Tobias’	25
3 row(s) returned

Querying Multiple Graphs

There is no restriction to the number of graphs an SQL statement can query. Allowing users to query more than one graph at the same time.

SELECT graph_1.name, graph_1.age, graph_2.license_numberFROM cypher('graph_1', $$ MATCH (v:Person) RETURN v.name, v.age $$) as graph_1(col_1 agtype, col_2 agtype, col_3 agtype)JOIN cypher('graph_2', $$ MATCH (v:Doctor) RETURN v.name, v.license_number $$) as graph_2(name agtype, license_number agtype)ON graph_1.name = graph_2.name

Results:

name	age	license_number
‘Andres’	36	1234567890
3 row(s) returned