CALL {} (subquery)

The CALL {} clause evaluates a subquery that returns some values.

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:

graph call subquery clause

To recreate the graph, run the following query in an empty Neo4j database:

CREATE
  (a:Person:Child {age: 20, name: 'Alice'}),
  (b:Person {age: 27, name: 'Bob'}),
  (c:Person:Parent {age: 65, name: 'Charlie'}),
  (d:Person {age: 30, name: 'Dora'})
  CREATE (a)-[:FRIEND_OF]->(b)
  CREATE (a)-[:CHILD_OF]->(c)
CREATE (:Counter {count: 0})

Semantics

A CALL clause is executed once for each incoming row.

Example 1. Execute for each incoming row

The CALL clause executes three times, one for each row that the UNWIND clause outputs.

Query

UNWIND [0, 1, 2] AS x
CALL {
  RETURN 'hello' AS innerReturn
}
RETURN innerReturn

Table 1. Result
innerReturn
`'hello'`
`'hello'`
`'hello'`
Rows:3

Each execution of a CALL clause can observe changes from previous executions.

Example 2. Observe changes from previous execution

Query

UNWIND [0, 1, 2] AS x
CALL {
  MATCH (n:Counter)
    SET n.count = n.count + 1
  RETURN n.count AS innerCount
}
WITH innerCount
MATCH (n:Counter)
RETURN
  innerCount,
  n.count AS totalCount

Table 2. Result
innerReturn	totalCount
1	3
2	3
3	3
Rows:3

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

The order in which subqueries are executed is not defined. If a query result depends on the order of execution of subqueries, an ORDER BY clause should precede the CALL clause.

Example 3. The order in which subqueries are executed

This query creates a linked list of all :Person nodes in order of ascending age.

The CALL clause is relying on the incoming row ordering to ensure that a correctly linked list is created, thus the incoming rows must be ordered with a preceding ORDER BY clause.

Query

MATCH (person:Person)
WITH person ORDER BY person.age ASC LIMIT 1
  SET person:ListHead
WITH *
MATCH (next: Person)
  WHERE NOT next:ListHead
WITH next ORDER BY next.age
CALL {
  WITH next
  MATCH (current:ListHead)
    REMOVE current:ListHead
    SET next:ListHead
    CREATE(current)-[r:IS_YOUNGER_THAN]->(next)
  RETURN current AS from, next AS to
}
RETURN
  from.name AS name,
  from.age AS age,
  to.name AS closestOlderName,
  to.age AS closestOlderAge

Table 4. Result
name	age	closestOlderName	closestOlderAge
`"Alice"`	`20`	`"Bob"`	`27`
`"Bob"`	`27`	`"Dora"`	`30`
`"Dora"`	`30`	`"Charlie"`	`65`
Rows: 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 5. 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 6. Result
p.name	count(other)
`"Alice"`	`2`
`"Bob"`	`0`
`"Charlie"`	`0`
`"Dora"`	`0`
Rows: 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 7. 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 8. Result
p.name	numberOfConnections
`"Alice"`	`3`
`"Bob"`	`2`
`"Charlie"`	`2`
`"Dora"`	`3`
Rows: 4

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 9. Result
count(*)
`4`
Rows: 1 Nodes created: 20 Properties set: 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 10. Result
p.name	youngerPersonsCount
`"Alice"`	`0`
`"Bob"`	`1`
`"Charlie"`	`3`
`"Dora"`	`2`
Rows: 4

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, imports, and deletes. To execute a subquery in separate transactions, you add the modifier IN TRANSACTIONS after the subquery.

The following example uses a CSV file and the LOAD CSV clause to import more data to the example graph. It creates nodes in separate transactions using CALL { ... } IN TRANSACTIONS:

friends.csv

1,Bill,26
2,Max,27
3,Anna,22
4,Gladys,29
5,Summer,24

Query

LOAD CSV FROM 'file:///friends.csv' AS line
CALL {
  WITH line
  CREATE (:PERSON {name: line[1], age: toInteger(line[2])})
} IN TRANSACTIONS

Table 11. 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.

Deleting a large volume of nodes

Using CALL { ... } IN TRANSACTIONS is the recommended way of deleting a large volume of nodes.

