GraphAcademy

Cypher Aggregations

Understanding Aggregation in Cypher

Working with Lists

Aggregating Functions

Scalar List Functions

List functions that return a single value

Cypher has a number of scalar functions that operate on a list to return a single value. The single value could be a string, number, or even another list.

Size of a list

The most-used scalar function on a list is one that you have already seen, size() that returns the number of elements in a list. If many cases, it is used to limit the number of results returned.

Here is an example:

cypher
MATCH (p:Person)-[:ACTED_IN]->(m:Movie)
WITH p, collect (m.title) AS MovieTitles
WITH p, MovieTitles, size(MovieTitles) AS NumMovies
WHERE NumMovies >= 20
RETURN p.name AS Actor, NumMovies, MovieTitles ORDER BY NumMovies

This query returns the names of actors who acted in more than 20 movies by creating the list for each actor, then filtering the list by the size. This query also uses the value of size() to order the results returned.

First/last element of a list

You use the head() function to return the first element of a list.

cypher
MATCH (m:Movie)
WHERE  date(m.released).year = 2000
WITH m ORDER BY date(m.released)
WITH collect(m) AS Movies
WITH head(Movies) as First
RETURN First.title AS FirstTitle, First.released AS FirstDate

This query retrieves all movies for the year 2000 and adds them to a list. Because the list is ordered by the released property of a Movie node, we can find the first element of the list of nodes, First. Then we return the value of the title and the value of the release date.

You use the last() function to return the last element of a list.

cypher
MATCH (m:Movie) where date(m.released).year = 2000
WITH m ORDER BY date(m.released)
WITH collect(m) as Movies
WITH last(Movies) as Last
RETURN Last.title as LastTitle, Last.released AS LastDate

Using reduce()

Cypher has a reduce() function that enables you to calculate a value where you specify the initial value and apply a formula or calculation using the initial value on every element in the list.

Here is an example:

cypher
MATCH (m:Movie) WHERE date(m.released).year = 2000
WITH collect(m.revenue) AS Revenues
WITH Revenues, reduce(t=0, r IN Revenues | t + r) AS TotalRevenue
RETURN TotalRevenue, size(Revenues) AS TotalMovies

This query retrieves all movie revenue values for movies released in 2000 and creates the Revenues list. It then uses reduce() with a starting value of 0. Finally, it adds each element to return the total of all elements in the list.

Here is another example:

cypher
MATCH (:User)-[r:RATED]->(m:Movie)
WHERE m.title = 'Toy Story'
WITH  collect(r.rating) AS Ratings
WITH Ratings, reduce(Rating = 0, x IN Ratings | Rating + x) AS TotalRatings
RETURN round(TotalRatings/size(Ratings),1)

In this example we are totaling the ratings for the movie, Toy Story and then returning the average by dividing by the size of the list. You will learn about the Cypher avg() function later in this course.

The reduce() function can be used for any type of calculation.

Here is another example where we use reduce() to flatten a list of lists:

cypher
WITH [[1,2,3], [4,5,6], [7,8,9]] AS StartingList
RETURN reduce(Calc = [], r IN StartingList | Calc + r) AS FlattenedList

The initial value is an empty list. Each element in the list is added to the initial value, Calc.

Check your understanding

1. First element of a list

What Cypher function returns the first element of a list?

  • ❏ first()

  • ✓ head()

  • ❏ top()

Hint

Think of going to the head of the line!

Solution

You use the head() function to return the first element of a list.

2. Martin Scorsese movie budgets

We want to know the total budget number for all movies that Martin Scorses directed.

Use the dropdown below to select the correct WITH clause.

Once you have selected your option, click the Check Results query button to continue.

cypher
MATCH (p:Person)-[:DIRECTED]->(m:Movie)
WHERE p.name = 'Martin Scorsese'
WITH collect(m.budget) AS Budgets
/*select:WITH Budgets, reduce(t=0, b IN Budgets | t + b) AS TotalBudget*/
RETURN  TotalBudget, size(Budgets) AS TotalMovies

  • ❏ WITH Budgets, calculate(t=0, b IN Budgets | t + b) AS TotalBudget

  • ✓ WITH Budgets, reduce(t=0, b IN Budgets | t + b) AS TotalBudget

  • ❏ WITH Budgets, reduce(Budgets) AS TotalBudget

  • ❏ WITH Budgets, sum(Budgets) AS TotalBudget

Hint

You use this function to operate on every element in the Budgets list. You must set an initial value. You will add the value of each element to create a total.

Solution

The correct answer is WITH Budgets, reduce(t=0, b IN Budgets | t + b) AS TotalBudget

Summary

In this lesson, you about the Cypher functions used to return a single value from a list. In the next Challenge, you will create some queries to use these Cypher scalar list functions.