The cypher() function can be embedded in several parts of an SQL query. This page shows how to use Cypher inside a common table expression (CTE), a JOIN clause, and SQL subexpressions such as =, IN, and EXISTS. It also covers querying multiple graphs in a single statement.
Use Cypher in a CTE
There are no restrictions on using Cypher inside a common table expression (CTE).
Example
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;Sample result:
name | age
-------------------+-----
"Andres" | 36
"Tobias" | 25
"Peter" | 35
(3 rows)Use Cypher in a JOIN clause
A Cypher query can be part of a JOIN clause.
Cypher queries that use CREATE, SET, or REMOVE cannot be used in JOINs because these clauses affect the PostgreSQL transaction system.
Example
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;Sample result:
id | names_match | ages_match
----+-------------+------------
1 | True | True
2 | False | True
3 | True | False
(3 rows)Use Cypher in a SQL expression
Cypher cannot be used directly as a SQL expression. The cypher() function must appear in a FROM clause. When placed in a subquery, it behaves like any other SQL subquery.
Place the cypher() call in a subquery within the FROM clause, then apply =, IN, or EXISTS as needed.
Use Cypher with =
Use = when the Cypher query returns exactly one column and one row.
SELECT t.name, t.age
FROM schema_name.sql_person AS t
WHERE t.name = (
SELECT name
FROM cypher('graph_name', $$
MATCH (v)
RETURN v.name
$$) AS (name varchar(50))
ORDER BY name
LIMIT 1
);Sample result:
name | age
-------------------+-----
"Andres" | 36
(1 row)Use Cypher with IN
Use IN when the Cypher query returns one column with potentially multiple rows.
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 (name agtype)
);Sample result:
name | age
-------------------+-----
"Andres" | 36
"Tobias" | 25
"Peter" | 35
(3 rows)Use Cypher with EXISTS
Use EXISTS when the Cypher query may return multiple columns and rows.
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
);Sample result:
name | age
-------------------+-----
"Andres" | 36
"Tobias" | 25
(2 rows)Query multiple graphs
A single SQL statement can query any number of graphs simultaneously. The following example joins results from two separate graphs.
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(name agtype, age 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;Sample result:
name | age | license_number
-------------+-----+----------------
"Andres" | 36 | 1234567890
(1 row)