Skip to main content

Advanced Cypher: paths, aggregations and recommendation

The News graph is in place: 200,853 articles, 41 categories, 23,082 authors, loaded in twenty-five seconds in module 11. Karim now wants to add to the Veille platform a feature customers have been asking for: "for this author, suggest three nearby authors to keep an eye on". Inès sets the constraint: the recommendation must fit into one readable Cypher query, lean on the graph and not on a heavy statistical model. This module writes eight progressive queries answering questions of increasing richness, up to the neighborhood recommendation, drawing on parameters, PROFILE, shortestPath and variable-length patterns.

Set a parameter: :param and $name​

Every query that follows talks about a reference author. Rather than writing "Lee Moran" twenty times, set it once in Neo4j Browser as a session parameter.

:param nom => 'Lee Moran'

:param is a Browser command (like :auto in module 11), not a language feature. It stores $nom for the whole session: every query can then use $nom without worrying about the value. In ./lab.sh cypher, parameters are passed instead from Python or from the cypher-shell command line. The key point: parameters are not string concatenation, they are real variables — the execution plan is cached and reused, and Cypher injection is impossible.

Always parameters, never concatenation

In real application code (module 13), we write session.run("MATCH (au:Auteur {nom: $nom}) ...", nom="Lee Moran"). Concatenating the value into the string blows the plan cache and opens a Cypher injection. The rule holds for Elasticsearch and for any modern database.

Query 1 — an author's favorite categories​

Business question: "in which categories does Lee Moran write the most, and how many articles does each category represent?". It is a MATCH on a three-node pattern, followed by a count.

MATCH (au:Auteur {nom: $nom})<-[:ECRIT_PAR]-(a:Article)-[:PUBLIE_DANS]->(c:Categorie)
RETURN c.nom AS categorie, count(a) AS articles
ORDER BY articles DESC
LIMIT 10;

Reading the pattern: we start from the author, walk up the articles they wrote (incoming arrow <-[:ECRIT_PAR]-), and go down to their category (outgoing arrow -[:PUBLIE_DANS]->). Two traversals, no explicit join.

Expected result for $nom = 'Lee Moran':

categorie      | articles
---------------+---------
COMEDY         |     779
WEIRD NEWS     |     391
ENTERTAINMENT  |     387
POLITICS       |     264
SPORTS         |     101
...

Total across all categories: 2,433 articles — Lee Moran's prolific count, first met in module 4.

Query 2 — nearby authors by category neighborhood​

The heart of the recommendation: find authors who write in the same categories as Lee, and rank them by number of shared categories.

MATCH (au:Auteur {nom: $nom})<-[:ECRIT_PAR]-(:Article)-[:PUBLIE_DANS]->(c:Categorie)
MATCH (c)<-[:PUBLIE_DANS]-(:Article)-[:ECRIT_PAR]->(autre:Auteur)
WHERE autre <> au
RETURN autre.nom AS auteur, count(DISTINCT c) AS categories_communes, count(*) AS articles_partages
ORDER BY categories_communes DESC, articles_partages DESC
LIMIT 10;

Two chained MATCHes (the two views of the same path), a WHERE autre <> au filter that removes Lee from his own neighborhood (without this line, the author closest to Lee would always be Lee himself), and two counters — the number of distinct shared categories and the raw volume of articles concerned. The second breaks ties between authors equal on the first.

This is a content-similarity recommendation: we do not try to imitate a machine-learning algorithm, we exploit the graph's topology. The top-ranked authors are those who write in Lee's five favorite categories; they will make good candidates for the "also read" page. (Your names may differ slightly depending on how multi-author cleanup went.)

Query 3 — recent articles by co-authors of a given article​

Business question: "for article number 100, who co-signed it, and what have they published most recently elsewhere?". This is a two-step path that uses WITH to carry the authors into the second step.

MATCH (:Article {id: 100})-[:ECRIT_PAR]->(au:Auteur)
WITH au
MATCH (au)<-[:ECRIT_PAR]-(autre:Article)
RETURN au.nom AS auteur, autre.titre AS titre, autre.date AS date
ORDER BY autre.date DESC
LIMIT 5;

