CALL subqueries

The CALL clause can be used to invoke a subquery. Unlike other subqueries in Cypher^®, it can be used to perform changes to the database (e.g. CREATE new nodes), and it requires an importing WITH clause.

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

Example graph

The following graph is used for the examples below:

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

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

Semantics

A CALL subquery is executed once for each incoming row.

In the below example, the CALL subquery 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 subquery can observe changes from previous executions.

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
innerCount	totalCount
1	3
2	3
3	3
Rows:3

Importing variables into subqueries

Variables are imported into a subquery using an importing WITH clause.

References to a variable in the outer scope that were not imported will introduce a new variable.

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

Execution order of CALL subqueries

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.

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 ordered 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 (nextPerson: Person&!ListHead)
WITH nextPerson ORDER BY nextPerson.age
CALL {
  WITH nextPerson
  MATCH (current:ListHead)
    REMOVE current:ListHead
    SET nextPerson:ListHead
    CREATE(current)-[:IS_YOUNGER_THAN]->(nextPerson)
  RETURN current AS from, nextPerson 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

Call subqueries can be used to further process the results of a UNION query. 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 result, subqueries need to be used. The example below query uses a CALL subquery in combination with UNION ALL to determine how much each Person node in the graph owes or is owed.

Query

MATCH (p:Person)
CALL {
    WITH p
    OPTIONAL MATCH (p)-[o:OWES]->(other:Person)
    RETURN o.dollars * -1 AS moneyOwed
UNION ALL
    WITH p
    OPTIONAL MATCH (other:Person)-[o:OWES]->(p)
    RETURN o.dollars AS moneyOwed
}
RETURN p.name, sum(moneyOwed) AS amountOwing

Table 6. Result
p.name	amountOwing
`"Alice"`	`-45`
`"Bob"`	`35`
`"Charlie"`	`20`
`"Dora"`	`-10`
Rows: 4

Aggregations

Returning subqueries change the number of results of the query. The result of the CALL subquery 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, 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.

Subqueries can also perform isolated aggregations. The below example uses the sum() function to count how much money is owed between the Person nodes in the graph.

Query

MATCH (p:Person)
CALL {
  WITH p
  MATCH (p)-[o:OWES]->(c)
  RETURN sum(o.dollars) AS owedAmount, c.name AS owedName
}
RETURN p.name, owedAmount, owedName

Table 8. Result
p.name	owedAmount	owedName
`"Alice"`	`25`	`"Bob"`
`"Alice"`	`20`	`"Charlie"`
`"Bob"`	`35`	`"Dora"`
`"Dora"`	`45`	`"Bob"`
Rows: 4

Note on returning subqueries and unit subqueries

The examples above have all used subqueries which end with a RETURN clause. These subqueries are called returning 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.

Subqueries without a RETURN statement are called unit subqueries. Unit subqueries are used for their ability to alter the graph with clauses such as CREATE, MERGE, SET, and DELETE. They do not explicitly return anything, and this means that the number of rows present after the subquery is the same as was going into the subquery.

Unit subqueries

Unit subqueries are used for their ability to alter the graph with updating clauses. They do not impact the amount of rows returned by the enclosing query.

This example query creates five clones of each existing Person node in the graph. 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

Rules

The following is true for CALL subqueries:

A CALL subquery can only refer to variables from the enclosing query if they are explicitly imported.
A CALL subquery cannot return variables with the same names as variables in the enclosing query.
All variables that are returned from a CALL subquery are afterwards available in the enclosing query.
A CALL subquery can be used to perform changes to the database.
A CALL subquery requires an importing WITH clause.

Was this page helpful?