Composing a query#

A Cypher or GQL statement is a pipeline. Each clause takes a stream of rows, does one thing to it, and hands the result to the next clause. MATCH produces rows, WHERE removes some, WITH reshapes them, RETURN ends the statement.

That is a different mental model from SQL, where a SELECT is a single block with named parts evaluated in a fixed order. Here the order is the order you wrote. A query can match, aggregate, filter the aggregate, match again from the survivors, and aggregate again, with no subquery, no CTE and no HAVING.

Everything runs against the example dataset.

WITH is the divider#

WITH ends one part of a pipeline and begins the next. It takes a projection list exactly like RETURN, and only what it lists stays in scope:

MATCH (d:person)-[:directed]->(m:movie)
WITH d, count(m) AS films
WHERE films > 2
RETURN d.name AS director, films ORDER BY films DESC, director;

     director      | films 
-------------------+-------
 "Lana Wachowski"  | 4
 "James Cameron"   | 3
 "Lilly Wachowski" | 3

m is gone after the WITH; d and films are what is left. The WHERE after it filters the aggregate, the job SQL needs HAVING for.

Order inside a WITH is fixed and worth committing to memory: WHERE first, then ORDER BY, then SKIP and LIMIT. So this filters to five films and then takes two of them, rather than taking two films and then filtering:

MATCH (m:movie) WITH m.year AS y WHERE y > 2010 ORDER BY y LIMIT 2 RETURN y;

  y   
------
 2012
 2013

A name may be passed on only once:

MATCH (m:movie) WITH m.year AS y, m.genre AS y RETURN y;

ERROR:  WITH passes on "y" more than once

How it differs from a CTE#

Both WITH keywords divide a query, and they are not the same keyword.

A SQL CTE is named and referenced: you declare WITH films AS (...) and then mention films wherever you want it, as many times as you like, in any order.

A Cypher WITH is positional: it has no name, it is referenced by being next, and it is consumed exactly once. There is no way to fork a Cypher pipeline into two branches and rejoin them.

They do not mix. Cypher’s WITH cannot introduce a named subquery: WITH d AS (MATCH ...) is a syntax error. But a SQL CTE can hold a Cypher query, which is the way to get the CTE behaviour when you want it:

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

The outer statement there is SQL, because a statement that begins with a SQL CTE is a SQL statement. Combining SQL and Cypher is the page for that boundary.

That is also why a Cypher pipeline cannot begin with WITH. A leading WITH is read as a CTE, so the parser is looking for a name and a parenthesised query:

WITH 1 AS x RETURN x;

ERROR:  syntax error at or near "1"
LINE 1: WITH 1 AS x RETURN x;
             ^

LET has no such collision, so it is the clause to open a statement with a computed value:

LET x = 1 RETURN x;

 x
---
 1

Aggregation#

Aggregates are PostgreSQL’s aggregates, and grouping is implicit: anything in the projection list that is not inside an aggregate becomes a grouping key.

MATCH (m:movie) RETURN m.genre AS genre, count(*) AS films, min(m.year) AS earliest ORDER BY genre;

    genre    | films | earliest 
-------------+-------+----------
 "Action"    | 7     | 1984
 "Adventure" | 1     | 1993
 "Sci-Fi"    | 10    | 1982

count(*) counts rows; count(x) counts rows where x is not null. That makes count(x) useful after an OPTIONAL MATCH. count(DISTINCT x) counts distinct values, and the gap between the two is usually the fan-out of your pattern:

MATCH (:person)-[:acted_in]->(m:movie) RETURN count(m) AS rows, count(DISTINCT m) AS films;

 rows | films 
------+-------
 25   | 18

collect() is the aggregate you will reach for most, because it turns a fan-out back into one row. Over a vertex, an edge or a path it builds a native array of those elements, so the collected elements stay usable:

MATCH (p:person)-[:acted_in]->(m:movie {title: 'The Matrix'})
WITH collect(p) AS players
RETURN pg_typeof(players) AS type, cardinality(players) AS n, players[1].name AS first;

    type    | n |        first         
------------+---+----------------------
 "vertex[]" | 3 | "Laurence Fishburne"

collect() over a property still returns a jsonb list, so stored data keeps its shape.

DISTINCT on the projection as a whole deduplicates rows, and is what you want after a pattern that reaches the same thing by several routes:

MATCH (:person {name: 'Keanu Reeves'})-[:acted_in]->(:movie)<-[:acted_in]-(co:person)
RETURN DISTINCT co.name AS co_actor ORDER BY co_actor;

       co_actor
----------------------
 "Carrie-Anne Moss"
 "Laurence Fishburne"

UNWIND#

UNWIND is the opposite of collect(): it takes a list and produces one row per element.

UNWIND [1999, 2010, 2014] AS y MATCH (m:movie {year: y}) RETURN y, m.title AS title ORDER BY y, title;

  y   |     title      
------+----------------
 1999 | "The Matrix"
 2010 | "Inception"
 2014 | "Interstellar"

It also unpacks a list you built earlier in the same pipeline, which is how you filter on an aggregate and then get back to one row per element:

MATCH (m:movie)<-[:acted_in]-(p:person)
WITH m, collect(p) AS players
WHERE cardinality(players) > 2
UNWIND players AS actor
RETURN m.title AS film, actor.name AS actor ORDER BY actor;

     film     |        actor         
--------------+----------------------
 "The Matrix" | "Carrie-Anne Moss"
 "The Matrix" | "Keanu Reeves"
 "The Matrix" | "Laurence Fishburne"

FOR is the GQL spelling, and it adds WITH OFFSET for the element’s 0-based index. See UNWIND.

Ordering and paging#

ORDER BY, SKIP, OFFSET and LIMIT are clauses in their own right. They can sit at the end of a RETURN or a WITH, as in SQL:

MATCH (m:movie) RETURN m.title AS title, m.year AS year ORDER BY year SKIP 2 LIMIT 3;

            title             | year 
------------------------------+------
 "Total Recall"               | 1990
 "Terminator 2: Judgment Day" | 1991
 "Jurassic Park"              | 1993

or stand alone in the middle of a pipeline, which the RETURN form cannot do:

MATCH (m:movie) LET y = m.year ORDER BY y LIMIT 3 RETURN m.title AS title, y;

      title       |  y   
------------------+------
 "Blade Runner"   | 1982
 "The Terminator" | 1984
 "Total Recall"   | 1990

They cannot end a statement: a query still has to finish with RETURN, FINISH or a write clause.

A sort key is ordered as the value it is, not as its jsonb picture, so an index on the property can serve the ordering. That is the whole reason to care: Indexes and constraints.

UNION#

UNION stacks two result sets that have the same column names, and deduplicates. UNION ALL keeps duplicates.

MATCH (m:movie {title: 'The Matrix'}) RETURN m.title AS name
UNION
MATCH (p:person {name: 'Keanu Reeves'}) RETURN p.name AS name;

      name      
----------------
 "Keanu Reeves"
 "The Matrix"

This is also the tidy way round the missing edge-label alternation: two narrow patterns unioned scan two edge tables, where one untyped pattern scans them all. See Reading a graph.

The GQL clauses#

Four ISO/IEC 39075 clauses do jobs that Cypher needs several clauses for. They are accepted in the same statements as everything else, and there is no separate mode.

LET adds computed values without dropping anything in scope. Where WITH makes you list every variable you still want, LET keeps them all:

MATCH (m:movie) LET decade = (m.year / 10) * 10 FILTER decade = 2010
RETURN m.title AS title, m.year AS year, decade ORDER BY year;

        title        | year | decade 
---------------------+------+--------
 "Inception"         | 2010 | 2010
 "The Avengers"      | 2012 | 2010
 "Elysium"           | 2013 | 2010
 "Interstellar"      | 2014 | 2010
 "Blade Runner 2049" | 2017 | 2010

FILTER filters rows and nothing else, so a filter no longer has to be attached to a WITH. It takes a bare predicate or the FILTER WHERE spelling.

NEXT chains one query onto the result of another, which is how you filter after a projection without a WITH:

MATCH (m:movie) RETURN m.genre AS genre, count(*) AS films
NEXT FILTER films > 3
RETURN genre, films ORDER BY genre;

  genre   | films 
----------+-------
 "Action" | 7
 "Sci-Fi" | 10

FINISH ends a query that projects nothing. It is the counterpart of RETURN for a statement run for its effect:

MATCH (m:movie {title: 'The Matrix'}) FINISH;

--
(0 rows)

Each has a reference page: LET, FILTER, NEXT, FINISH.

Subqueries#

CALL { } runs a subquery once per incoming row and joins its result back. Use it when the thing you want per row is a whole query rather than an expression: a per-row aggregate, a per-row top-N.

Everything about it is ordinary except one thing, and it changes the answer rather than raising an error.

Important

A CALL { } with no import list does not import the outer variables. A name used inside the body that also exists outside is rebound to a fresh, unconstrained variable, so the subquery runs over the whole graph instead of over the current row. Always write the import list.

Here is the difference. m is bound outside, the body mentions m, and the answer is the same for every film:

MATCH (d:person)-[:directed]->(m:movie)
CALL { MATCH (a:person)-[:acted_in]->(m) RETURN count(a) AS cast_size }
RETURN m.title AS film, cast_size ORDER BY film LIMIT 3;

        film         | cast_size 
---------------------+-----------
 "Avatar"            | 25
 "Blade Runner"      | 25
 "Blade Runner 2049" | 25

25 is every acted_in edge in the graph. The subquery’s m was not the outer m.

Naming the import fixes it:

MATCH (d:person)-[:directed]->(m:movie)
CALL (m) { MATCH (a:person)-[:acted_in]->(m) RETURN count(a) AS cast_size }
RETURN m.title AS film, cast_size ORDER BY film LIMIT 3;

        film         | cast_size 
---------------------+-----------
 "Avatar"            | 1
 "Blade Runner"      | 1
 "Blade Runner 2049" | 2

CALL (*) { ... } imports everything in scope, and CALL { WITH m ... } is the older spelling of the same thing. All three agree.

A subquery may not hand back a name that is already bound outside it:

MATCH (m:movie) CALL (m) { MATCH (p:person)-[:acted_in]->(m) RETURN m AS m } RETURN count(*);

ERROR:  variable "m" returned by the CALL subquery is already bound in the outer query

Write clauses are not allowed in a subquery body. OPTIONAL CALL is the left-join form, producing a row even when the body yields nothing. See CALL { } and OPTIONAL CALL.

Subqueries as values#

Six value expressions take a subquery and reduce it to something you can put in a WHERE or a RETURN. They need no import list, because the outer variables are in scope as expressions rather than as a separate query.

EXISTS { } is true when the subquery returns any row:

MATCH (p:person) WHERE EXISTS { MATCH (p)-[:directed]->(:movie) } RETURN count(p) AS directors;

 directors 
-----------
 12

COUNT { } is how many rows it returns:

MATCH (m:movie) WHERE COUNT { MATCH (:person)-[:acted_in]->(m) } > 2
RETURN m.title AS title ORDER BY title;

    title     
--------------
 "The Matrix"

COLLECT { } and ARRAY { } gather its single column into a list:

MATCH (m:movie {title: 'The Matrix'})
RETURN COLLECT { MATCH (p:person)-[:acted_in]->(m) RETURN p.name ORDER BY p.name } AS players;

                          players                           
------------------------------------------------------------
 ["Carrie-Anne Moss", "Keanu Reeves", "Laurence Fishburne"]

VALUE { } takes its single value:

MATCH (m:movie {title: 'The Matrix'})
RETURN VALUE { MATCH (p:person)-[:directed]->(m) RETURN p.name ORDER BY p.name LIMIT 1 } AS a_director;

    a_director    
------------------
 "Lana Wachowski"

and x IN { } tests membership of its result:

MATCH (p:person)
WHERE p.name IN { MATCH (d:person)-[:directed]->(:movie {title: 'Inception'}) RETURN d.name }
RETURN p.name AS name;

        name         
---------------------
 "Christopher Nolan"

EXISTS { } is the one you will use most, because a bare pattern is not a predicate. See Reading a graph. All six are defined in Subquery expressions.

Where to go next#