Neo4j: Cypher - FOREACH vs CALL {} (subquery)
Date: 2020-10-29 00:21:00
Learn how to use the CALL {} subquery syntax to create data in a graph.

I recently wanted to create a graph based on an adjacency list, and in this post we'll learn how to do that using the https://neo4j.com/docs/cypher-manual/current/clauses/foreach/[FOREACH^] clause and then with the new https://neo4j.com/docs/cypher-manual/current/clauses/call-subquery/#subquery-aggregation[CALL {}^] subquery clause.

We'll start with the following map of ids -> arrays of ids:

[source,cypher]
----
:param list => ({`0`: [7, 9], `1`: [2, 4, 5, 6, 8, 9], `2`: [0, 6, 8, 9], `3`: [1, 2, 6, 9], `4`: [1, 2, 3, 7], `5`: [8, 9], `6`: [2, 4, 5, 7, 8, 9], `7`: [0, 3, 4, 6, 8, 9], `8`: [1, 6, 9], `9`: [0, 1, 3, 5]})
----

We want to create one node per id and create a relationship from each node to the nodes in its array.
So for example, we'll have a relationship from `0` -> `7`, `0` -> `9`, `1` -> `2`, and so on.

We can do this using the `FOREACH` clause, as shown in the following query

[source,cypher]
----
UNWIND keys($list) AS key
MERGE (n:Node {id: toInteger(key)})
FOREACH(item IN $list[key] |
  MERGE (m:Node {id: item})
  MERGE (n)-[:CONNECTED_TO]->(m)
)
RETURN n.id;
----

If we run this query, we'll see the following output:

.Results
[opts="header"]
|===
| n.id
| 0
| 1
| 2
| 3
| 4
| 5
| 6
| 7
| 8
| 9
|===

But what if along with the id, we'd also like to return the number of outgoing relationships from each node?
One way to do this would be to use the `UNWIND` clause to iterate through the values in the array, as shown below:

[source,cypher]
----
UNWIND keys($list) AS key
MERGE (n:Node {id: toInteger(key)})
WITH n, key
UNWIND $list[key] AS item
MERGE (m:Node {id: item})
MERGE (n)-[:CONNECTED_TO]->(m)
RETURN n.id, count(*);
----

If we run this query, we'll see the following output:

.Results
[opts="header"]
|===
| n.id | count
| 0    | 2
| 1    | 6
| 2    | 4
| 3    | 4
| 4    | 4
| 5    | 2
| 6    | 6
| 7    | 6
| 8    | 3
| 9    | 4
|===

This works, but we are creating a lot of extra rows between lines 4-6, before squashing them back again on line 7.
In this example it's not too much of a problem, but in queries with multiple `UNWIND` clauses, we can simplify things by https://neo4j.com/docs/cypher-manual/current/clauses/call-subquery/#subquery-aggregation[isolating the side effects^] in a `CALL {}` subquery.

If we do that, we'll have the query below:

[source,cypher]
----
UNWIND keys($list) AS key
MERGE (n:Node {id: toInteger(key)})
CALL {
  WITH key, n
  UNWIND $list[key] AS item
  MERGE (m:Node {id: item})
  MERGE (n)-[:CONNECTED_TO]->(m)
  RETURN count(*) AS count
}
RETURN n.id, count
----

If we run that query, we'll see the following output:

.Results
[source,text]
----
WITH is required between MERGE and CALL (line 3, column 1 (offset: 62))
"CALL {"
 ^
----

Hmmm, not quite.
Let's add a `WITH` clause for `n` and `key`:

[source,cypher]
----
UNWIND keys($list) AS key
MERGE (n:Node {id: toInteger(key)})
WITH n, key
CALL {
  UNWIND $list[key] AS item
  MERGE (m:Node {id: item})
  MERGE (n)-[:CONNECTED_TO]->(m)
  RETURN count(*) AS count
}
RETURN n.id, count;
----

And run our query again:

.Results
[source,text]
----
Variable `key` not defined (line 5, column 16 (offset: 96))
"  UNWIND $list[key] AS item"
                ^
----

Not quite!
We also need to add a `WITH` clause with each of these values to the start of the `CALL{}` clause:

[source,cypher]
----
UNWIND keys($list) AS key
MERGE (n:Node {id: toInteger(key)})
WITH n, key
CALL {
  WITH key, n
  UNWIND $list[key] AS item
  MERGE (m:Node {id: item})
  MERGE (n)-[:CONNECTED_TO]->(m)
  RETURN count(*) AS count
}
RETURN n.id, count
----

And now if we run the query, we'll see the following output:

.Results
[opts="header"]
|===
| n.id | count
| 0    | 2
| 1    | 6
| 2    | 4
| 3    | 4
| 4    | 4
| 5    | 2
| 6    | 6
| 7    | 6
| 8    | 3
| 9    | 4
|===