Getting More Out of Queries

Not only can the equality = be tested, but you can test ranges, existence, strings, as well as specify logical operations during the query.

Here is an example of specifying a range for filtering the query:

Here is the result:

You can also specify the same query as:

You can specify conditions in a WHERE clause that return a value of true or false (for example predicates). For testing numeric values, you use the standard numeric comparison operators. Each condition can be combined for runtime evaluation using the boolean operators AND, OR, XOR, and NOT. There are a number of numeric functions you can use in your conditions. See the Neo4j Cypher Manual’s section Mathematical Functions for more information.

A special condition in a query is when the retrieval returns an unknown value called null. Read the Neo4j Cypher Manual’s section Working with null to understand how null values are used at runtime.

Thus far, you have used the node labels to filter queries in a MATCH clause. You can filter node labels in the WHERE clause also:

For example, these two Cypher queries:

can be rewritten using WHERE clauses as follows:

Not all node labels need to be tested during a query, but if your graph has multiple labels for the same node, filtering it by the node label will provide better query performance.

Recall that a property is associated with a particular node or relationship. A property is not associated with a node with a particular label or relationship type. In one of our queries earlier, we saw that the movie "Something’s Gotta Give" is the only movie in the Movie database that does not have a tagline property. Suppose we only want to return the movies that the actor, Jack Nicholson acted in with the condition that they must all have a tagline.

Here is the query to retrieve the specified movies where we test the existence of the tagline property:

Here is the result:

Cypher has a set of string-related keywords that you can use in your WHERE clauses to test string property values. You can specify STARTS WITH, ENDS WITH, and CONTAINS.

For example, to find all actors in the Movie database whose first name is Michael, you would write:

Here is the result:

Note that the comparison of strings is case-sensitive. There are a number of string-related functions you can use to further test strings. For example, if you want to test a value, regardless of its case, you could call the toLower() function to convert the string to lower case before it is compared.

See the String functions section of the Neo4j Cypher Manual for more information. It is sometimes useful to use the built-in string functions to modify the data that is returned in the query in the RETURN clause.

If you prefer, you can test property values using regular expressions. You use the syntax =~ to specify the regular expression you are testing with. Here is an example where we test the name of the Person using a regular expression to retrieve all Person nodes with a name property that begins with 'Tom':

Here is the result:

Sometimes during a query, you may want to perform additional filtering using the relationships between nodes being visited during the query. For example, during retrieval, you may want to exclude certain paths traversed. You can specify a NOT specifier on a pattern in a WHERE clause.

Here is an example where we want to return all Person nodes of people who wrote movies:

Here is the result:

Next, we modify this query to exclude people who directed that movie:

Here is the result:

Here is another example where we want to find Gene Hackman and the movies that he acted in with another person who also directed the movie.

Here is the result:

If you have a set of values you want to test with, you can place them in a list or you can test with an existing list in the graph.

You can define the list in the WHERE clause. During the query, the graph engine will compare each property with the values IN the list. You can place either numeric or string values in the list, but typically, elements of the list are of the same type of data. If you are testing with a property of a string type, then all the elements of the list will be strings.

In this example, we only want to retrieve Person nodes of people born in 1965 or 1970:

Here is the result:

You can also compare a value to an existing list in the graph.

We know that the :ACTED_IN relationship has a property, roles that contains the list of roles an actor had in a particular movie they acted in. Here is the query we write to return the name of the actor who played Neo in the movie The Matrix:

Here is the result:

This MATCH clause includes a pattern specified by two paths separated by a comma:

If possible, you write the same query as follows:

There are, however, some queries where you will need to specify two or more patterns. Multiple patterns are used when a query is complex and cannot be satisfied with a single pattern. This is useful when you are looking for a specific node in the graph and want to connect it to a different node. You will learn about creating nodes and relationships later in this training.

Here are some examples of specifying two paths in a MATCH clause. In the first example, we want the actors that worked with Keanu Reeves to meet Hugo Weaving, who has worked with Keanu Reeves. Here we retrieve the actors who acted in the same movies as Keanu Reeves, but not when Hugo Weaving acted in the same movie. To do this, we specify two paths for the MATCH:

When you perform this type of query, you may see a warning in the query edit pane stating that the pattern represents a cartesian product and may require a lot of resources to perform the query. Only perform these types of queries if you know the data well and the implications of doing the query.

Here is the result of executing this query:

