Using Cypher in a CTE Expression

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

Using Cypher in a Join expression

A Cypher query can be part of a JOIN clause.

Developers Note
Cypher 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_match
FROM 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

Cypher in SQL 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 t
where 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.age
FROM schema_name.sql_person as t
WHERE 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_number
FROM 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