Subquery expressions

A subquery expression runs a query body once for every row of the enclosing query and reduces the rows that body produces to a single value. Memgraph supports three of them, which differ only in what they reduce to:

Expression	Returns	Body matched nothing
`EXISTS { … }`	Boolean	`false`
`COUNT { … }`	Integer	`0`
`COLLECT { … }`	List	`[]`

None of them ever returns null, so the result is always safe to compare, sort or aggregate on.

A subquery body can reference variables of the enclosing query, which makes it correlated — it is re-evaluated per row against the values that row holds. Variables the body introduces stay inside the body.

Dataset

The examples on this page run against a small graph of Person nodes joined by KNOWS, and Movie nodes they are joined to by ACTED_IN. Only some of the people have a nickname. You can create it locally by executing the queries at the end of the page: Dataset queries.

The three side by side

The same body, reduced three ways. Carol and Dave match nothing, so each expression falls back to its empty value rather than to null:

MATCH (p:Person)
RETURN p.name AS name,
       EXISTS  { MATCH (p)-[:ACTED_IN]->(m) }                AS acted,
       COUNT   { MATCH (p)-[:ACTED_IN]->(m) }                AS credits,
       COLLECT { MATCH (p)-[:ACTED_IN]->(m) RETURN m.title } AS titles
ORDER BY name;

Output:

+---------+-------+---------+----------------------------------------------+
| name    | acted | credits | titles                                       |
+---------+-------+---------+----------------------------------------------+
| "Alice" | true  | 3       | ["The Matrix", "Johnny Mnemonic", "Jumanji"] |
| "Bob"   | true  | 1       | ["Jumanji"]                                  |
| "Carol" | false | 0       | []                                           |
| "Dave"  | false | 0       | []                                           |
+---------+-------+---------+----------------------------------------------+

EXISTS

EXISTS { … } is true when its body produces at least one row. Over a single pattern it can be written as a bare pattern. With NOT it filters for the absence of a match:

MATCH (p:Person)
WHERE NOT EXISTS { MATCH (p)-[:KNOWS]->() }
RETURN p.name AS name
ORDER BY name;

Output:

+---------+
| name    |
+---------+
| "Carol" |
| "Dave"  |
+---------+

EXISTS versus a pattern expression

The exists(pattern) function and a bare pattern filter such as MATCH (n) WHERE (n)-->() are convenient for a simple existence check, but a pattern on its own cannot carry additional clauses. Reach for EXISTS { … } when the check needs a WHERE, a WITH with an aggregation, or more than one pattern part — as in supported clauses.

For EXISTS as a filter in the WHERE clause, see also Existential subqueries.

COUNT

COUNT { … } returns the number of rows its body produces, and over a single pattern it can be written as a bare pattern. Because the result is an integer, it can be compared directly in a WHERE:

MATCH (p:Person)
WHERE COUNT { MATCH (p)-[:KNOWS]->(f) } > 1
RETURN p.name AS name
ORDER BY name;

Output:

+---------+
| name    |
+---------+
| "Alice" |
+---------+

DISTINCT in the body counts distinct rows. Alice acted in three movies, but they were released in two distinct years:

MATCH (p:Person)
RETURN p.name AS name,
       COUNT { MATCH (p)-[:ACTED_IN]->(m) RETURN DISTINCT m.released } AS years
ORDER BY name;

Output:

+---------+-------+
| name    | years |
+---------+-------+
| "Alice" | 2     |
| "Bob"   | 1     |
| "Carol" | 0     |
| "Dave"  | 0     |
+---------+-------+

Prefer COUNT { … } to counting a pattern comprehension with size([(p)-[:KNOWS]->(f) | f]), which builds the whole list before measuring it.

COLLECT

COLLECT { … } returns the body’s single return column as a list, in the order the body produced its rows. Its body must end in a RETURN of exactly one column.

Because the body’s own order becomes the list’s order, ORDER BY inside the body sorts the list:

MATCH (p:Person)
RETURN p.name AS name,
       COLLECT { MATCH (p)-[:ACTED_IN]->(m) RETURN m.title ORDER BY m.title } AS titles