WITH acts here like a pipe: take the authors found at step 1, feed them into step 2. Without WITH, each MATCH restarts from scratch and the logical link is lost. The ORDER BY autre.date DESC benefits from the article_date index set in module 11: we do not sort 200,000 rows, we walk the index in reverse order.

Query 4 — the shortest path between two authors​

A purely graph-oriented question: "how far apart do Lee Moran and Ed Mazza meet in the graph?". Answer with shortestPath and a variable-length pattern.

MATCH (a:Auteur {nom: 'Lee Moran'}), (b:Auteur {nom: 'Ed Mazza'})
MATCH chemin = shortestPath((a)-[*..6]-(b))
RETURN [n IN nodes(chemin) | coalesce(n.nom, n.titre)] AS etapes,
       length(chemin) AS longueur;

shortestPath((a)-[*..6]-(b)) asks for the shortest path, across all relationships, up to six hops. The upper bound is essential: without it, Neo4j could explore the entire graph.

The path typically goes through a shared article (Auteur → Article → Auteur, length 2) or a shared category (Auteur → Article → Categorie → Article → Auteur, length 4). The comprehension [n IN nodes(chemin) | coalesce(n.nom, n.titre)] builds the readable list of stops, picking nom if it is an author or a category and titre if it is an article. (The exact length depends on the co-signatures in your load.)

Always bound the path length

shortestPath((a)-[*]-(b)) without a bound explores the entire connected component breadth-first. On a graph of 200,000 nodes, that is catastrophic. The Veille convention: never more than six hops for a proximity search.

Query 5 — variable-length paths *1..3​

shortestPath returns one path. To get all paths up to a given length, use the *min..max quantifier directly on the relationship.

MATCH chemin = (a:Auteur {nom: 'Lee Moran'})-[*1..3]-(voisin:Auteur)
WHERE voisin <> a
RETURN voisin.nom AS voisin, length(chemin) AS distance
ORDER BY distance ASC, voisin.nom
LIMIT 10;

*1..3 allows 1, 2 or 3 relationships in any direction (the pattern no longer has an oriented > arrow). The WHERE voisin <> a filter drops the starting author. This query costs more than the previous one (it explores everything, not just the shortest) and lends itself well to a strict LIMIT.

In practice, for recommendation, query 2 is preferable — faster, more readable and more interpretable. Variable-length paths shine mainly for detecting a connection ("is there a link at three hops between X and Y?") rather than for ranking one.

Query 6 — OPTIONAL MATCH, WITH and collect​

Business question: "for each author in the top 5 by volume, what are their three most recent articles, and their main category?". Two new bricks: OPTIONAL MATCH, which does not fail if the pattern is empty, and collect, which groups rows into a list.

MATCH (au:Auteur)<-[:ECRIT_PAR]-(a:Article)
WITH au, count(a) AS total
ORDER BY total DESC
LIMIT 5
OPTIONAL MATCH (au)<-[:ECRIT_PAR]-(recent:Article)
WITH au, total, recent
ORDER BY recent.date DESC
WITH au, total, collect(recent.titre)[..3] AS derniers_titres
RETURN au.nom AS auteur, total, derniers_titres;

Three WITHs in cascade. The first computes the total and keeps the top five authors. The second sorts the recent articles. The third groups them into a list with collect, then keeps the first three with the slice [..3]. OPTIONAL MATCH covers the rare (but possible) case of an author left with no article after cleanup.

Expected result (the top 5 is stable): Reuters, Lee Moran, Ron Dicker, Ed Mazza, Cole Delbyck, each with their three most recent titles. (The exact titles vary with the selection; their count per author does not.)

Query 7 — UNWIND and CASE: rank authors​

UNWIND does the opposite of collect: it turns a list into one row per element. CASE categorizes a numerical value into a label.

MATCH (au:Auteur)<-[:ECRIT_PAR]-(a:Article)
WITH au, count(a) AS total
WITH
  CASE
    WHEN total >= 500 THEN 'prolifique'
    WHEN total >= 50  THEN 'régulier'
    ELSE                   'occasionnel'
  END AS niveau,
  au, total
RETURN niveau, count(au) AS auteurs, avg(total) AS moyenne
ORDER BY
  CASE niveau WHEN 'prolifique' THEN 1 WHEN 'régulier' THEN 2 ELSE 3 END;

