CALL {} (subquery)

1. 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.

Subqueries which end in a RETURN statement are called returning subqueries while subqueries without such a return statement are called unit subqueries.

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

Unit subqueries on the other hand are called for their side-effects and not for their results and do therefore not influence the results of the enclosing query.

There are restrictions on how subqueries interact with the enclosing query:

  • 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:

Diagram

2. 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).

3. 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

4. Aggregations

Returning subqueries change the number of results of the query: The result of the CALL clause is the combined result of evaluating the subquery for each input row.

The following example finds the name of each person and the names of their friends:

Query
MATCH (p:Person)
CALL {
  WITH p
  MATCH (p)-[:FRIEND_OF]-(c:Person)
  RETURN c.name AS friend
}
RETURN p.name, friend
Table 4. Result
p.name friend

"Alice"

"Bob"

"Bob"

"Alice"

Rows: 2

The number of results of the subquery changed the number of results of the enclosing query: Instead of 4 rows, one for each node), there are now 2 rows which were found for Alice and Bob respectively. No rows are returned for Charlie and Dora since they have no friends in our example graph.

We can also use subqueries to perform isolated aggregations. In this example we count the number of relationships each person has. As we get one row from each evaluation of the subquery, the number of rows is the same, before and after the CALL clause:

Query
MATCH (p:Person)
CALL {
  WITH p
  MATCH (p)--(c)
  RETURN count(c) AS numberOfConnections
}
RETURN p.name, numberOfConnections
Table 5. Result
p.name numberOfConnections

"Alice"

2

"Bob"

1

"Charlie"

1

"Dora"

0

Rows: 4

5. Unit subqueries and side-effects

Unit subqueries do not return any rows and are therefore used for their side effects.

This example query creates five clones of each existing person. As the subquery is a unit subquery, it does not change the number of rows of the enclosing query.

Query
MATCH (p:Person)
CALL {
  WITH p
  UNWIND range (1, 5) AS i
  CREATE (:Person {name: p.name})
}
RETURN count(*)
Table 6. Result
count(*)

4

Rows: 1
Nodes created: 20
Properties set: 20
Labels added: 20

6. 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 7. Result
p.name youngerPersonsCount

"Alice"

0

"Bob"

1

"Charlie"

3

"Dora"

2

Rows: 4

7. Subqueries in transactions

Subqueries can be made to execute in separate, inner transactions, producing intermediate commits. This can come in handy when doing large write operations, like batch updates or imports. To execute a subquery in separate transactions you add the modifier IN TRANSACTIONS after the subquery.

The following example imports a CSV file using the LOAD CSV clause, and creates nodes in separate transactions using CALL {} IN TRANSACTIONS

artists.csv
1,ABBA,1992
2,Roxette,1986
3,Europe,1979
4,bob hund,1991
5,The Cardigans,1992
Query
LOAD CSV FROM 'file:///artists.csv' AS line
CALL {
  WITH line
  CREATE (:Artist {name: line[1], year: toInteger(line[2])})
} IN TRANSACTIONS
Table 8. Result

(empty result)

Rows: 0
Nodes created: 5
Properties set: 10
Labels added: 5
Transactions committed: 1

As the size of the CSV file in this example is small, only a single separate transaction is started and committed.

CALL { …​ } IN TRANSACTIONS is only allowed in implicit transactions

7.1. Batching

The amount of work to do in each separate transaction can be specified in terms of how many input rows to process before committing the current transaction and starting a new one. The number of input rows is set with the modifier OF n ROWS (or ROW). If omitted, the default batch size is 1000 rows. Here’s the same example as above, but with one transaction every 2 input rows

Query
LOAD CSV FROM 'file:///artists.csv' AS line
CALL {
  WITH line
  CREATE (:Artist {name: line[1], year: toInteger(line[2])})
} IN TRANSACTIONS OF 2 ROWS
Table 9. Result

(empty result)

Rows: 0
Nodes created: 5
Properties set: 10
Labels added: 5
Transactions committed: 3

The query now starts and commits three separate transactions

  1. The first two executions of the subquery (for the first two input rows from LOAD CSV) take place in the first transaction.

  2. The first transaction is then committed before proceeding.

  3. The next two executions of the subquery (for the next two input rows) take place in a second transaction.

  4. The second transaction is committed.

  5. The last execution of the subquery (for the last input row) takes place in a third transaction.

  6. The third transaction is committed.

7.2. Errors

If an error occurs in CALL {} IN TRANSACTIONS the entire query fails and both the current inner transaction and the outer transaction are rolled back.

On error, any previously committed inner transactions remain committed, and are not rolled back.

In the following example, the last subquery execution in the second inner transaction fails due to division by zero.

Query
UNWIND [4, 2, 1, 0] AS i
CALL {
  WITH i
  CREATE (:Example {num: 100/i})
} IN TRANSACTIONS OF 2 ROWS
RETURN i

/ by zero (Transactions committed: 1)

When the failure occurred, the first transaction had already been committed, so the database contains two example nodes

Query
MATCH (e:Example)
RETURN e.num
Table 10. Result
e.num

25

50

Rows: 2