ORDER BY name;

Output:

+---------+----------------------------------------------+
| name    | titles                                       |
+---------+----------------------------------------------+
| "Alice" | ["Johnny Mnemonic", "Jumanji", "The Matrix"] |
| "Bob"   | ["Jumanji"]                                  |
| "Carol" | []                                           |
| "Dave"  | []                                           |
+---------+----------------------------------------------+

SKIP and LIMIT apply too, which is how you pick the top n per row:

MATCH (p:Person)
RETURN p.name AS name,
       COLLECT {
         MATCH (p)-[:ACTED_IN]->(m)
         RETURN m.title ORDER BY m.title LIMIT 1
       } AS first
ORDER BY name;

Output:

+---------+---------------------+
| name    | first               |
+---------+---------------------+
| "Alice" | ["Johnny Mnemonic"] |
| "Bob"   | ["Jumanji"]         |
| "Carol" | []                  |
| "Dave"  | []                  |
+---------+---------------------+

COLLECT compared with collect()

COLLECT { … } gathers every row its body returns, including the rows whose value is null. The collect() aggregation function drops them. Two of the four people have a nickname:

MATCH (p:Person)
RETURN collect(p.nickname) AS nicknames;

Output:

+-----------------+
| nicknames       |
+-----------------+
| ["Al", "Bobby"] |
+-----------------+

RETURN COLLECT { MATCH (p:Person) RETURN p.nickname ORDER BY p.name } AS nicknames;

Output:

+-----------------------------+
| nicknames                   |
+-----------------------------+
| ["Al", "Bobby", Null, Null] |
+-----------------------------+

Filter in the body to leave them out:

RETURN COLLECT {
         MATCH (p:Person) WHERE p.nickname IS NOT NULL
         RETURN p.nickname ORDER BY p.name
       } AS nicknames;

Output:

+-----------------+
| nicknames       |
+-----------------+
| ["Al", "Bobby"] |
+-----------------+

The COLLECT keyword does not reserve the name: collect remains usable as a variable, alias, property key and label, and collect(x) remains the list aggregation function.

Where you can use a subquery expression

EXISTS { … }, COUNT { … } and COLLECT { … } are accepted in the same positions:

a WITH or RETURN projection
a WHERE, whether it hangs off MATCH, OPTIONAL MATCH or WITH
an ORDER BY
a CASE expression
an argument of an aggregation function

Ordering on one — fewest credits first:

MATCH (p:Person)
RETURN p.name AS name
ORDER BY COUNT { MATCH (p)-[:ACTED_IN]->() } ASC, name;

Output:

+---------+
| name    |
+---------+
| "Carol" |
| "Dave"  |
| "Bob"   |
| "Alice" |
+---------+

Inside a CASE:

MATCH (p:Person)
RETURN p.name AS name,
       CASE WHEN EXISTS { MATCH (p)-[:ACTED_IN]->() } THEN 'actor' ELSE 'crew' END AS role
ORDER BY name;

Output:

+---------+---------+
| name    | role    |
+---------+---------+
| "Alice" | "actor" |
| "Bob"   | "actor" |
| "Carol" | "crew"  |
| "Dave"  | "crew"  |
+---------+---------+

A subquery expression in a projection alongside an aggregation becomes part of the grouping key, so it decides how the rows are grouped — here, people grouped by how many credits they have:

MATCH (p:Person)
RETURN COUNT { (p)-[:ACTED_IN]->() } AS credits, collect(p.name) AS people
ORDER BY credits;

Output:

+---------+-------------------+
| credits | people            |
+---------+-------------------+
| 0       | ["Carol", "Dave"] |
| 1       | ["Bob"]           |
| 3       | ["Alice"]         |
+---------+-------------------+

As an aggregation argument:

MATCH (p:Person)
RETURN sum(COUNT { MATCH (p)-[:KNOWS]->(f) }) AS total;

Output:

+-------+
| total |
+-------+
| 3     |
+-------+

The subquery body

Supported clauses

A body is a read-only query — it never writes to the graph. It matches with MATCH, filters with WHERE, aggregates through WITH, and may end in a RETURN; it may also be a UNION of such branches.

