Release History
Help

YMatrix

Quick Start

Deployment

Data Usage

Manage Clusters

Security

Best Practice

Technical Principles

Advanced Features

Graph Database

Guide

SQL Reference

FAQ

Using Cypher in CTEs, JOINs, and SQL Expressions

Using Cypher in CTEs

There are no restrictions on using Cypher within a Common Table Expression (CTE).

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;

Result

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

Using Cypher in JOINs

Cypher queries may be used as a source in a JOIN clause.

Note!
Cypher queries containing CREATE, SET, or REMOVE clauses must not be used in SQL queries with JOIN. Such operations interfere with the PostgreSQL transaction system. A possible workaround is to wrap the Cypher query in a CTE. For details, see "Using CTEs with CREATE, REMOVE, and SET".

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;

Result

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

Using Cypher in SQL Expressions

Cypher cannot be used directly inside SQL expressions — the Cypher query must appear in the FROM clause of a query. However, when wrapped in a subquery, it behaves like any standard SQL query.

Note!
In YMatrix AGE, Cypher queries containing CREATE, SET, or REMOVE clauses must not be used in SQL write statements such as INSERT, DELETE, or UPDATE. For example: 

INSERT INTO t  
SELECT * FROM cypher('graph_name', $$  
    CREATE (a)  
    RETURN a  
$$) AS (a agtype);

Using Cypher with =

When the Cypher query is known to return exactly one column and one row, the = comparison operator may be used.

SELECT t.name, t.age
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
);

Result

name age
'Andres' 36
1 row(s) returned

Using Cypher with PostgreSQL’s IN

When the Cypher query returns a single column but potentially multiple rows, use the IN operator.

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)
);

Result

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

Using Cypher with PostgreSQL’s EXISTS

When the Cypher query may return multiple columns and multiple rows, use the EXISTS operator.

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
);

Result

name age
'Andres' 36
'Tobias' 25
2 row(s) returned

Querying Multiple Graphs

There is no limit to the number of graphs a single SQL statement can query. Users may query multiple graphs concurrently.

Query

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;

Result

name age license_number
'Andres' 36 1234567890
1 row(s) returned
← Previous
Overview
Next →
PL/pgSQL Functions