Example 4. DETACH DELETE

Query

MATCH (n)
CALL {
  WITH n
  DETACH DELETE n
} IN TRANSACTIONS

Table 12. Result
`(empty result)`
Rows: 0 Nodes deleted: 5 Relationships deleted: 2 Transactions committed: 1

The CALL { ... } IN TRANSACTIONS subquery is handled by the database so as to ensure optimal performance. Modifying the subquery may result in OutOfMemory exceptions for sufficiently large datasets.

Example 5. DETACH DELETE

The CALL { ... } IN TRANSACTIONS subquery should not be modified.

Any necessary filtering can be done before the subquery.

Query

MATCH (n:Label) WHERE n.prop > 100
CALL {
  WITH n
  DETACH DELETE n
} IN TRANSACTIONS

Table 13. Result
`(empty result)`
Rows: 0

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. The following is the same example but with one transaction every 2 input rows:

friends.csv

1,Bill,26
2,Max,27
3,Anna,22
4,Gladys,29
5,Summer,24

Query

LOAD CSV FROM 'file:///friends.csv' AS line
CALL {
  WITH line
  CREATE (:Person {name: line[1], age: toInteger(line[2])})
} IN TRANSACTIONS OF 2 ROWS

Table 14. 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:

The first two executions of the subquery (for the first two input rows from LOAD CSV) take place in the first transaction.
The first transaction is then committed before proceeding.
The next two executions of the subquery (for the next two input rows) take place in a second transaction.
The second transaction is committed.
The last execution of the subquery (for the last input row) takes place in a third transaction.
The third transaction is committed.

You can also use CALL { ... } IN TRANSACTIONS OF n ROWS to delete all your data in batches in order to avoid a huge garbage collection or an OutOfMemory exception. For example:

Query

MATCH (n)
CALL {
  WITH n
  DETACH DELETE n
} IN TRANSACTIONS OF 2 ROWS

Table 15. Result
`(empty result)`
Rows: 0 Nodes deleted: 9 Relationships deleted: 2 Transactions committed: 5

Up to a point, using a larger batch size will be more performant. The batch size of 2 ROWS is an example given the small data set used here. For larger data sets, you might want to use larger batch sizes, such as 10000 ROWS.

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

Error message

/ 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 16. Result
e.num
`25`
`50`
Rows: 2

Restrictions

These are the restrictions on queries that use CALL { ... } IN TRANSACTIONS:

A nested CALL { ... } IN TRANSACTIONS inside a CALL { ... } clause is not supported.
A CALL { ... } IN TRANSACTIONS in a UNION is not supported.
A CALL { ... } IN TRANSACTIONS after a write clause is not supported, unless that write clause is inside a CALL { ... } IN TRANSACTIONS.

Additionally, there are some restrictions that apply when using an importing WITH clause in a CALL subquery:

Only variables imported with the importing WITH clause can be used.
No expressions or aliasing are allowed within the importing WITH clause.
It is not possible to follow an importing WITH clause with any of the following clauses: DISTINCT, ORDER BY, WHERE, SKIP, and LIMIT.

Attempting any of the above, will throw an error. For example, the following query using a WHERE clause after an importing WITH clause will throw an error:

Query

UNWIND [[1,2],[1,2,3,4],[1,2,3,4,5]] AS l
CALL {
    WITH l
    WHERE size(l) > 2
    RETURN l AS largeLists
}
RETURN largeLists

Error message

Importing WITH should consist only of simple references to outside variables.
WHERE is not allowed.

A solution to this restriction, necessary for any filtering or ordering of an importing WITH clause, is to declare a second WITH clause after the importing WITH clause. This second WITH clause will act as a regular WITH clause. For example, the following query will not throw an error:

Query

UNWIND [[1,2],[1,2,3,4],[1,2,3,4,5]] AS l
CALL {
 WITH l
 WITH size(l) AS size, l AS l
 WHERE size > 2
 RETURN l AS largeLists
}
RETURN largeLists

Table 17. Result
largeLists
`[1, 2, 3, 4]`
`[1, 2, 3, 4, 5]`
Rows: 2

Was this page helpful?