Combining SQL and Cypher#

There is no bridge between SQL and Cypher in AgensGraph, because there is nothing to bridge. A graph is a set of PostgreSQL tables in the same database as your ordinary tables, and both languages compile to the same plan tree.

So the two mix by nesting: a Cypher query goes where SQL expects a table, and a SQL query goes where Cypher expects a value.

This page walks the crossing points, in the order you are likely to need them, and then the one rule that surprises everybody: what a Cypher RETURN hands back.

Everything runs against the example dataset, which includes an ordinary table, rating, that is not part of the graph.

Reading a label with plain SQL#

A label is a table, so no Cypher is needed to read one:

SELECT id, properties->>'title' AS title, properties->>'year' AS year
FROM moviekb.movie ORDER BY (properties->>'year')::int LIMIT 3;

  id  |     title      | year 
------+----------------+------
 3.8  | Blade Runner   | 1982
 3.15 | The Terminator | 1984
 3.11 | Total Recall   | 1990

Nothing needs enabling for that. Writing rows into a label with SQL does; see What is deliberately closed off. Reading is just reading.

Direct SQL is the right tool when the question is relational: counts per label, a join to a reference table on a property value, a window function over a property. It is the wrong tool for anything with a variable number of hops. The rest of this page covers those.

A Cypher query in a SQL FROM#

Put a Cypher query in parentheses where a SQL FROM expects a table, and give it an alias. Its RETURN list becomes the columns:

SELECT * FROM (MATCH (m:movie) WHERE m.year > 2015 RETURN m.title AS title, m.year AS year) q
ORDER BY year;

           title            | year 
----------------------------+------
 "Blade Runner 2049"        | 2017
 "The Matrix Resurrections" | 2021

The alias is required:

SELECT count(*) FROM (MATCH (m:movie) RETURN m.title AS title);

ERROR:  Cypher in FROM must have an alias
HINT:  For example, FROM (...) [AS] foo.

Two more rules. Every column needs a name, because the outer query has to be able to refer to it. So RETURN m.title without AS is refused. And the nested query must be a read: a write clause inside a FROM is a syntax error.

You can nest more than one, and join them like any two subqueries:

SELECT d.name #>> '{}' AS director, a.name #>> '{}' AS actor
FROM (MATCH (p:person)-[:directed]->(:movie {title: 'The Matrix'}) RETURN p.name AS name) d
CROSS JOIN (MATCH (p:person)-[:acted_in]->(:movie {title: 'The Matrix'}) RETURN p.name AS name) a
ORDER BY director, actor LIMIT 4;

    director     |       actor        
-----------------+--------------------
 Lana Wachowski  | Carrie-Anne Moss
 Lana Wachowski  | Keanu Reeves
 Lana Wachowski  | Laurence Fishburne
 Lilly Wachowski | Carrie-Anne Moss

Joining a graph to an ordinary table#

This is the reason most people come to this page. Ask the graph a graph question, then join its answer to relational data:

SELECT q.title #>> '{}' AS film, r.score
FROM (MATCH (:person {name: 'Christopher Nolan'})-[:directed]->(m:movie) RETURN m.title AS title) q
JOIN rating r ON r.title = q.title #>> '{}'
WHERE r.source = 'critics'
ORDER BY r.score DESC;

     film     | score 
--------------+-------
 Inception    |   8.8
 Interstellar |   8.7

The #>> '{}' on both sides is the whole trick, and the next section explains it. Without it you are comparing a jsonb string against a text column, which does not type-check.

A graph and a table have no foreign keys between them, so the join key is a value: a title, a code, an external id. If you control the model, store that id as a property and index it.

If the join is hot, promote it to a column: a text column joins to a text column with no casting at all. See Promoting a property to a column.

A Cypher query in a CTE#

A WITH clause takes a Cypher query as easily as a SELECT. That gives you a name you can reference more than once:

WITH films AS (MATCH (m:movie) WHERE m.year > 2010 RETURN m.title AS title, m.year AS year)
SELECT f.title #>> '{}' AS title, r.score
FROM films f JOIN rating r ON r.title = f.title #>> '{}'
WHERE r.source = 'critics' ORDER BY r.score DESC;

          title           | score 
--------------------------+-------
 Interstellar             |   8.7
 Blade Runner 2049        |   8.0
 The Avengers             |   8.0
 Elysium                  |   6.6
 The Matrix Resurrections |   5.7

Note which language the statement is in. A statement beginning with WITH cte AS (...) is SQL, and its body has to be SQL. This is not Cypher’s WITH, which is a pipeline divider and cannot name a subquery. See How they differ.

