Complex SQL-like queries in Cypher

I am new to Cypher and trying to figure out how one would achieve some kinds of queries that are relatively straightforward in SQL. One that comes to mind is computing Jaccard Similarity. I know there is a special procedure that does that, but say I wanted to do that natively.

In SQL, I could just:

Edges AS (
SELECT as movie1, as movie2
FROM movie_actors as m1 
LEFT JOIN movie_actors as m2 
ON =
Intersection AS (
SELECT movie1, movie2, COUNT(distinct actor) as num
FROM Edges
Counts AS (
SELECT movie, COUNT(distinct actor) as num
FROM Edges
SELECT movie1, movie2, (I.num) / (C1.num + C2.num - I.num) as similarity
FROM Intersection I 
JOIN Counts C1 ON I.movie1 = 
JOIN Counts C2 ON I.movie2 =

How would you do such chaining with the WITHs in Cypher? I would need to store and reuse intermediate results and I wasn't sure how to do that

What about something like this:

WHERE i:Actor AND i:Director
WITH count(i) AS Intersection
WHERE u:Actor OR u:Director
WITH Intersection, count(u) AS Union
RETURN Intersection, Union, toFloat(Intersection) / Union AS `Jaccard index`

It doesn't look like very complex though.

Intermediate results are stored using the WITH-statement.

1 Like

This is not quite what I had in mind -- For each pair of movies (m1, m2) I want to compute the Jaccard Similarity of their actors (sorry I should've clarified this earlier). For example, Matrix 1 and Matrix 2 might share 80% of the actors (intersection over union of the actors).

I know how I could compute the intersection:

MATCH (m2:Movie)<-[:ACTED_IN]-(i:Person)-[:ACTED_IN]->(m1:Movie)
RETURN m1, m2, count(distinct i) AS Intersection

And this seems to work more or less.

Doing a UNION of the actors between all pairs of movies is where I am having difficulties.

I tried something like this:

MATCH (m2:Movie)<-[:ACTED_IN]-(i:Person)-[:ACTED_IN]->(m1:Movie)
WHERE id(m1) = 9 AND id(m2) = 10
WITH m1, m2, count(distinct i) AS Intersection
MATCH (m3:Movie)<-[:ACTED_IN]-(i:Person)
WITH m3, COUNT(distinct i) as count_actors
WHERE m1 = m3 OR m2 = m3
RETURN m1, m2, Intersection / SUM(count_actors)

But I get this error:

In a WITH/RETURN with DISTINCT or an aggregation, it is not possible to access variables declared before the WITH/RETURN: m1 (line 6, column 7 (offset: 235))
"WHERE m1 = m3 OR m2 = m3"

Essentially in SQL, you can reference any of the preceding WITH clauses by their name, but it seems that in Cypher, you can only reference the one immediately before. Is that true? That would restrict the flexibility of the language quite a bit.


your line 6 of

WITH m3, COUNT(distinct i) as count_actors

effectively indicates only carry forward variables m3 and count_actors.

But line 7 refers to variables m1 and m2. and line 8 refers to variable Intersection

Can you change line 6 to

with m1, m2, Intersection, m3, COUNT(disinct i) as count_actors

I did not have time to test the following pattern, could something like this work to calculate the union?

MATCH (m2:Movie)<-[:ACTED_IN]-(i:Person)-[:ACTED_IN]->(m1:Movie)
WITH m1, m2
MATCH (u:Person)-[:ACTED_IN]->(m:Movie)
WHERE ID(m) = ID(m1) OR ID(m) = ID(m2)
1 Like

Try this:

MATCH (m:Movie)<-[:ACTED_IN]-(i:Person))
where id(m1) = 9
with m1, collect(id(i)) as person1
MATCH (m2:Movie)<-[:ACTED_IN]-(j:Person)
where id(m2) = 10
with m1, person1, m2, collect(id(j)) as person2
with m1, m2, apoc.coll.intersection(person1, person2) as diff, 
apoc.coll.union(person1, person2) as u1
return as movie1, as movie2, (toFloat(size(diff))/ toFloat(size(u1))) as jaccard

1 Like

This worked! Thank you :)

This also worked, and it makes sense -- albeit it's a bit counterintuitive coming from SQL. Readability-wise, the below solution looks a bit better to me but do you know which would be more efficient?