CALL subqueries
The CALL
clause can be used to invoke subqueries that execute operations within a defined scope, thereby optimizing data handling and query efficiency.
Unlike other subqueries in Cypher®, CALL
subqueries can be used to perform changes to the database (e.g. CREATE new nodes).
The CALL clause is also used for calling procedures.
For descriptions of the CALL clause in this context, refer to the CALL procedure.
|
Example graph
A graph with the following schema is used for the examples below:
To recreate the graph, run the following query in an empty Neo4j database:
CREATE (teamA:Team {name: 'Team A'}),
(teamB:Team {name: 'Team B'}),
(teamC:Team {name: 'Team C'}),
(playerA:Player {name: 'Player A', age: 21}),
(playerB:Player {name: 'Player B', age: 23}),
(playerC:Player {name: 'Player C', age: 19}),
(playerD:Player {name: 'Player D', age: 30}),
(playerE:Player {name: 'Player E', age: 25}),
(playerF:Player {name: 'Player F', age: 35}),
(playerA)-[:PLAYS_FOR]->(teamA),
(playerB)-[:PLAYS_FOR]->(teamA),
(playerD)-[:PLAYS_FOR]->(teamB),
(playerE)-[:PLAYS_FOR]->(teamC),
(playerF)-[:PLAYS_FOR]->(teamC),
(teamA)-[:OWES {dollars: 1500}]->(teamB),
(teamA)-[:OWES {dollars: 3000}]->(teamB),
(teamB)-[:OWES {dollars: 1700}]->(teamC),
(teamC)-[:OWES {dollars: 5000}]->(teamB)
Semantics and performance
A CALL
subquery is executed once for each incoming row.
The variables returned in a subquery are available to the outer scope of the enclosing query.
In this example, the CALL
subquery executes three times, one for each row that the UNWIND
clause outputs.
UNWIND [0, 1, 2] AS x
CALL () {
RETURN 'hello' AS innerReturn
}
RETURN innerReturn
innerReturn |
---|
|
|
|
Rows: 3 |
Each execution of a CALL
subquery can observe changes from previous executions.
This allows for the accumulation of results and the progressive transformation of data within a single Cypher query.
In this example, each iteration of the CALL
subquery adds 1 to the age
of Player A
and the returned newAge
reflects the age
after each increment.
UNWIND [1, 2, 3] AS x
CALL () {
MATCH (p:Player {name: 'Player A'})
SET p.age = p.age + 1
RETURN p.age AS newAge
}
MATCH (p:Player {name: 'Player A'})
RETURN x AS iteration, newAge, p.age AS totalAge
iteration | newAge | totalAge |
---|---|---|
1 |
22 |
24 |
2 |
23 |
24 |
3 |
24 |
24 |
Rows: 3 |
The scoping effect of a CALL
subquery means that the work performed during each execution of each row can be cleaned up as soon its execution ends, before proceeding to the next row.
This allows for efficient resource management and reduces memory overhead by ensuring that temporary data structures created during the subquery execution do not persist beyond their usefulness.
As a result, CALL
subqueries can help maintain optimal performance and scalability, especially in complex or large-scale queries.
In this example, a CALL
subquery is used to collect
a LIST
containing all players who play for a particular team.
MATCH (t:Team)
CALL (t) {
MATCH (p:Player)-[:PLAYS_FOR]->(t)
RETURN collect(p) as players
}
RETURN t AS team, players
team | players |
---|---|
|
|
|
|
|
|
Rows: 3 |
The CALL
subquery ensures that each Team
is processed separately (one row per Team
node), rather than having to hold every Team
and Player
node in heap memory simultaneously before collecting them into lists.
Using a CALL
subquery can therefore reduce the amount of heap memory required for an operation.
Importing variables
Variables from the outer scope must be explicitly imported into the inner scope of the CALL
subquery, either by using a variable scope clause or an importing WITH
clause (deprecated).
As the subquery is evaluated for each incoming input row, the imported variables are assigned the corresponding values from that row.
The variable scope clause
Variables can be imported into a CALL
subquery using a scope clause: CALL (<variable>)
.
Using the scope clause disables the deprecated importing WITH
clause.
A scope clause can be used to import all, specific, or none of the variables from the outer scope.
This example only imports the p
variable from the outer scope and uses it to create a new, randomly generated, rating
property for each Player
node.
It then returns the Player
node with the highest rating
.
MATCH (p:Player), (t:Team)
CALL (p) {
WITH rand() AS random
SET p.rating = random
RETURN p.name AS playerName, p.rating AS rating
}
RETURN playerName, rating, t AS team
ORDER BY rating
LIMIT 1
playerName | rating | team |
---|---|---|
|
|
|
Rows: 1 |
To import additional variables, include them within the parentheses after CALL
, separated by commas.
For example, to import both variables from the MATCH
clause in the above query, modify the scope clause accordingly: CALL (p, t)
.
To import all variables from the outer scope, use CALL (*)
.
This example imports both the p
and t
variables and sets a new lastUpdated
property on both.
MATCH (p:Player), (t:Team)
CALL (*) {
SET p.lastUpdated = timestamp()
SET t.lastUpdated = timestamp()
}
RETURN p.name AS playerName,
p.lastUpdated AS playerUpdated,
t.name AS teamName,
t.lastUpdated AS teamUpdated
LIMIT 1
playerName | playerUpdated | teamName | teamUpdated |
---|---|---|---|
|
|
|
|
Rows: 1 |
To import no variables from the outer scope, use CALL ()
.
MATCH (t:Team)
CALL () {
MATCH (p:Player)
RETURN count(p) AS totalPlayers
}
RETURN count(t) AS totalTeams, totalPlayers
totalTeams | totalPlayers |
---|---|
|
|
Rows: 1 |
As of Neo4j 5.23, it is deprecated to use Deprecated
|
Rules
-
The scope clause’s variables can be globally referenced in the subquery. A subsequent
WITH
within the subquery cannot delist an imported variable. The deprecated importingWITH
clause behaves differently because imported variables can only be referenced from the first line and can be delisted by subsequent clauses. -
Variables cannot be aliased in the scope clause. Only simple variable references are allowed.
MATCH (t:Team)
CALL (t AS teams) {
MATCH (p:Player)-[:PLAYS_FOR]->(teams)
RETURN collect(p) as players
}
RETURN t AS teams, players
-
The scope clause’s variables cannot be re-declared in the subquery.
MATCH (t:Team)
CALL (t) {
WITH 'New team' AS t
MATCH (p:Player)-[:PLAYS_FOR]->(t)
RETURN collect(p) as players
}
RETURN t AS team, players
-
The subquery cannot return a variable name which already exists in the outer scope. To return imported variables they must be renamed.
MATCH (t:Team)
CALL (t) {
RETURN t
}
RETURN t
Importing WITH
clause
Variables can also be imported into a CALL
subquery using an importing WITH
clause.
Note that this syntax is not GQL conformant.
WITH
clauseMATCH (t:Team)
CALL {
WITH t
MATCH (p:Player)-[:PLAYS_FOR]->(t)
RETURN collect(p) as players
}
RETURN t AS teams, players
Click to read more about importing variables using the WITH
clause
-
Just as when using a variable scope clause, a subquery using an importing
WITH
clause cannot return a variable name which already exists in the outer scope. To return imported variables they must be renamed. -
The importing
WITH
clause must the first clause of a subquery (or the second clause, if directly following aUSE
clause). -
It is not possible to follow an importing
WITH
clause with any of the following clauses:DISTINCT
,ORDER BY
,WHERE
,SKIP
, andLIMIT
.
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:
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
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:
UNWIND [[1,2],[1,2,3,4],[1,2,3,4,5]] AS l
CALL {
WITH l
WITH l
WHERE size(l) > 2
RETURN l AS largeLists
}
RETURN largeLists
largeLists |
---|
|
|
Rows: 2 |
Optional subquery calls
OPTIONAL CALL
allows for optional execution of a CALL
subquery.
Similar to OPTIONAL MATCH
any empty rows produced by the OPTIONAL CALL
subquery will return null
.
CALL
and OPTIONAL CALL
This example, which finds the team that each Player
plays for, highlights the difference between using CALL
and OPTIONAL CALL
.
CALL
MATCH (p:Player)
CALL (p) {
MATCH (p)-[:PLAYS_FOR]->(team:Team)
RETURN team
}
RETURN p.name AS playerName, team.name AS team
playerName | team |
---|---|
|
|
|
|
|
|
|
|
|
|
Rows: 5 |
Note that no results are returned for Player C
, since they are not connected to any Team
with a PLAYS_FOR
relationship.
OPTIONAL CALL
MATCH (p:Player)
OPTIONAL CALL (p) {
MATCH (p)-[:PLAYS_FOR]->(team:Team)
RETURN team
}
RETURN p.name AS playerName, team.name AS team
Now all Player
nodes, regardless of whether they have any PLAYS_FOR
relationships connected to a Team
, are returned.
playerName | team |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Rows: 6 |
Execution order of CALL subqueries
The order in which rows from the outer scope are passed into subqueries is not defined.
If the results of the subquery depend on the order of these rows, use an ORDER BY
clause before the CALL
clause to guarantee a specific processing order for the rows.
CALL
subqueryThis example creates a linked list of all Player
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.
CALL
subqueryMATCH (player:Player)
WITH player
ORDER BY player.age ASC LIMIT 1
SET player:ListHead
WITH *
MATCH (nextPlayer: Player&!ListHead)
WITH nextPlayer
ORDER BY nextPlayer.age
CALL (nextPlayer) {
MATCH (current:ListHead)
REMOVE current:ListHead
SET nextPlayer:ListHead
CREATE(current)-[:IS_YOUNGER_THAN]->(nextPlayer)
RETURN current AS from, nextPlayer AS to
}
RETURN
from.name AS name,
from.age AS age,
to.name AS closestOlderName,
to.age AS closestOlderAge
name | age | closestOlderName | closestOlderAge |
---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Rows: 5 |
Post-union processing
Call subqueries can be used to further process the results of a UNION query.
UNION
within a CALL
subqueryThis example query finds the youngest and the oldest Player
in the graph.
CALL () {
MATCH (p:Player)
RETURN p
ORDER BY p.age ASC
LIMIT 1
UNION
MATCH (p:Player)
RETURN p
ORDER BY p.age DESC
LIMIT 1
}
RETURN p.name AS playerName, p.age AS age
playerName | age |
---|---|
|
|
|
|
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 Team
in the graph owes or is owed.
MATCH (t:Team)
CALL (t) {
OPTIONAL MATCH (t)-[o:OWES]->(other:Team)
RETURN o.dollars * -1 AS moneyOwed
UNION ALL
OPTIONAL MATCH (other)-[o:OWES]->(t)
RETURN o.dollars AS moneyOwed
}
RETURN t.name AS team, sum(moneyOwed) AS amountOwed
ORDER BY amountOwed DESC
team | amountOwed |
---|---|
|
|
|
|
|
|
Rows: 3 |
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.
CALL
subquery changing returned rows of outer queryThe following example finds the name of each Player
and the team they play for.
No rows are returned for Player C
, since they are not connected to a Team
with a PLAYS_FOR
relationship.
The number of results of the subquery thus changed the number of results of the enclosing query.
MATCH (p:Player)
CALL (p) {
MATCH (p)-[:PLAYS_FOR]->(team:Team)
RETURN team.name AS team
}
RETURN p.name AS playerName, team
playerName | team |
---|---|
|
|
|
|
|
|
|
|
|
|
Rows: 5 |
CALL
subqueries and isolated aggregationsSubqueries can also perform isolated aggregations.
The below example uses the sum() function to count how much money is owed between the Team
nodes in the graph.
Note that the owedAmount
for Team A
is the aggregated results of two OWES
relationships to Team B
.
MATCH (t:Team)
CALL (t) {
MATCH (t)-[o:OWES]->(t2:Team)
RETURN sum(o.dollars) AS owedAmount, t2.name AS owedTeam
}
RETURN t.name AS owingTeam, owedAmount, owedTeam
owingTeam | owedAmount | owedTeam |
---|---|---|
|
|
|
|
|
|
|
|
|
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 3 clones of each existing Player
node in the graph.
As the subquery is a unit subquery, it does not change the number of rows of the enclosing query.
MATCH (p:Player)
CALL (p) {
UNWIND range (1, 3) AS i
CREATE (:Person {name: p.name})
}
RETURN count(*)
count(*) |
---|
|
Rows: 1 |
Summary
-
CALL
subqueries optimize data handling and query efficiency, and can perform changes to the database. -
CALL
subqueries allow for row-by-row data transformation and enable the accumulation of results across multiple rows, facilitating complex operations that depend on intermediate or aggregated data. -
CALL
subqueries can only refer to variables from the enclosing query if they are explicitly imported by either a variable scope clause or an importingWITH
clause (deprecated). -
All variables that are returned from a
CALL
subquery are afterwards available in the enclosing query. -
Returning subqueries (with
RETURN
clause) influence the number of output rows, while unit subqueries (withoutRETURN
clause) perform graph updates without changing the number of rows. -
An
ORDER BY
clause can be used beforeCALL
subqueries to ensure a specific order. -
CALL
subqueries can be used in combination withUNION
to process and aggregate different parts of a query result.