Advanced Cypher Queries

Building on the basics, this lesson covers advanced Cypher features including aggregation, UNWIND, CASE expressions, subqueries, variable-length paths, and WITH for query pipelining.

---

Aggregation Functions

Cypher provides built-in aggregation functions:

Function	Description	Example
count()	Count results	count(p)
sum()	Sum numeric values	sum(o.total)
avg()	Average	avg(p.age)
min()	Minimum value	min(p.age)
max()	Maximum value	max(p.age)
collect()	Collect values into a list	collect(p.name)
percentileCont()	Percentile (continuous)	percentileCont(p.age, 0.5)
stDev()	Standard deviation	stDev(p.age)

Counting

// Count all Person nodes
MATCH (p:Person)
RETURN count(p) AS totalPeople

// Count purchases per person
MATCH (p:Person)-[:PURCHASED]->(product:Product)
RETURN p.name, count(product) AS purchaseCount
ORDER BY purchaseCount DESC

Collecting into lists

// Collect all product names purchased by each person
MATCH (p:Person)-[:PURCHASED]->(product:Product)
RETURN p.name, collect(product.name) AS products

---

WITH — Query Pipelining

The WITH clause passes results from one part of a query to the next, enabling multi-step queries:

// Find people with more than 3 purchases
MATCH (p:Person)-[:PURCHASED]->(product:Product)
WITH p, count(product) AS purchaseCount
WHERE purchaseCount > 3
RETURN p.name, purchaseCount

// Top 5 buyers and their products
MATCH (p:Person)-[:PURCHASED]->(product:Product)
WITH p, count(product) AS total
ORDER BY total DESC
LIMIT 5
MATCH (p)-[:PURCHASED]->(product:Product)
RETURN p.name, total, collect(product.name) AS products

---

UNWIND — Expanding Lists

UNWIND takes a list and expands it into individual rows:

// Create nodes from a list
UNWIND ["Alice", "Bob", "Charlie"] AS name
MERGE (p:Person {name: name})
RETURN p

// Expand a collected list
MATCH (p:Person)-[:PURCHASED]->(product:Product)
WITH p, collect(product) AS products
UNWIND products AS product
RETURN p.name, product.name

Practical use: Batch creation from parameters

UNWIND [
  {name: "Alice", age: 30},
  {name: "Bob", age: 25},
  {name: "Charlie", age: 35}
] AS person
MERGE (p:Person {name: person.name})
SET p.age = person.age
RETURN p

---

CASE Expressions

Conditional logic in Cypher:

Simple CASE

MATCH (p:Person)
RETURN p.name,
  CASE p.age
    WHEN 30 THEN "Thirty"
    WHEN 25 THEN "Twenty-five"
    ELSE "Other"
  END AS ageLabel

Generic CASE

MATCH (p:Person)
RETURN p.name,
  CASE
    WHEN p.age < 18 THEN "Minor"
    WHEN p.age < 65 THEN "Adult"
    ELSE "Senior"
  END AS ageGroup

---

Variable-Length Paths

Traverse paths of varying lengths:

Syntax