29 Oct 2020

Neo4j: Cypher - FOREACH vs CALL {} (subquery)

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 FOREACH clause and then with the new CALL {} subquery clause.

We’ll start with the following map of ids → arrays of ids:

: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

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:

Table 1. Results
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:

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:

Table 2. Results
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 isolating the side effects in a CALL {} subquery.

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

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

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:

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

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:

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:

Table 3. Results
n.id	count
0	2
1	6
2	4
3	4
4	4
5	2
6	6
7	6
8	3
9	4

About the author

I'm currently working on short form content at ClickHouse. I publish short 5 minute videos showing how to solve data problems on YouTube @LearnDataWithMark. I previously worked on graph analytics at Neo4j, where I also co-authored the O'Reilly Graph Algorithms Book with Amy Hodler.