How to get a list of movies that pairs of actors performed in together using Neo4j?

Question

I have two node types: Actor and Movie.
I want to group actors by movies they performed in.
That's why I've created Cypher query:

MATCH (a1:Actor)-[:performed_in]->(m:Movie)<-[:performed_in]-(a2:Actor) 
RETURN a1.name, a2.name, 
COLLECT(DISTINCT m.name)

Unfortunately, the result is not as I need.
The problem is that I've got a table like this:

a1       a2       m
Sam      Joe      Movie1, Movie2
Joe      Sam      Movie1, Movie2

As you can see I have two rows which mean the same in that situation.
How can I get rid of that?

Do you mean you want the movie name and the list of actors for that movie? — Rajendra Kadam

rickhg12hs rickhg12hs · Accepted Answer · 2019-01-27T19:34:41

I know it feels like a hack, but how about:

MATCH (a1:Actor)-[:performed_in]->(m:Movie)<-[:performed_in]-(a2:Actor)
WHERE id(a1) < id(a2)
RETURN a1.name, a2.name, COLLECT(DISTINCT m.name)

Insisting that the internal ID of one node be less than the other will give you the result I think you want.