Skip to main content

WHERE — Filtering Rows

WHERE filters rows produced by the preceding MATCH, WITH, or OPTIONAL MATCH. Any boolean expression is valid.

WHERE runs before RETURN and aggregation. For filtering after an aggregate (SQL HAVING), pipe through WITH.

Overview​

GoalOperator / Keyword
Compare scalars=, <>, <, <=, >, >=
Boolean combinatorsAND, OR, NOT, XOR
Match a prefix / suffix / substringSTARTS WITH, ENDS WITH, CONTAINS
Regex=~
Null-safe checkIS NULL, IS NOT NULL
MembershipIN [...] / IN $param
List quantifiersall, any, none, single
Pattern existenceEXISTS { (...)-[...]->(...) }
Conditional branchCASE WHEN … THEN … END

Comparison​

MATCH (n:User) WHERE n.age > 18                     RETURN n;
MATCH (n:User) WHERE n.age >= 18 AND n.age <= 65    RETURN n;
MATCH (n:User) WHERE n.name = 'alice'               RETURN n;
MATCH (n:User) WHERE n.name <> 'bob'                RETURN n

RANGE indexes can accelerate equality and range comparisons when the predicate is scoped to a matching label or relationship type:

CREATE INDEX user_age FOR (u:User) ON (u.age);
MATCH (u:User) WHERE u.age >= 18 AND u.age < 65 RETURN u

Comparison returns null (not false) when either operand is null or when the types mismatch — see Scalars → Null and Limitations.

No BETWEEN keyword — use explicit >= / <= bounds:

MATCH (p:Product) WHERE p.price >= 10 AND p.price <= 50 RETURN p

Boolean operators​

MATCH (n) WHERE n.active AND n.age >= 18 RETURN n;
MATCH (n) WHERE n.active OR n.age < 18   RETURN n;
MATCH (n) WHERE NOT n.active             RETURN n;
MATCH (n) WHERE n.active XOR n.admin     RETURN n

Three-valued logic applies — null AND false is false, null AND true is null. See the full truth table in Scalars → Null.

AND and OR evaluate left to right and stop as soon as the left side decides: after false AND or true OR the right side is not evaluated, so it cannot fail the query. That makes a guard work:

MATCH (e:Event)
WHERE type.of(e.at) = 'DATE' AND e.at >= date('2024-01-01')
RETURN e

Here an e.at that holds a DATETIME never reaches the comparison, which would otherwise be an error (see Temporal types). A null left side decides nothing, so the right side is evaluated.

Precedence​

NOT binds tightest, then AND, then XOR, then OR. Parenthesise freely when in doubt:

// These are equivalent
MATCH (n) WHERE n.a OR n.b AND n.c       RETURN n;
MATCH (n) WHERE n.a OR (n.b AND n.c)     RETURN n

String matching​

All string operators are case-sensitive. For case-insensitive matching, normalise with string.lower or string.upper on both sides.

MATCH (n) WHERE n.name STARTS WITH 'a'   RETURN n;
MATCH (n) WHERE n.name ENDS   WITH 'z'   RETURN n;
MATCH (n) WHERE n.name CONTAINS 'al'     RETURN n

TEXT indexes can accelerate these string predicates:

CREATE TEXT INDEX user_name FOR (u:User) ON (u.name);
MATCH (u:User) WHERE u.name STARTS WITH 'Al' RETURN u
MATCH (u:User)
WHERE string.lower(u.name) STARTS WITH string.lower($query)
RETURN u

Regex​

MATCH (u:User) WHERE u.name  =~ 'A.*e'           RETURN u;
MATCH (u:User) WHERE u.email =~ '.*@loradb\\.com' RETURN u

Uses the Rust regex crate — standard RE2-style syntax, no backreferences. Anchors are implicit: =~ 'foo' matches only the full string "foo", not any string containing foo. Use .* to allow prefixes/suffixes, or CONTAINS 'foo' for plain substring.

Null checks​

Most expressions involving null propagate to null, not to false. Use IS NULL / IS NOT NULL, not = null.

MATCH (n) WHERE n.optional IS NULL     RETURN n;
MATCH (n) WHERE n.optional IS NOT NULL RETURN n

;// Wrong — always yields zero rows
MATCH (n) WHERE n.optional = null      RETURN n

Common guard: require a property to exist and be non-empty:

MATCH (u:User)
WHERE u.email IS NOT NULL AND string.length(u.email) > 0
RETURN u

IN​

Membership check against a list literal or parameter.

MATCH (n)      WHERE n.age IN [18, 21, 25]         RETURN n;
MATCH (n)      WHERE NOT n.name IN ['Alice', 'Bob'] RETURN n;
MATCH (u:User) WHERE u.id IN $ids                   RETURN u

$ids binds to a list in the host language ([1, 2, 3] in JS/Python, Vec<LoraValue> in Rust).

IN with DISTINCT​

MATCH (u:User)-[:OWNS]->(p:Project)
WHERE p.tag IN $tags
RETURN DISTINCT u

