CALL {} (subquery)

Introduction

CALL allows to execute subqueries, i.e. queries inside of other queries. Subqueries allow you to compose queries, which is especially useful when working with UNION or aggregations.

The CALL clause is also used for calling procedures. For descriptions of the CALL clause in this context, refer to CALL procedure.

A subquery is evaluated for each incoming input row and may produce an arbitrary number of output rows. Every output row is then combined with the input row to build the result of the subquery. That means that a subquery will influence the number of rows. If the subquery does not return any rows, there will be no rows available after the subquery.

There are restrictions on what queries are allowed as subqueries and how they interact with the enclosing query:

  • A subquery must end with a RETURN clause.

  • A subquery can only refer to variables from the enclosing query if they are explicitly imported.

  • A subquery cannot return variables with the same names as variables in the enclosing query.

  • All variables that are returned from a subquery are afterwards available in the enclosing query.

The following graph is used for the examples below:

Graph
  N0 [
    label = "{Person, Child|name = \'Alice\'\lage = 20\l}"
  ]
  N0 -> N2 [
    color = "#2e3436"
    fontcolor = "#2e3436"
    label = "CHILD_OF\n"
  ]
  N0 -> N1 [
    color = "#4e9a06"
    fontcolor = "#4e9a06"
    label = "FRIEND_OF\n"
  ]
  N1 [
    label = "{Person|name = \'Bob\'\lage = 27\l}"
  ]
  N2 [
    label = "{Person, Parent|name = \'Charlie\'\lage = 65\l}"
  ]
  N3 [
    label = "{Person|name = \'Dora\'\lage = 30\l}"
  ]

Importing variables into subqueries

Variables are imported into a subquery using an importing WITH clause. As the subquery is evaluated for each incoming input row, the imported variables get bound to the corresponding values from the input row in each evaluation.

Query
UNWIND [0, 1, 2] AS x
CALL {
  WITH x
  RETURN x * 10 AS y
}
RETURN x, y
Table 1. Result
x y

0

0

1

10

2

20

Rows: 3

An importing WITH clause must:

  • Consist only of simple references to outside variables - e.g. WITH x, y, z. Aliasing or expressions are not supported in importing WITH clauses - e.g. WITH a AS b or WITH a+1 AS b.

  • Be the first clause of a subquery (or the second clause, if directly following a USE clause).

Post-union processing

Subqueries can be used to process the results of a UNION query further. This example query finds the youngest and the oldest person in the database and orders them by name.

Query
CALL {
  MATCH (p:Person)
  RETURN p
  ORDER BY p.age ASC
  LIMIT 1
UNION
  MATCH (p:Person)
  RETURN p
  ORDER BY p.age DESC
  LIMIT 1
}
RETURN p.name, p.age
ORDER BY p.name
Table 2. Result
p.name p.age

"Alice"

20

"Charlie"

65

Rows: 2

If different parts of a result should be matched differently, with some aggregation over the whole results, subqueries need to be used. This example query finds friends and/or parents for each person. Subsequently the number of friends and parents are counted together.

Query
MATCH (p:Person)
CALL {
  WITH p
  OPTIONAL MATCH (p)-[:FRIEND_OF]->(other:Person)
  RETURN other
UNION
  WITH p
  OPTIONAL MATCH (p)-[:CHILD_OF]->(other:Parent)
  RETURN other
}
RETURN DISTINCT p.name, count(other)
Table 3. Result
p.name count(other)

"Alice"

2

"Bob"

0

"Charlie"

0

"Dora"

0

Rows: 4

Aggregation and side-effects

Subqueries can be useful to do aggregations for each row and to isolate side-effects. This example query creates five Clone nodes for each existing person. The aggregation ensures that cardinality is not changed by the subquery. Without this, the result would be five times as many rows.

Query
MATCH (p:Person)
CALL {
  UNWIND range(1, 5) AS i
  CREATE (c:Clone)
  RETURN count(c) AS numberOfClones
}
RETURN p.name, numberOfClones
Table 4. Result
p.name numberOfClones

"Alice"

5

"Bob"

5

"Charlie"

5

"Dora"

5

Rows: 4
Nodes created: 20
Labels added: 20

Aggregation on imported variables

Aggregations in subqueries are scoped to the subquery evaluation, also for imported variables. The following example counts the number of younger persons for each person in the graph:

Query
MATCH (p:Person)
CALL {
  WITH p
  MATCH (other:Person)
  WHERE other.age < p.age
  RETURN count(other) AS youngerPersonsCount
}
RETURN p.name, youngerPersonsCount
Table 5. Result
p.name youngerPersonsCount

"Alice"

0

"Bob"

1

"Charlie"

3

"Dora"

2

Rows: 4