Question about exercise 6.3

[e_boter]

Exercise 6.3 reads as follows: “The results returned from the previous query returns the collection of movie titles with duplicates. That is because there are multiple actors per released year. Next, modify the query so that there is no duplication of the movies listed for a year.”

The solution (on page 8/15) is as follows:

MATCH (a:Person)-[:ACTED_IN]->(m:Movie)
WHERE m.released >= 1990 AND m.released < 2000
RETURN m.released, collect(DISTINCT m.title), collect(a.name)

In the output of the query, the movie titles are no longer duplicated. But it is no longer clear which actors acted in which movie. I just get a list of actors who played in movies that were released in a particular year. Now, if I would like to have a result entry for each year of release, with a list of the movies released in that year and then – for each of those movies – a list of the actors who acted in THAT particular movie. How should the query look like? Or are the requirements for this query incompatible in some way?

I hope my question is clear.

[andrew_bowman]

It's a good question.

To get the results you want, we need to do two separate aggregations. First we need to aggregate the actors per movie into some object containing both the movie and its actors. Then we need to aggregate these movie objects per released year.

This is easiest done by using map projection, which allows us to generate a map from properties of a node, and introduce additional key/value pairs even if they aren't node properties:

MATCH (a:Person)-[:ACTED_IN]->(m:Movie)
WHERE m.released >= 1990 AND m.released < 2000
WITH m.released as releasedYear, m {.title, actors:collect(a.name)} as movie
RETURN releasedYear, collect(movie) as movies

Alternately, we could also use pattern comprehension such that we get the movie first, then use the pattern comprehension to get the actors per movie, already collected, then collect the movies per released year.

MATCH (m:Movie)
WHERE m.released >= 1990 AND m.released < 2000
WITH m.released as releasedYear, m {.title, actors: [(a:Person)-[:ACTED_IN]->(m) | a.name] } as movie
RETURN releasedYear, collect(movie) as movies