WITH lets the body aggregate and then filter on the aggregate — here, people who acted in at least two movies:

MATCH (p:Person)
WHERE EXISTS {
  MATCH (p)-[:ACTED_IN]->(m)
  WITH count(*) AS c
  WHERE c >= 2
}
RETURN p.name AS name
ORDER BY name;

Output:

+---------+
| name    |
+---------+
| "Alice" |
+---------+

A WHERE in the body can test relationship properties:

MATCH (p:Person)
WHERE EXISTS { MATCH (p)-[r:KNOWS]->(f) WHERE r.since < 2015 }
RETURN p.name AS name
ORDER BY name;

Output:

+---------+
| name    |
+---------+
| "Alice" |
+---------+

The bare pattern shorthand

EXISTS and COUNT accept a bare pattern in place of a full body, which is a shorter way to write a check or a count over one pattern. The pattern cannot introduce names of its own — a variable already bound outside may appear in it, and acts as a join:

MATCH (p:Person)
RETURN p.name AS name,
       EXISTS { (p)-[:KNOWS]->() } AS knows,
       COUNT  { (p)-[:ACTED_IN]->() } AS credits
ORDER BY name;

Output:

+---------+-------+---------+
| name    | knows | credits |
+---------+-------+---------+
| "Alice" | true  | 3       |
| "Bob"   | true  | 1       |
| "Carol" | false | 0       |
| "Dave"  | false | 0       |
+---------+-------+---------+

The shorthand holds a pattern and nothing else, so a filter or any other clause needs the MATCH form — COUNT { MATCH (p)-[r:KNOWS]->() WHERE r.since < 2015 }. COLLECT has no shorthand, since a bare pattern returns no column for it to gather.

Correlation with the enclosing query

Variables of the enclosing query are visible throughout the body, including after a WITH inside it. The correlation may live in the body’s WHERE rather than in its pattern:

MATCH (p:Person)
RETURN p.name AS name,
       COUNT { MATCH (f:Person) WHERE (p)-[:KNOWS]->(f) } AS friends
ORDER BY name;

Output:

+---------+---------+
| name    | friends |
+---------+---------+
| "Alice" | 2       |
| "Bob"   | 1       |
| "Carol" | 0       |
| "Dave"  | 0       |
+---------+---------+

A body need not be correlated at all. An uncorrelated body produces the same value for every row:

RETURN COUNT { MATCH (m:Movie) } AS movies,
       COLLECT { MATCH (m:Movie) RETURN m.title ORDER BY m.title } AS titles;

Output:

+--------+----------------------------------------------+
| movies | titles                                       |
+--------+----------------------------------------------+
| 3      | ["Johnny Mnemonic", "Jumanji", "The Matrix"] |
+--------+----------------------------------------------+

Variables introduced inside the body are scoped to it and are not available after the subquery expression.

The body’s RETURN decides the result

The body is a query, and its final RETURN shapes the row set that the expression reduces. DISTINCT, SKIP, LIMIT and aggregation in the body all count.

This matters most for aggregation. An aggregating RETURN produces one row even when nothing matched, so an EXISTS over it is always true:

MATCH (p:Person)
RETURN p.name AS name,
       EXISTS { MATCH (p)-[:ACTED_IN]->(m) RETURN count(m) } AS v
ORDER BY name;

Output:

+---------+------+
| name    | v    |
+---------+------+
| "Alice" | true |
| "Bob"   | true |
| "Carol" | true |
| "Dave"  | true |
+---------+------+

To test whether anything matched, leave the aggregation out and let the MATCH decide, as in the EXISTS section.

EXISTS and COUNT do not require a RETURN at all; COLLECT does, because it needs a column to gather.

UNION

A body may be a UNION of branches. UNION reduces over the branches’ rows with duplicates removed, while UNION ALL keeps every row — so for COUNT and COLLECT the choice decides the answer:

MATCH (p:Person)
RETURN p.name AS name,
       COUNT {
         MATCH (p)-[:KNOWS]->(x) RETURN x
         UNION
         MATCH (p)-[:ACTED_IN]->(x) RETURN x
       } AS related