Three thresholds, three categories. A CASE on total produces the niveau label, a second CASE serves as sort key (alphabetical order would give "occasionnel", "prolifique", "régulier"). Expected result: the "prolific" authors are a handful (Reuters, Lee Moran, Ron Dicker, Ed Mazza, Cole Delbyck, Andy McDonald, David Moye, Mary Papenfuss…), the "regulars" are a few hundred, the "occasionals" are the vast majority — the overwhelming long tail. (Exact numbers vary with how co-authors were split.)

A pure UNWIND example on this graph, to unfold Lee's favorite categories into a single table:

MATCH (au:Auteur {nom: $nom})<-[:ECRIT_PAR]-(a:Article)-[:PUBLIE_DANS]->(c:Categorie)
WITH au, collect(DISTINCT c.nom) AS categories
UNWIND categories AS categorie
RETURN au.nom AS auteur, categorie
ORDER BY categorie;

Query 8 — PROFILE before and after an indexed WHERE​

EXPLAIN shows the execution plan without running the query; PROFILE runs it and reports the real cost in db hits (logical disk accesses). It is the tool to understand why a query is slow and to check that an index truly earns its keep.

Version 1 — no indexed filter:

PROFILE
MATCH (a:Article)-[:PUBLIE_DANS]->(:Categorie {nom: 'POLITICS'})
WHERE a.titre CONTAINS 'trump'
RETURN count(a);

The plan starts with a NodeIndexSeek on the categorie_nom constraint (lookup by name, instantaneous), then goes down to the articles with Expand(All), then applies the CONTAINS 'trump' filter article by article — a Filter line with a high db hits counter, proportional to the number of POLITICS articles (32,739).

Version 2 — with an indexed filter:

PROFILE
MATCH (a:Article)-[:PUBLIE_DANS]->(:Categorie {nom: 'POLITICS'})
WHERE a.date >= date('2018-01-01')
RETURN count(a);

This time, the date constraint benefits from the article_date index created in module 11. The plan shows a NodeIndexSeekByRange on Article(date), and the db hits count drops by an order of magnitude: we start straight from the recent articles rather than sweeping POLITICS.

What to read in a PROFILE: the db hits count per step (lower is better), the NodeByLabelScans (to avoid on large labels), the Expand(All) (traversals, normal) and the Filters (costly filters after a traversal signal a missing index or a query to reformulate).

EXPLAIN vs PROFILE

EXPLAIN is free: it runs nothing, it counts estimates. PROFILE runs the query for real — avoid it on a destructive query and think twice before launching PROFILE MATCH (n) DETACH DELETE n.

The trap to avoid: the Cartesian product​

The following pattern compiles but produces a disaster:

MATCH (a:Auteur), (b:Auteur)
WHERE a <> b
RETURN a.nom, b.nom
LIMIT 10;

No relationship between a and b: Neo4j computes the Cartesian product of the two labels, i.e. 23,082 × 23,082 ≈ 533 million pairs, before filtering and keeping ten rows. Neo4j prints an explicit warning ("This query builds a cartesian product…").

The right form always ties the nodes with a relationship, even a long one:

MATCH (a:Auteur)-[:ECRIT_PAR|PUBLIE_DANS*1..4]-(b:Auteur)
WHERE a <> b
RETURN a.nom, b.nom
LIMIT 10;

Every time you write two MATCHes (or two nodes separated by a comma) with no pattern linking them, reread: either you wanted a WITH, or you wanted a path.

Massive updates with apoc.periodic.iterate​

A recurring case: add a computed property to hundreds of thousands of nodes. Written in a single transaction, the command blows the heap. Written with apoc.periodic.iterate, the work happens in batches.

CALL apoc.periodic.iterate(
  'MATCH (a:Article) RETURN a',
  'SET a.annee = a.date.year',
  {batchSize: 10000, parallel: false}
) YIELD batches, total
RETURN batches AS lots, total AS articles_mis_a_jour;

Two queries, separated by a semicolon in the string: the producer (MATCH ... RETURN) and the consumer (SET ...). APOC pipes one into the other in batches of 10,000, commits between each, and returns the total at the end. That is the "write" version of the LOAD CSV ... IN TRANSACTIONS pattern.

Try it 1 — Category share by author​

Write the query that gives, for an author passed as parameter, their five most frequent categories and the percentage of articles they represent compared to their total.

