Results, Aggregation and Lists

These examples use the sample graph from the matching page.

​

RETURN, DISTINCT and expressions

MATCH (n:Person)
RETURN n.name, n.age + 5 AS adjusted

MATCH (n:Person)
RETURN DISTINCT n.city

MATCH (n:Person)
RETURN n.name AS nm
ORDER BY size(nm) DESC, nm

MATCH (n:Person)
RETURN *

RETURN * returns every variable in scope (nodes as IDs). A column without an alias is named after its expression, for example n.name.

​

ORDER BY, SKIP and LIMIT

MATCH (n:Person)
RETURN n.name, n.age
ORDER BY n.age

MATCH (n:Person)
RETURN n.name, n.age
ORDER BY n.age DESC

MATCH (n:Person)
RETURN n.name, n.city, n.age
ORDER BY n.city, n.age DESC

MATCH (n:Person)
RETURN n.name, n.age
ORDER BY n.age DESC SKIP 1 LIMIT 2

• You can sort by several keys, by aliases and by expressions. Ascending is the default.
• SKIP and LIMIT combine for pagination: SKIP 10 LIMIT 10 returns the 11th to 20th rows.
• SKIP and LIMIT take constant expressions.
• RETURN DISTINCT ... ORDER BY can only sort by returned columns.
• Nulls sort last in ascending order and first in descending order.

​

WITH

WITH pipes the result of one part of a query into the next. It is how you filter on aggregates and chain several stages:

MATCH (n:Person)
WITH n
WHERE n.age > 28
RETURN n.name

MATCH (n:Person)
WITH n.name AS name, n.age AS age
ORDER BY age DESC LIMIT 2
RETURN name, age

MATCH (n:Person)
WITH n
WHERE n.age > 26
WITH n
WHERE n.age < 36
RETURN n.name

MATCH (n:Person)
WITH n.city AS city, count(*) AS c
WHERE c > 1
RETURN city, c

​

UNION

MATCH (p:Person)
RETURN p.name AS name
UNION
MATCH (c:Company)
RETURN c.name AS name

MATCH (p:Person)
RETURN p.name AS name
UNION ALL
MATCH (c:Company)
RETURN c.name AS name

UNION removes duplicates and UNION ALL keeps them. All branches must return the same column names, and each column must keep one value type across the branches.

​

Aggregation

Grouping is implicit, as in Neo4j: the non-aggregated items in the RETURN are the grouping keys.

MATCH (a:Person)-[:WORKS_AT]->(c:Company)
RETURN c.name, count(a) AS employees, collect(a.name) AS who, avg(a.age) AS avgAge
ORDER BY employees DESC

MATCH (n:Person)
RETURN count(*), count(n.age), sum(n.age), avg(n.age), min(n.age), max(n.age)

MATCH (a)-[w:WORKS_AT]->(c)
RETURN w.role, count(DISTINCT c) AS companies

MATCH (n:Person)
RETURN n.city, max(n.age) - min(n.age) AS spread

MATCH (n:Person)
RETURN sum(CASE WHEN n.age > 30 THEN 1 ELSE 0 END) AS seniors

MATCH (n:Person)
RETURN collect(n.name)[0..3] AS firstThree

MATCH ()-[r]->()
RETURN type(r) AS t, count(*) AS c
ORDER BY c DESC

MATCH (n)
RETURN labels(n) AS l, count(*) AS c
ORDER BY c DESC

Statistical aggregates such as stdev and percentileCont are not yet available. Compute them in your application, for example in pandas.

Rules:

• WHERE runs before aggregation, so to filter on an aggregate, compute it in a WITH first and filter on its alias (see the example below).
• To aggregate an aggregate, compute the first one in a WITH and aggregate the result in the next stage.
• Aggregates can be combined with each other and with constants in one expression (max(n.age) - min(n.age), count(*) * 10). To use a property in the same expression, return it as its own item so it is a grouping key: RETURN n.age, n.age + count(*) works.
• With no grouping keys, an empty input still returns one row: count is 0, sum is 0, avg is null and collect is []. With grouping keys, an empty input returns no rows.

​

Filtering on an aggregate

MATCH (n:Person)
WITH n.city AS city, count(*) AS people
WHERE people > 1
RETURN city, people

​

UNWIND

UNWIND expands a list into one row per element. It accepts any list expression, including lists you computed earlier in the query:

UNWIND [1, 2, 3] AS x
RETURN x * 10

UNWIND range(1, 10, 3) AS x
RETURN x

MATCH (n:Person)
WITH collect(n.name) AS names
UNWIND names AS nm
RETURN nm

MATCH (n:Person)
WITH collect(n) AS ns
UNWIND ns AS p
RETURN p.name

MATCH (n:Person)
UNWIND n.tags AS t
RETURN t, count(*)

range(start, end[, step]) includes both bounds. Unwound nodes stay nodes, so p.name works after UNWIND ns AS p. You can also UNWIND a list of maps and read their keys: UNWIND [{a: 1}, {a: 2}] AS m RETURN m.a.

​

Lists

RETURN [1, 2, 3, 4, 5][0], [1, 2, 3, 4, 5][-1], [1, 2, 3, 4, 5][1..3], [1, 2, 3][10]

RETURN size([1, 2]), head([1, 2]), last([1, 2]), tail([1, 2]), reverse([1, 2, 3])

MATCH (n:Person {name: 'Alice'})
RETURN n.tags[0], size(n.tags)

RETURN [1, 2] + [3], 'a' || 'b'

• Indexing and slicing work on literals, list properties and function results (labels(n)[0]). Negative indexes count from the end, and an out-of-range index returns null.
• Lists and maps compare by value with =.
• size(null) and head([]) return null.

​

List comprehensions

RETURN [x IN range(1, 6) WHERE x % 2 = 0 | x * x]

MATCH (a:Person)
RETURN a.name, [(a)-[:KNOWS]->(b) | b.name] AS friends

MATCH (n:Person)
WHERE any(t IN n.tags WHERE t = 'vip')
RETURN n.name

RETURN reduce(total = 0, x IN [1, 2, 3] | total + x)

• [x IN list WHERE condition | expression] filters and maps a list.
• A pattern comprehension [(a)-[:KNOWS]->(b) | b.name] collects a value for every match of a pattern.
• all, any, none and single test a condition over a list: any(t IN n.tags WHERE t = 'vip').
• reduce(acc = init, x IN list | expression) folds a list into a single value.

​

CASE

MATCH (n:Person)
RETURN n.name,
  CASE WHEN n.age >= 35 THEN 'senior' WHEN n.age IS NULL THEN 'unknown' ELSE 'junior' END AS band,
  CASE n.city WHEN 'London' THEN 'UK' WHEN 'Paris' THEN 'FR' ELSE 'other' END AS country

The simple form (CASE n.city WHEN 'London' THEN ...) and the searched form (CASE WHEN condition THEN ...) both work. TuringDB also accepts comparison tests in the simple form, such as CASE n.age WHEN > 30 THEN 'old' WHEN IS NULL THEN '?' END.