Cypher Functions¶

QilbeeDB supports a wide range of built-in functions for data manipulation and analysis.

String Functions¶

toLower / toUpper¶

RETURN toLower("HELLO") AS lower  // "hello"
RETURN toUpper("hello") AS upper  // "HELLO"

trim / lTrim / rTrim¶

RETURN trim("  hello  ") AS trimmed      // "hello"
RETURN lTrim("  hello  ") AS leftTrim    // "hello  "
RETURN rTrim("  hello  ") AS rightTrim   // "  hello"

substring¶

RETURN substring("hello world", 0, 5) AS sub  // "hello"
RETURN substring("hello world", 6) AS sub     // "world"

replace¶

RETURN replace("hello world", "world", "there") AS result
// "hello there"

split¶

RETURN split("a,b,c", ",") AS parts  // ["a", "b", "c"]

size (String Length)¶

RETURN size("hello") AS length  // 5

String Concatenation¶

RETURN "hello" + " " + "world" AS greeting  // "hello world"

Numeric Functions¶

abs¶

RETURN abs(-5) AS absolute  // 5

round / floor / ceil¶

RETURN round(3.7) AS rounded   // 4
RETURN floor(3.7) AS floored   // 3
RETURN ceil(3.2) AS ceiled     // 4

sqrt / power¶

RETURN sqrt(16) AS root        // 4
RETURN power(2, 8) AS result   // 256

sign¶

RETURN sign(-5) AS s   // -1
RETURN sign(0) AS s    // 0
RETURN sign(5) AS s    // 1

rand¶

RETURN rand() AS random  // Random float between 0 and 1

Aggregation Functions¶

count¶

MATCH (u:User)
RETURN count(u) AS totalUsers

// Count distinct
MATCH (u:User)
RETURN count(DISTINCT u.city) AS cities

sum¶

MATCH (p:Product)
RETURN sum(p.price) AS totalValue

avg¶

MATCH (u:User)
RETURN avg(u.age) AS averageAge

min / max¶

MATCH (u:User)
RETURN min(u.age) AS youngest, max(u.age) AS oldest

collect¶

MATCH (u:User)-[:KNOWS]->(f:User)
RETURN u.name, collect(f.name) AS friends

percentile¶

MATCH (u:User)
RETURN percentileDisc(u.age, 0.5) AS medianAge
RETURN percentileCont(u.age, 0.95) AS p95Age

List Functions¶

size (List Length)¶

RETURN size([1, 2, 3]) AS length  // 3

head / last¶

RETURN head([1, 2, 3]) AS first  // 1
RETURN last([1, 2, 3]) AS last   // 3

tail¶

RETURN tail([1, 2, 3]) AS rest  // [2, 3]

range¶

RETURN range(1, 10) AS numbers      // [1, 2, 3, ..., 10]
RETURN range(1, 10, 2) AS evens     // [1, 3, 5, 7, 9]

reverse¶

RETURN reverse([1, 2, 3]) AS reversed  // [3, 2, 1]

List Comprehension¶

MATCH (u:User)-[:KNOWS]->(f)
RETURN [friend IN collect(f) | friend.name] AS friendNames

Date/Time Functions¶

datetime¶

RETURN datetime() AS now
RETURN datetime("2024-01-15") AS date

date¶

RETURN date() AS today
RETURN date("2024-01-15") AS specificDate

time¶

RETURN time() AS currentTime
RETURN time("14:30:00") AS specificTime

duration¶

RETURN duration("P1Y2M3D") AS period      // 1 year, 2 months, 3 days
RETURN duration("PT1H30M") AS timespan    // 1 hour, 30 minutes

Date Arithmetic¶

RETURN datetime() + duration("P7D") AS nextWeek
RETURN datetime() - duration("P1M") AS lastMonth

Extract Components¶

WITH datetime() AS now
RETURN now.year, now.month, now.day,
       now.hour, now.minute, now.second

Type Conversion Functions¶

toInteger¶

RETURN toInteger("42") AS num      // 42
RETURN toInteger(42.7) AS num      // 42

toFloat¶

RETURN toFloat("3.14") AS num      // 3.14
RETURN toFloat(42) AS num          // 42.0