Solution
MATCH (au:Auteur {nom: $nom})<-[:ECRIT_PAR]-(a:Article)
WITH au, count(a) AS total
MATCH (au)<-[:ECRIT_PAR]-(a:Article)-[:PUBLIE_DANS]->(c:Categorie)
WITH c.nom AS categorie, count(a) AS articles, total
RETURN categorie, articles, round(100.0 * articles / total, 1) AS pourcentage
ORDER BY articles DESC
LIMIT 5;

For Lee Moran, COMEDY comes out at about 32%, WEIRD NEWS at 16%, ENTERTAINMENT at 16%, POLITICS at 11%, SPORTS at 4%.

Try it 2 — Top 3 authors near Lee Moran with a volume filter​

Find the three authors closest to Lee Moran by shared categories, including only authors who have written at least 20 articles in total (to filter out noise).

Solution
MATCH (au:Auteur {nom: 'Lee Moran'})<-[:ECRIT_PAR]-(:Article)-[:PUBLIE_DANS]->(c:Categorie)
MATCH (c)<-[:PUBLIE_DANS]-(:Article)-[:ECRIT_PAR]->(autre:Auteur)
WHERE autre <> au
WITH autre, count(DISTINCT c) AS categories_communes
MATCH (autre)<-[:ECRIT_PAR]-(a:Article)
WITH autre, categories_communes, count(a) AS total_autre
WHERE total_autre >= 20
RETURN autre.nom AS auteur, categories_communes, total_autre
ORDER BY categories_communes DESC, total_autre DESC
LIMIT 3;

The second MATCH with count(a) lets us filter marginal authors. (Your number may differ slightly.)

Try it 3 — Compare PROFILE with and without an indexed filter​

Compare PROFILE on the same query with and without a WHERE a.date >= date('2018-01-01'), and note the approximate db hits ratio.

Solution
PROFILE
MATCH (a:Article)-[:PUBLIE_DANS]->(:Categorie {nom: 'POLITICS'})
RETURN count(a);

then

PROFILE
MATCH (a:Article)-[:PUBLIE_DANS]->(:Categorie {nom: 'POLITICS'})
WHERE a.date >= date('2018-01-01')
RETURN count(a);

The first version walks the 32,739 POLITICS articles; the second restricts the traversal thanks to the article_date index. The db hits ratio is typically an order of magnitude (factor 5 to 15 depending on the yearly distribution of articles).

Key takeaways​

  • Set a parameter with :param nom => 'Lee Moran' then use $nom: plan cache respected, Cypher injection impossible.
  • The neighborhood recommendation fits into one double pattern: "articles → shared categories → other authors", with WHERE autre <> au and count(DISTINCT c) sorted.
  • shortestPath((a)-[*..N]-(b)) with a bound finds proximity between two nodes; without a bound, the query explodes.
  • WITH chains steps, collect groups into a list, UNWIND unfolds it, CASE categorizes a numerical value into a label.
  • PROFILE reveals the real cost in db hits: a NodeIndexSeekByRange replaces a costly Filter as soon as you filter on an indexed property.
  • Two MATCHes with no relationship between them = Cartesian product: Neo4j warns you, reread the query.
  • Massive updates go through apoc.periodic.iterate with a producer query and a consumer query, never in a single transaction.

Troubleshooting​

  • The query runs forever (spinner never stops) → unbounded path (*.. or *) or Cartesian product → interrupt in the Browser, add a bound *..6 or a relationship between the two nodes, then PROFILE the new version.
  • $nom returns "Expected parameter(s): nom" → the value was not set for the session → :param nom => 'Lee Moran' in Neo4j Browser, or pass the parameter from Python.
  • PROFILE shows a NodeByLabelScan on Article → no constraint was met before the filter → check that the pattern starts with the most selective part (Categorie with nom, for example), otherwise create an index with CREATE INDEX ... on the filtered property.
  • apoc.periodic.iterate returns "Unknown function" → APOC is not loaded in the container → ./lab.sh logs neo4j (look for "Loaded apoc"), otherwise ./lab.sh reset then ./lab.sh up.

Further reading​

Next module: drive Elasticsearch and Neo4j from Python, with the official clients preinstalled in the veille-python container, and write the script that combines search and recommendation for the final demonstration.