Skip to main content

RETURN and WITH — Projecting and Piping Results

Both clauses project rows forward. WITH hands the projected rows to the next clause; RETURN ends the query and hands them back to the caller. Rows typically come from a preceding MATCH or UNWIND.

Think of WITH as a pipe between stages, and RETURN as the output of the final stage.

Overview​

GoalClause
Shape the final outputRETURN
Rename a columnRETURN expr AS name
Deduplicate rowsDISTINCT
Sort / paginateORDER BY, SKIP, LIMIT
Build a subset-map per entityMap projection
Conditional per-row valueCASE … WHEN … THEN … END
Pipe into the next stageWITH
HAVING-style filteringWITH … WHERE
Combine two result setsUNION / UNION ALL

RETURN​

Basic projection​

Return whole entities, bare properties, or any expression.

MATCH (n) RETURN n;
MATCH (n) RETURN n.name, n.age;
MATCH (n) RETURN n.name AS userName;
MATCH (n) RETURN n.age * 2 AS doubled_age

Aliases (AS) set the column name in the host response. Reserve them for anything the consumer has to look up by key.

Star​

RETURN * projects every variable in scope. Handy for exploratory work, noisy for production queries.

MATCH (a)-[r]->(b) RETURN *;
MATCH (a)-[r]->(b) RETURN *, a.name AS name

Literal expressions​

Return constants, function calls, arithmetic:

RETURN 1 + 2 AS three;
RETURN temporal.timestamp() AS now_ms;
RETURN temporal.now() AS now, temporal.today() AS today;
RETURN 'hello, ' + $name AS greeting

DISTINCT​

Deduplicate the output rows. Applies to the full row, not per-column.

MATCH (n) RETURN DISTINCT n.city;
MATCH (p:Person)-[:WROTE]->(:Post) RETURN DISTINCT p

DISTINCT runs before ORDER BY and is expensive on large inputs — prefer filtering with WHERE first.

ORDER BY, SKIP, LIMIT​

Shape the final result set. Full reference: Ordering & Pagination.

MATCH (n) RETURN n ORDER BY n.name ASC;
MATCH (n) RETURN n ORDER BY n.last ASC, n.first DESC;
MATCH (n) RETURN n ORDER BY n.name DESC SKIP 5 LIMIT 10;
MATCH (n) RETURN n LIMIT 1

SKIP and LIMIT take a non-negative integer. null (for example an unbound $limit), a negative number or a fraction is an error, not "no limit".

Map projection​

Shape a node or relationship into a map with only the keys you want — useful when the consumer doesn't need every property.

// Pick a subset
MATCH (n:User) RETURN n {.name, .age}

;// All properties (equivalent to \`properties(n)\`)
MATCH (n:User) RETURN n {.*}

;// Rename + compute
MATCH (n:User) RETURN n {.name, score: n.age * 2}

;// Include related data
MATCH (u:User)
RETURN u {.name, posts: [(u)-[:WROTE]->(p) | p.title]}

See also Lists & Maps → Map projection.

CASE expressions​

CASE is LoraDB's conditional expression — the Cypher equivalent of SQL's CASE or a ternary. It's a plain expression, so it works anywhere a value is allowed: RETURN, WITH, SET, ORDER BY, and inside predicates.

Two forms.

Simple form — match an input against successive values:

MATCH (p:Product)
RETURN p.name,
     CASE p.tier
       WHEN 'gold'   THEN 1.2
       WHEN 'silver' THEN 1.1
       WHEN 'bronze' THEN 1.0
       ELSE                0.9
     END AS multiplier

Generic form — each branch is its own boolean expression:

MATCH (o:Order)
RETURN o.id,
     CASE
       WHEN o.amount >= 1000 THEN 'large'
       WHEN o.amount >= 100  THEN 'medium'
       WHEN o.amount >= 10   THEN 'small'
       ELSE                       'tiny'
     END AS bucket

The generic form is the one you'll reach for most often — it allows arbitrary predicates per branch, including null-safe checks and pattern-based predicates.

ELSE is optional​

Omitting ELSE implicitly falls through to null:

MATCH (u:User)
RETURN u.name,
     CASE WHEN u.score > 100 THEN 'pro' END AS tier
// tier is null for users at or below 100

Branches are short-circuit​

Branches evaluate top-to-bottom; the first matching WHEN wins. Place the narrowest condition first if branches overlap.

Type coercion across branches​

Every branch — including the implicit null from a missing ELSE — can return any type. Nothing forces uniformity. Most callers prefer one type per CASE for predictable downstream shape:

RETURN CASE WHEN $has_value THEN $value ELSE null END AS maybe

In predicates and filters​

CASE is an expression, so it composes inside WHERE and ORDER BY:

MATCH (p:Product)
WHERE CASE
      WHEN p.on_sale THEN p.sale_price
      ELSE                p.price
    END < $max
RETURN p
MATCH (t:Task)
RETURN t
ORDER BY CASE t.status
         WHEN 'urgent' THEN 0
         WHEN 'open'   THEN 1
         ELSE               2
       END, t.created_at

That ordering pattern is how you express "custom priority order" — ASCII/byte order on the status string would give you open, urgent, not what you want.

In SET and aggregates​

MATCH (u:User)
SET u.tier = CASE WHEN u.score >= 100 THEN 'pro' ELSE 'free' END
MATCH (r:Review)
RETURN r.product,
     count(CASE WHEN r.stars >= 4 THEN 1 END) AS positive,
     count(CASE WHEN r.stars <= 2 THEN 1 END) AS negative