toString¶

RETURN toString(42) AS str         // "42"
RETURN toString(3.14) AS str       // "3.14"

toBoolean¶

RETURN toBoolean("true") AS bool   // true
RETURN toBoolean("false") AS bool  // false

Predicate Functions¶

exists¶

MATCH (u:User)
WHERE exists(u.email)
RETURN u

coalesce¶

MATCH (u:User)
RETURN coalesce(u.nickname, u.name, "Anonymous") AS displayName

CASE¶

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

Path Functions¶

length (Path Length)¶

MATCH path = (a:User)-[:KNOWS*]-(b:User)
RETURN length(path) AS hops

nodes¶

MATCH path = (a)-[:KNOWS*1..3]-(b)
RETURN nodes(path) AS pathNodes

relationships¶

MATCH path = (a)-[:KNOWS*1..3]-(b)
RETURN relationships(path) AS pathRels

shortestPath¶

MATCH path = shortestPath((a:User {name: 'Alice'})-[:KNOWS*]-(b:User {name: 'Bob'}))
RETURN length(path) AS distance

Node/Relationship Functions¶

id¶

MATCH (u:User)
RETURN id(u) AS nodeId

labels¶

MATCH (n)
RETURN labels(n) AS nodeLabels

type (Relationship Type)¶

MATCH ()-[r]->()
RETURN type(r) AS relType

properties¶

MATCH (u:User {name: 'Alice'})
RETURN properties(u) AS allProps

keys¶

MATCH (u:User {name: 'Alice'})
RETURN keys(u) AS propertyNames

Spatial Functions (if supported)¶

point¶

RETURN point({latitude: 37.7749, longitude: -122.4194}) AS location

distance¶

WITH point({latitude: 37.7749, longitude: -122.4194}) AS sf,
     point({latitude: 40.7128, longitude: -74.0060}) AS nyc
RETURN distance(sf, nyc) AS distanceMeters

User-Defined Functions¶

randomUUID¶

CREATE (u:User {id: randomUUID(), name: 'Alice'})
RETURN u

Common Function Combinations¶

Full Name¶

MATCH (u:User)
RETURN u.firstName + ' ' + u.lastName AS fullName

Email Normalization¶

MATCH (u:User)
RETURN toLower(trim(u.email)) AS normalizedEmail

Age from Birthday¶

MATCH (u:User)
RETURN u.name,
  duration.between(u.birthDate, date()).years AS age

Default Values¶

MATCH (u:User)
RETURN u.name,
  coalesce(u.phone, "N/A") AS phone

Time Ago¶

MATCH (p:Post)
WITH p, duration.between(p.created, datetime()) AS elapsed
RETURN p.title,
  CASE
    WHEN elapsed.days = 0 THEN "Today"
    WHEN elapsed.days = 1 THEN "Yesterday"
    WHEN elapsed.days < 7 THEN elapsed.days + " days ago"
    WHEN elapsed.days < 30 THEN (elapsed.days / 7) + " weeks ago"
    ELSE (elapsed.days / 30) + " months ago"
  END AS timeAgo

Search Score¶

MATCH (p:Post)
WHERE toLower(p.title) CONTAINS toLower($query)
   OR toLower(p.content) CONTAINS toLower($query)
RETURN p,
  (CASE WHEN toLower(p.title) CONTAINS toLower($query) THEN 10 ELSE 0 END +
   CASE WHEN toLower(p.content) CONTAINS toLower($query) THEN 5 ELSE 0 END +
   p.likes * 0.1) AS score
ORDER BY score DESC

Performance Tips¶

Use Functions on Indexed Properties Carefully

// Bad: Function prevents index use
WHERE toLower(u.email) = 'alice@example.com'

// Good: Direct comparison
WHERE u.email = 'alice@example.com'

Aggregate Early

// Good: Aggregate before expensive operations
MATCH (u:User)-[:POSTED]->(p:Post)
WITH u, count(p) AS posts
WHERE posts > 10
RETURN u

Use coalesce for Defaults

SET u.credits = coalesce(u.credits, 0) + 10

Next Steps¶

Learn MATCH clause
Explore WHERE filtering
Use RETURN clause
Read Cypher Introduction