IN over an empty list​

x IN [] is always false. Empty-list parameters drop every row — validate on the host side if that's a likely accident.

Arithmetic in WHERE​

Any expression that produces a boolean is allowed.

MATCH (n) WHERE n.age + 5 > 30                 RETURN n;
MATCH (n) WHERE n.price * n.quantity > 1000    RETURN n;
MATCH (n) WHERE (n.end - n.start).seconds > 60 RETURN n

Cross-variable comparison​

Predicates can reference multiple bindings from the MATCH:

MATCH (a:User)-[:FOLLOWS]->(b:User)
WHERE a.age > b.age
RETURN a.name AS older, b.name AS younger
MATCH (a:User)-[:FOLLOWS]->(b)
WHERE a.country = b.country
RETURN a, b

Pattern existence​

Use EXISTS { pattern } to filter rows by whether a pattern matches — without adding extra rows to the output.

MATCH (u:User)
WHERE EXISTS { (u)-[:FOLLOWS]->() }
RETURN u
// Users who have never posted
MATCH (u:User)
WHERE NOT EXISTS { (u)-[:WROTE]->(:Post) }
RETURN u.name

This is the anti-join pattern — cheaper than OPTIONAL MATCH … WHERE other IS NULL when you don't need the optional result.

When the subquery's start node is not bound by the outer row, give it an inline property map, for example EXISTS { MATCH (v:Person {subject: $s}) WHERE v.verified }. The same applies to COUNT { }. LoraDB seeks that property through an index, as MATCH does, instead of scanning the label once per outer row. See Performance → Query shape notes.

List predicates​

Ask a question about the elements of a list. Covered fully in List Functions → Predicates.

MATCH (n) WHERE all(x IN n.scores WHERE x > 0)      RETURN n;
MATCH (n) WHERE any(x IN n.tags   WHERE x = 'VIP')  RETURN n;
MATCH (n) WHERE none(x IN n.scores WHERE x < 0)     RETURN n;
MATCH (n) WHERE single(x IN n.scores WHERE x = 100) RETURN n

CASE in predicates​

CASE is an expression, so it composes inside WHERE wherever you'd write a scalar. Useful when the comparison value itself depends on a per-row condition:

MATCH (p:Product)
WHERE CASE
      WHEN p.on_sale THEN p.sale_price
      ELSE                p.price
    END <= $budget
RETURN p

Equivalent with coalesce when you only need a "first non-null" fallback:

MATCH (p:Product)
WHERE coalesce(p.sale_price, p.price) <= $budget
RETURN p

See RETURN → CASE expressions for the full syntax.

Common patterns​

MATCH (u:User)
WHERE string.lower(u.name) STARTS WITH string.lower($query)
RETURN u
ORDER BY u.name
LIMIT 20

Tag filtering​

MATCH (p:Product)
WHERE any(t IN p.tags WHERE t IN $tags)
RETURN p

Date range​

MATCH (e:Event)
WHERE e.at >= '2024-01-01'::DATE AND e.at < '2025-01-01'::DATE
RETURN e
ORDER BY e.at

See Temporal Functions for cast-based temporal construction and arithmetic.

"Has at least one of each"​

MATCH (u:User)
WHERE EXISTS { (u)-[:OWNS]->(:Repo) }
AND EXISTS { (u)-[:WROTE]->(:Post) }
RETURN u

"Has none of these"​

MATCH (p:Post)
WHERE none(t IN ['spam', 'nsfw', 'flagged']
         WHERE EXISTS { (p)-[:TAGGED]->(:Tag {name: t}) })
RETURN p

Chained optional predicates​

Break a complex predicate into WITH stages for readability. Each stage only sees what it needs:

MATCH (u:User)
WITH u, coalesce(u.score, 0) AS s
WHERE s >= 50
WITH u, s, (u.last_seen >= temporal.now() - 'P30D'::DURATION) AS recent
WHERE recent
RETURN u.handle, s

Same meaning as one giant WHERE, but each stage is narrower and easier to trace.

Default a missing value​

Use coalesce to substitute a fallback:

MATCH (p:Person)
WHERE coalesce(p.score, 0) >= 50
RETURN p

Edge cases​

Aggregates in WHERE​

Aggregates are not allowed in WHERE. Attempting WHERE count(x) > 5 is a semantic error — use WITH … WHERE instead.

Missing property vs null property​

A missing property and a property set to null are indistinguishable — both return null on access. SET n.prop = null is the idiomatic way to remove a property (see SET → computed expressions).

Comparison across types​

Cross-type comparisons (e.g. Int < String) return null, not false — see Limitations. Cast explicitly with toString / toInteger / toFloat first.

null propagation in compound predicates​

// If n.a is null, the whole expression is null → row dropped
MATCH (n) WHERE n.a = 1 AND n.b > 2 RETURN n

Guard null-prone properties with IS NOT NULL or coalesce when you need to reason about three-valued logic.

See also​