Combining CASE with count(expr) is the idiomatic way to express "count rows that satisfy X" inside a larger aggregation — count skips null, so the missing ELSE branch is exactly what you want.

See also​

WITH​

WITH is the pipe of Cypher. Use it to split a query into stages. The projected rows of one stage become the input rows of the next.

Piping variables​

The simplest WITH — pass the bindings through untouched:

MATCH (a)-[r]->(b)
WITH a, r, b
RETURN a, r, b

That's pedagogical; a real query uses WITH to change something.

Transforming between stages​

WITH can rename, compute, filter, aggregate — anything RETURN does at the end of the pipeline.

MATCH (u:User)
WITH u, u.born AS year
WHERE year < 1900
RETURN u.name, year

HAVING-style filtering (WITH)​

Aggregates are not allowed in WHERE. Aggregate into a WITH, then filter:

MATCH (p:Person)-[:WORKS_AT]->(c:Company)
WITH c.name AS company, count(p) AS employees
WHERE employees > 5
RETURN company, employees

See Aggregation → HAVING-style filtering.

Renaming and shaping​

MATCH (n:User)
WITH n.name AS username
RETURN username

Ordering inside a pipeline​

ORDER BY and LIMIT attach to a WITH stage just like they do to a final RETURN. Only surviving rows move forward.

MATCH (n:User)
WITH n
ORDER BY n.age DESC
LIMIT 3
MATCH (n)-[:FOLLOWS]->(other)
RETURN n.name, other.name

Chaining multiple WITH stages​

MATCH (o:Order)-[:CONTAINS]->(i:Item)
WITH o, sum(i.price) AS total
WHERE total > 100
WITH o, total
ORDER BY total DESC
LIMIT 20
RETURN o.id, total

Each stage's output columns become the next stage's bindings — any variable not projected is dropped.

Losing variables through WITH​

A variable must be explicitly projected into WITH to survive. This is a common source of Unknown variable errors:

MATCH (a:User)-[r:KNOWS]->(b)
WITH a         // r and b drop out of scope here
RETURN a, r    // error: r is not in scope

Either pipe them through (WITH a, r, b) or don't bind them in the first place.

UNION / UNION ALL​

Combine two result sets that share a column shape. UNION deduplicates; UNION ALL doesn't.

MATCH (n:User)    RETURN n.name AS name
UNION
MATCH (n:Product) RETURN n.name AS name
MATCH (a:A) RETURN a.v AS v
UNION ALL
MATCH (b:B) RETURN b.v AS v
UNION ALL
MATCH (c:C) RETURN c.v AS v

ORDER BY / LIMIT across UNION​

Apply at the very end — they shape the combined result:

MATCH (n:User)    RETURN n.name AS name
UNION ALL
MATCH (n:Product) RETURN n.name AS name
ORDER BY name
LIMIT 10

Column shape must match​

Both sides must expose the same column names in the same order:

// Valid
MATCH (n:User)    RETURN n.name AS name, 'user'    AS kind
UNION ALL
MATCH (n:Product) RETURN n.name AS name, 'product' AS kind

;// Invalid — column shape mismatch
MATCH (n:User)    RETURN n.name, 'user'
UNION
MATCH (n:Product) RETURN n.name, n.price, 'product'

UNION inside CALL ​

A UNION inside a CALL { } body combines the branches once per outer row. The query after the CALL can then sort, page and count the combined rows:

CALL {
MATCH (f:ImageFile) RETURN f
UNION ALL
MATCH (f:VideoFile) RETURN f
}
WITH f ORDER BY f.size DESC LIMIT 20
RETURN f.key AS key, f.size AS size

Each branch sees the outer row's variables, but not the variables of the branch before it. Every branch ends in RETURN with the same column names in the same order. Plain UNION drops duplicate rows within each outer row, so the same row can still appear once for each outer row:

MATCH (p:Person)
CALL {
WITH p MATCH (p)-[:LIKES]->(t) RETURN t.name AS thing
UNION
WITH p MATCH (p)-[:OWNS]->(t) RETURN t.name AS thing
}
RETURN p.name AS person, thing

Common patterns​

Pick N, then follow​

Top-3 users by age, then project their friends:

MATCH (u:User)
WITH u ORDER BY u.age DESC LIMIT 3
MATCH (u)-[:FOLLOWS]->(f)
RETURN u.name, collect(f.name) AS following

Count-and-rank​

MATCH (u:User)-[:WROTE]->(p:Post)
WITH u, count(p) AS posts
ORDER BY posts DESC
LIMIT 10
RETURN u.name, posts

Project the top of a nested list​

MATCH (p:Person)
RETURN p.name,
     [(p)-[:KNOWS]->(f) | f.name][..5] AS first_five_friends

Keep only rows that meet an aggregate​

MATCH (r:Review)
WITH r.product AS product, avg(r.stars) AS mean
WHERE mean >= 4.5
RETURN product, mean
ORDER BY mean DESC

Using both RETURN DISTINCT and ORDER BY​

DISTINCT runs first — you can only order by projected columns.

MATCH (p:Person)
RETURN DISTINCT p.city AS city
ORDER BY city

Edge cases​

Empty aggregation input​

RETURN count(*) with zero matches still emits one row with value 0. sum returns 0 on empty input; avg, min and max return null. See Aggregation → count.

WITH without projection​

Every WITH must project at least one thing — there's no "pass everything" shorthand. WITH * works and projects every in-scope variable:

MATCH (a)-[r]->(b)
WITH *
RETURN a, r, b

Aggregation in WITH without a group key​

Aggregating with no non-aggregated column folds everything into one row:

MATCH (o:Order)
WITH sum(o.amount) AS total
RETURN total

See also​