List Expressions, UNION, and Subqueries

We have looked at filtering and sorting in Cypher queries in earlier chapters. In this chapter, we will take a look at more advanced options for querying.

We will cover the following topics:

• Working with list expressions
• Working with UNION in Cypher
• Working with subqueries

List expressions provide a powerful paradigm to manipulate list results in Cypher. UNION queries provide the means to combine the results of distinct queries and return data. Subqueries provide a powerful option for executing a query inside another query and using the results in the main query.

Now, let us take a look at list expressions.

Working with list expressions

Cypher provides native support for lists. This means that not only are they treated as first-class entities, such as integers or strings, but all the functions that can create, manipulate, or process the lists are built into Cypher. Let us look at the following functions, all of which are available to process lists:

• range
• head
• tail
• last
• size
• reverse
• reduce

As well as these functions, we can also use list comprehensions. First, we will take a look at the preceding functions, and then we will explore list comprehensions in greater depth.

Let us look at the range function.

Working with the range function

The range function provides a way to create a list with numbers. It takes a start value and an end value with an optional step parameter and returns a list of all integer values bound by start and end. The syntax of the range function is as follows:

range(start, end [, step])

The step value is optional...

Working with UNION in Cypher

The UNION clause combines the results of two or more queries and returns the results. It works pretty similarly to how it works in SQL queries. Normally, we use the UNION clause when we want to combine the results of multiple, disparate queries returning similar datasets.

Let’s look at an example usage of the UNION clause:

MATCH (p:Patient)-[:HAS_ENCOUNTER]->()-[:HAS_DIAGNOSIS]->(d)
WHERE p.id='f237e253-9052-a038-7c9e-dbd9a1d7da32'
RETURN d.code as drug
UNION
MATCH (p:Patient)-[:HAS_ENCOUNTER]->()-[:HAS_DIAGNOSIS]->(d)
WHERE p.id='ffa580de-08e5-9a47-b12a-db312ad6825b'
RETURN d.code as drug

This query returns the diagnosis codes used among two patients, as shown in the following figure:

Figure 6.13 – Usage of the UNION clause

We can see there are five records returned. The UNION clause eliminates duplicate records in the results if there are any.

We could have written the...

Working with subqueries

Cypher allows you to write subqueries using the CALL clause. There are two types of subqueries available:

• Returning subqueries
• Unit subqueries

The subqueries are evaluated for each incoming row that is provided by the parent query.

Let us work with returning subqueries first.

Working with returning subqueries

Subqueries that end with a RETURN statement are called returning subqueries. Every row from a returning subquery is combined with the input row to prepare the result of the query. This means the final output of the outer query can be impacted by the subquery returned values.

Note

If a subquery does not return any rows, then there will not be any rows returned by the outer query.

Returning subqueries are very useful when we want to apply sorting and extra filtering to UNION queries. When we use the UNION clause, it is not possible to apply any sorting. Let us take a look at a UNION query with sorting using a subquery...

Summary

In this chapter, we have learned about using list expressions, UNION queries, and subqueries. We worked with multiple functions to process lists, using the reduce function to calculate a single value by iterating through a list, using list comprehensions to manipulate lists, combining list comprehensions and the reduce function with filter expressions to calculate a single value, using UNION to combine the results of multiple queries, using UNION ALL to keep the duplicates from multiple queries, using subqueries to apply to filters and for sorting UNION queries, using subqueries to perform isolated updates, and finally using the IN TRANSACTIONS clause, along with subqueries, to perform batch updates in separate transactions.

In the next chapter, we will take a deeper look at how lists and maps form the core of Cypher data types, and how these data types can make working with data much easier.