ORDER BY name;

Output:

+---------+---------+
| name    | related |
+---------+---------+
| "Alice" | 5       |
| "Bob"   | 2       |
| "Carol" | 0       |
| "Dave"  | 0       |
+---------+---------+

Every branch must return its one column under the same name. Alice and Bob both know Carol, so UNION reports her once:

MATCH (p:Person {name: 'Alice'})
RETURN COLLECT {
         MATCH (p)-[:KNOWS]->(f) RETURN f.name AS n
         UNION
         MATCH (:Person {name: 'Bob'})-[:KNOWS]->(f) RETURN f.name AS n
       } AS people;

Output:

+------------------+
| people           |
+------------------+
| ["Bob", "Carol"] |
+------------------+

The same body with UNION ALL keeps the repeat:

MATCH (p:Person {name: 'Alice'})
RETURN COLLECT {
         MATCH (p)-[:KNOWS]->(f) RETURN f.name AS n
         UNION ALL
         MATCH (:Person {name: 'Bob'})-[:KNOWS]->(f) RETURN f.name AS n
       } AS people;

Output:

+---------------------------+
| people                    |
+---------------------------+
| ["Bob", "Carol", "Carol"] |
+---------------------------+

Nesting

A body may itself contain subquery expressions. Here EXISTS filters on a nested EXISTS — people who know an actor:

MATCH (p:Person)
WHERE EXISTS {
  MATCH (p)-[:KNOWS]->(f)
  WHERE EXISTS { MATCH (f)-[:ACTED_IN]->() }
}
RETURN p.name AS name
ORDER BY name;

Output:

+---------+
| name    |
+---------+
| "Alice" |
+---------+

They can also be combined, for example collecting a per-friend count:

MATCH (p:Person)
RETURN p.name AS name,
       COLLECT {
         MATCH (p)-[:KNOWS]->(f)
         RETURN COUNT { (f)-[:ACTED_IN]->() } ORDER BY f.name
       } AS friendMovies
ORDER BY name;

Output:

+---------+--------------+
| name    | friendMovies |
+---------+--------------+
| "Alice" | [1, 0]       |
| "Bob"   | [0]          |
| "Carol" | []           |
| "Dave"  | []           |
+---------+--------------+

Dataset queries

We encourage you to try out the examples by yourself. You can get the dataset locally by executing the following query block.

MATCH (n) DETACH DELETE n;
 
CREATE (alice:Person {name: 'Alice', nickname: 'Al'});
CREATE (bob:Person {name: 'Bob', nickname: 'Bobby'});
CREATE (carol:Person {name: 'Carol'});
CREATE (dave:Person {name: 'Dave'});
CREATE (:Movie {title: 'The Matrix', released: 1999});
CREATE (:Movie {title: 'Jumanji', released: 1995});
CREATE (:Movie {title: 'Johnny Mnemonic', released: 1995});
MATCH (a:Person {name: 'Alice'}), (b:Person {name: 'Bob'})
CREATE (a)-[:KNOWS {since: 2010}]->(b);
MATCH (a:Person {name: 'Alice'}), (c:Person {name: 'Carol'})
CREATE (a)-[:KNOWS {since: 2015}]->(c);
MATCH (b:Person {name: 'Bob'}), (c:Person {name: 'Carol'})
CREATE (b)-[:KNOWS {since: 2018}]->(c);
MATCH (a:Person {name: 'Alice'}), (m:Movie {title: 'The Matrix'})
CREATE (a)-[:ACTED_IN]->(m);
MATCH (a:Person {name: 'Alice'}), (m:Movie {title: 'Johnny Mnemonic'})
CREATE (a)-[:ACTED_IN]->(m);
MATCH (a:Person {name: 'Alice'}), (m:Movie {title: 'Jumanji'})
CREATE (a)-[:ACTED_IN]->(m);
MATCH (b:Person {name: 'Bob'}), (m:Movie {title: 'Jumanji'})
CREATE (b)-[:ACTED_IN]->(m);

Expressions Schema