Here is another example where two patterns are necessary. Suppose we want to retrieve the movies that Meg Ryan acted in and their respective directors, as well as the other actors that acted in these movies. Here is the query to do this:

Here is the result returned:

You have previously seen how you can assign a path used in a MATCH clause to a variable. This is useful if you want to reuse the path later in the same query or if you want to return the path. So the previous Cypher statement could return the path as follows:

Here is the result returned:

Any graph that represents social networking, trees, or hierarchies will most likely have multiple paths of varying lengths. Think of the connected relationship in LinkedIn and how connections are made by people connected to more people. The Movie database for this training does not have much depth of relationships, but it does have the :FOLLOWS relationship that you learned about earlier:

You write a MATCH clause where you want to find all of the followers of the followers of a Person by specifying a numeric value for the number of hops in the path. Here is an example where we want to retrieve all Person nodes that are exactly two hops away:

Here is the result returned:

If we had specified [:FOLLOWS*] rather than [:FOLLOWS*2], the query would return all Person nodes that are in the :FOLLOWS path from Paul Blythe.

Here are simplified syntax examples for how varying length patterns are specified in Cypher:

Retrieve all paths of any length with the relationship, :RELTYPE from nodeA to nodeB and beyond:

Retrieve all paths of any length with the relationship, :RELTYPE from nodeA to nodeB or from nodeB to nodeA and beyond. This is usually a very expensive query so you place limits on how many nodes are retrieved:

Retrieve the paths of length 3 with the relationship, :RELTYPE from nodeA to nodeB:

Retrieve the paths of lengths 1, 2, or 3 with the relationship, :RELTYPE from nodeA to nodeB, nodeB to nodeC, as well as, nodeC to _nodeD) (up to three hops):

You can learn more about varying paths in the Patterns section of the Neo4j Cypher Manual.

A built-in function that you may find useful in a graph that has many ways of traversing the graph to get to the same node is the shortestPath() function. Using the shortest path between two nodes improves the performance of the query.

In this example, we want to discover a shortest path between the movies The Matrix and A Few Good Men. In our MATCH clause, we set the variable p to the result of calling shortestPath(), and then return p. In the call to shortestPath(), notice that we specify * for the relationship. This means any relationship; for the traversal.

Here is the result returned:

Notice that the graph engine has traversed many types of relationships to get to the end node.

When you use the shortestPath() function, the query editor will show a warning that this type of query could potentially run for a long time. You must heed the warning, especially for large graphs. Read the Graph Algorithms documentation about the shortest path algorithm.

When you use ShortestPath(), you can specify a upper limits for the shortest path. In addition, aim to provide the patterns for the from an to nodes that execute efficiently. For example, use labels and indexes.

OPTIONAL MATCH matches patterns with your graph, just like MATCH does. The difference is that if no matches are found, OPTIONAL MATCH will use NULLs for missing parts of the pattern. OPTIONAL MATCH could be considered the Cypher equivalent of the outer join in SQL.

Here is an example where we query the graph for all people whose name starts with James. The OPTIONAL MATCH is specified to include people who have reviewed movies:

Here is the result returned:

Notice that for all rows that do not have the :REVIEWED relationship, a null value is returned for the movie part of the query, as well as the relationship.

Aggregation in Cypher is different from aggregation in SQL. In Cypher, you need not specify a grouping key. As soon as an aggregation function is used, all non-aggregated result columns become grouping keys. The grouping is implicitly done, based upon the fields in the RETURN clause.

For example, in this Cypher statement, all rows returned with the same values for a.name and d.name are counted and only returned once.

With this result returned:

Cypher has a built-in function, collect() that enables you to aggregate a value into a list. Here is an example where we collect the list of movies that Tom Cruise acted in:

Here is the result returned:

In Cypher, there is no "GROUP BY" clause as there is in SQL. The graph engine uses non-aggregated columns as an automatic grouping key.

The Cypher count() function is very useful when you want to count the number of occurrences of a particular query result. If you specify count(n), the graph engine calculates the number of occurrences of n. If you specify count(*), the graph engine calculates the number of rows retrieved, including those with null values. When you use count(), the graph engine does an implicit group by based upon the aggregation.

Here is an example where we count the paths retrieved where an actor and director collaborated in a movie and the count() function is used to count the number of paths found for each actor/director collaboration.

Here is the result returned:

There are more aggregating functions such as min() or max() that you can also use in your queries. These are described in the Aggregating Functions section of the Neo4j Cypher Manual.