The pattern generalises to aggregating a traversal relationally. Here Cypher counts each film’s cast and SQL averages the scores:

WITH cast_size AS (
  MATCH (p:person)-[:acted_in]->(m:movie) RETURN m.title AS title, count(p) AS players
)
SELECT c.title #>> '{}' AS film, c.players #>> '{}' AS players, round(avg(r.score), 2) AS mean_score
FROM cast_size c JOIN rating r ON r.title = c.title #>> '{}'
GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 4;

            film            | players | mean_score 
----------------------------+---------+------------
 The Matrix                 | 3       |       8.90
 Inception                  | 1       |       8.90
 Interstellar               | 1       |       8.65
 Terminator 2: Judgment Day | 2       |       8.60

SQL inside a Cypher query#

The nesting works in the other direction too. A SQL scalar subquery is an expression, so it goes anywhere Cypher wants one: in a WHERE, in a RETURN, in a property value being written:

MATCH (m:movie)
WHERE m.title = (SELECT title FROM rating WHERE source = 'critics' ORDER BY score DESC LIMIT 1)
RETURN m.title AS title, m.year AS year;

    title    | year 
-------------+------
 "Inception" | 2010

The subquery must return one row and one column, as in SQL. No cast was needed here: the subquery’s text was compared against a jsonb property, and the comparison resolved. The other direction is the one that needs #>> '{}': a jsonb property into a text column.

A user-defined function is an expression as well, so a SQL function can be called from a Cypher RETURN:

CREATE FUNCTION decade(y int) RETURNS int AS 'SELECT (y / 10) * 10' LANGUAGE sql IMMUTABLE;

MATCH (m:movie) RETURN decade(m.year::int) AS decade, count(*) AS films ORDER BY decade;

 decade | films 
--------+-------
 1980   | 2
 1990   | 6
 2000   | 4
 2010   | 5
 2020   | 1

The ::int on m.year turns the jsonb property into an argument the function’s signature accepts. Any language works: PL/pgSQL, PL/Python, C. So does any aggregate or window function, because Cypher’s aggregates are PostgreSQL’s.

Getting a plain value out#

Anything a Cypher RETURN derives from a property comes back as jsonb. That is not a display quirk; it is the column’s type. Casting or unwrapping inside the query does not change it, because the projection re-boxes the result.

This is the single most common source of confusion at this boundary, so here it is from every angle.

Not every column is boxed, only the property-derived ones. Checking the outer types of a Cypher subquery’s columns shows the rule exactly:

SELECT pg_typeof(raw) AS raw, pg_typeof(cast_text) AS cast_text,
       pg_typeof(unwrapped) AS unwrapped, pg_typeof(is_modern) AS boolean_expr,
       pg_typeof(gid) AS gid, pg_typeof(v) AS vertex
FROM (MATCH (m:movie {title: 'The Matrix'})
      RETURN m.title AS raw, m.title::text AS cast_text, m.title #>> '{}' AS unwrapped,
             m.year > 1990 AS is_modern, id(m) AS gid, m AS v) q;

  raw  | cast_text | unwrapped | boolean_expr |   gid   | vertex
-------+-----------+-----------+--------------+---------+--------
 jsonb | jsonb     | jsonb     | boolean      | graphid | vertex

So a boolean expression stays boolean, id(m) stays graphid, and a graph element stays vertex, edge or graphpath. The three property-derived columns arrive as jsonb. That includes the one that was cast to text and the one that was already unwrapped.

A string property comes back JSON-quoted:

MATCH (m:movie {title: 'The Matrix'}) RETURN m.title AS t;

      t       
--------------
 "The Matrix"

Casting inside the Cypher query does not help, because the outer projection re-boxes the result:

MATCH (m:movie {title: 'The Matrix'}) RETURN m.title::text AS t;

      t       
--------------
 "The Matrix"

SELECT pg_typeof(t) AS type FROM (MATCH (m:movie {title: 'The Matrix'}) RETURN m.title::text AS t) q;

 type  
-------
 jsonb

Neither does extracting the text with #>> '{}' inside Cypher, for the same reason:

MATCH (m:movie {title: 'The Matrix'}) RETURN m.title #>> '{}' AS t;

      t       
--------------
 "The Matrix"

The way out is to do the unwrapping in SQL, one level up. Wrap the Cypher query and apply #>> '{}' outside it. That operator is jsonb’s “extract the value at the empty path as text”:

SELECT t #>> '{}' AS t FROM (MATCH (m:movie {title: 'The Matrix'}) RETURN m.title AS t) q;

     t      
------------
 The Matrix

SELECT pg_typeof(t #>> '{}') AS type FROM (MATCH (m:movie {title: 'The Matrix'}) RETURN m.title AS t) q;

 type 
------
 text

From there the value behaves like any text: compare it to a column, pass it to a function, cast it onwards with (q.year #>> '{}')::int for a number.

Three notes on this rule.

It applies to the outermost projection only. A property read within a Cypher expression keeps its type all the way through. So decade(m.year::int) above works.

A promoted property is the exception. A property with a typed column behind it comes back as that column’s type, not as jsonb. So RETURN m.year on a promoted year is already an integer and needs no unwrapping. See A promoted read has the column’s type.

A vertex or an edge comes back as its own composite type, not as jsonb, and SQL can take it apart with field notation:

SELECT pg_typeof(m) AS type, (m).id AS id, (m).properties->>'title' AS title
FROM (MATCH (m:movie {title: 'The Matrix'}) RETURN m) q;

  type  | id  |   title    
--------+-----+------------
 vertex | 3.1 | The Matrix

The parentheses around m are SQL’s, required whenever you read a field of a composite column.

A view over a Cypher query#

A view cannot begin with a Cypher clause:

CREATE VIEW film_rating AS MATCH (m:movie) RETURN m.title AS title;

ERROR:  syntax error at or near "MATCH"

Wrap it in a SELECT and it works. So a graph traversal can be published as a relational view for anything that speaks SQL: a BI tool, an ORM, a foreign data wrapper:

CREATE VIEW film_rating AS
SELECT q.title #>> '{}' AS title, (q.year #>> '{}')::int AS year, r.score
FROM (MATCH (m:movie) RETURN m.title AS title, m.year AS year) q
JOIN rating r ON r.title = q.title #>> '{}'
WHERE r.source = 'critics';

SELECT * FROM film_rating ORDER BY score DESC LIMIT 4;

           title            | year | score 
----------------------------+------+-------
 Inception                  | 2010 |   8.8
 Interstellar               | 2014 |   8.7
 The Matrix                 | 1999 |   8.7
 Terminator 2: Judgment Day | 1991 |   8.6

The view is an ordinary view, so it lands in the SQL search path rather than in the graph. graph_path says which graph a label name belongs to; it says nothing about where a view goes:

SELECT schemaname, viewname FROM pg_views WHERE viewname = 'film_rating';

 schemaname |  viewname   
------------+-------------
 public     | film_rating

What the server stored#

A view holds a parsed query tree, not your text. Ask for the definition back and you get the lowered SQL the Cypher compiled to, not the Cypher you wrote:

SELECT pg_get_viewdef('public.film_rating'::regclass, true);

                            pg_get_viewdef                             
-----------------------------------------------------------------------
  SELECT q.title #>> '{}'::text[] AS title,                           +
     (q.year #>> '{}'::text[])::integer AS year,                      +
     r.score                                                          +
    FROM ( SELECT (_agens_default_s.m).properties.'title' AS title,   +
             (_agens_default_s.m).properties.'year' AS year           +
            FROM ( SELECT ROW(m.id, m.properties, m.ctid)::vertex AS m+
                    FROM moviekb.movie m) _agens_default_s) q         +
      JOIN rating r ON r.title = (q.title #>> '{}'::text[])           +
   WHERE r.source = 'critics'::text;

That is worth reading once, because it is the clearest possible statement of what a Cypher query is on this engine. MATCH (m:movie) became a scan of moviekb.movie whose rows are assembled into a vertex value. m.title became (m).properties.'title'. The names beginning _agens_default_s are the generated aliases for the pipeline’s stages.

Two consequences follow.

Your Cypher is not recoverable from the catalog. Keep the source of a view under version control, the way you would any other schema object. This is not specific to views. Anything built on a stored query tree records the lowered form: a rule, a materialised view.

It round-trips. pg_dump emits that same lowered SQL, and restoring it rebuilds a working view. The definition after a dump and restore into a fresh database is identical, character for character, and the view returns the same rows.

So a view over a Cypher query is safe to rely on operationally, even though it does not read back as you wrote it.

One thing to watch: if the Cypher resolved a property to a promoted column, the view holds a dependency on that column. Dropping the column is refused until the view goes. That is deliberate, because the view would otherwise silently change meaning.

Clean up#

DROP VIEW film_rating;
DROP FUNCTION decade(int);

Where to go next#