During the execution of a MATCH clause, you can specify that you want some intermediate calculations or values that will be used for further processing of the query, or for limiting the number of results before further processing is done. You use the WITH clause to perform intermediate processing or data flow operations.

Here is an example where we start the query processing by retrieving all actors and their movies. During the query processing, we want to only return actors that have 2 or 3 movies. All other actors and the aggregated results are filtered out. This type of query is a replacement for SQL’s "HAVING" clause. The WITH clause does the counting and collecting, but is then used in the subsequent WHERE clause to limit how many paths are visited.

Here is the result returned:

When you use the WITH clause, you specify the variables from the previous part of the query you want to pass on to the next part of the query. In this example, the variable a is specified to be passed on in the query, but m is not. Since m is not specified to be passed on, m will not be available later in the query. Notice that for the RETURN clause, a, numMovies, and movies are available for use.

Here is another example where we want to find all actors who have acted in at least five movies, and find (optionally) the movies they directed and return the person and those movies.

Here is the result returned:

In this example, we first retrieve all people, but then specify a pattern in the WITH clause where we calculate the number of :ACTED_IN relationships retrieved using the size() function. If this value is greater than five, we then also retrieve the :DIRECTED paths to return the name of the person and the title of the movie they directed. In the result, we see that these actors acted in more than five movies, but Tom Hanks is the only actor who directed a movie and thus the only person to have a value for the movie.

You have seen a number of query results where there is duplication in the results returned. In most cases, you want to eliminate duplicated results. You do so by using the DISTINCT keyword.

Here is a simple example where duplicate data is returned. Tom Hanks both acted in and directed the movie, That Thing You Do, so the movie is returned twice in the result stream:

Here is the result returned:

We can eliminate the duplication by specifying the DISTINCT keyword as follows:

Here is the result returned:

Another way that you can avoid duplication is to with WITH and DISTINCT together as follows:

Here is the result returned:

If you want the results to be sorted, you specify the expression to use for the sort using the ORDER BY keyword and whether you want the order to be descending using the DESC keyword. Ascending order is the default. Note that you can provide multiple sort expressions and the result will be sorted in that order. Just as you can use DISTINCT with WITH to eliminate duplication, you can use ORDER BY with WITH to control the sorting of results.

In this example, we specify that the release date of the movies for Tom Hanks will be returned in descending order.

Here is the result returned:

Although you can filter queries to reduce the number of results returned, you may also want to limit the number of results. This is useful if you have very large result sets and you only need to see the beginning or end of a set of ordered results. You can use the LIMIT keyword to specify the number of results returned. Furthermore, you can use the LIMIT keyword with the WITH clause to limit results.

Suppose you want to see the titles of the ten most recently released movies. You could do so as follows where you limit the number of results using the LIMIT keyword as follows:

Here is the result returned:

Previously, you saw how you can use the WITH clause to perform some intermediate processing during a query. You can use the WITH clause to limit the number of results.

In this example, we count the number of movies during the query and we return the results once we have reached 5 movies:

Here is the result returned:

There are many built-in Cypher functions that you can use to build or access elements in lists. A Cypher map is list of key/value pairs where each element of the list is of the format key: value. For example, a map of months and the number of days per month could be:

[Jan: 31, Feb: 28, Mar: 31, Apr: 30 , May: 31, Jun: 30 , Jul: 31, Aug: 31, Sep: 30, Oct: 31, Nov: 30, Dec: 31]

You can collect values for a list during a query and when you return results, you can sort by the size of the list using the size() function as follows:

Here is the result returned:

You can read more about working with lists in the List Functions section of the Neo4j Cypher Manual.

There may be some situations where you want to perform the opposite of collecting results, but rather separate the lists into separate rows. This functionality is done using the UNWIND clause.

Here is an example where we create a list with three elements, unwind the list and then return the values. Since there are three elements, three rows are returned with the values:

Here is the result returned:

Notice that there is no MATCH clause. You need not query the database to execute Cypher statements, but you do need the RETURN clause here to return the calculated values from the Cypher query.

Cypher has a built-in date() function, as well as other temporal values and functions that you can use to calculate temporal values. You use a combination of numeric, temporal, spatial, list and string functions to calculate values that are useful to your application. For example, suppose you wanted to calculate the age of a Person node, given a year they were born (the born property must exist and have a value).

Here is an example Cypher to retrieve all actors from the graph, and if they have a value for born, calculate the age value.

Here is the result returned:

Consult the Neo4j Cypher Manual for more information about the built-in functions available for working with data of all types: