Expressions and Functions

This section describes the expressions and functions available in ArcadeDB’s OpenCypher implementation.

Literals

Type Syntax Example

Type	Syntax	Example
Integer	`123`, `-456`	`RETURN 42`
Float	`3.14`, `-2.5`	`RETURN 3.14159`
String	`'text'`, `"text"`	`RETURN 'Hello'`
Boolean	`true`, `false`	`RETURN true`
Null	`null`	`RETURN null`
List	`[1, 2, 3]`	`RETURN [1, 'a', true]`
Map	`{key: value}`	`RETURN {name: 'Alice', age: 30}`

Integer

123, -456

RETURN 42

Float

3.14, -2.5

RETURN 3.14159

String

'text', "text"

RETURN 'Hello'

Boolean

true, false

RETURN true

Null

null

RETURN null

List

[1, 2, 3]

RETURN [1, 'a', true]

Map

{key: value}

RETURN {name: 'Alice', age: 30}

Property Access

Access properties using dot notation:

MATCH (p:Person)
RETURN p.name, p.age, p.address.city

CASE Expression

Conditional logic in expressions.

Simple CASE:

MATCH (p:Person)
RETURN p.name,
       CASE
         WHEN p.age < 18 THEN 'minor'
         WHEN p.age < 65 THEN 'adult'
         ELSE 'senior'
       END AS ageGroup

Extended CASE:

MATCH (p:Person)
RETURN p.name,
       CASE p.status
         WHEN 'A' THEN 'Active'
         WHEN 'I' THEN 'Inactive'
         ELSE 'Unknown'
       END AS statusName

Arithmetic Expressions

Perform arithmetic operations on numeric values.

Operator Description Example

Operator	Description	Example
`+`	Addition	`RETURN 5 + 3` (returns 8)
`-`	Subtraction	`RETURN 10 - 4` (returns 6)
`*`	Multiplication	`RETURN 6 * 7` (returns 42)
`/`	Division	`RETURN 15 / 3` (returns 5.0)
`%`	Modulo	`RETURN 17 % 5` (returns 2)
`^`	Power	`RETURN 2 ^ 8` (returns 256.0)

+

Addition

RETURN 5 + 3 (returns 8)

-

Subtraction

RETURN 10 - 4 (returns 6)

*

Multiplication

RETURN 6 * 7 (returns 42)

/

Division

RETURN 15 / 3 (returns 5.0)

%

Modulo

RETURN 17 % 5 (returns 2)

^

Power

RETURN 2 ^ 8 (returns 256.0)

Examples with properties:

// Double a person's age
MATCH (p:Person)
RETURN p.name, p.age * 2 AS doubleAge

// Calculate monthly salary
MATCH (p:Person)
RETURN p.name, p.salary / 12 AS monthlySalary

// Complex expression
MATCH (p:Person)
RETURN p.name, (p.age * 2) + 10 AS result

The + operator also works for string concatenation: RETURN 'Hello' + ' ' + 'World' returns 'Hello World'.

Map Literals

Create inline map (object) structures.

Syntax: {key1: value1, key2: value2, …}

// Simple map literal
RETURN {name: 'Alice', age: 30}

// Empty map
RETURN {}

// Map with expressions
MATCH (p:Person)
WHERE p.name = 'Alice'
RETURN {
  personName: p.name,
  doubled: p.age * 2,
  nextYearAge: p.age + 1
} AS info

List Comprehensions

Transform and filter lists using comprehension syntax.

Syntax: [variable IN list WHERE condition | expression]

variable - iteration variable
list - source list or range
WHERE condition - optional filter (elements that don’t match are excluded)
| expression - optional mapping expression (transforms each element)

Basic transformation:

// Double each number
RETURN [x IN [1, 2, 3] | x * 2]
// Returns [2, 4, 6]

// Square numbers using range
RETURN [x IN range(1, 5) | x * x]
// Returns [1, 4, 9, 16, 25]

With filter:

// Filter and transform
RETURN [x IN [1, 2, 3, 4, 5] WHERE x > 2 | x * 10]
// Returns [30, 40, 50]

// Filter only (no transformation)
RETURN [x IN [1, 2, 3, 4, 5] WHERE x > 3]
// Returns [4, 5]

Map Projections

Extract specific properties from nodes or maps into a new map structure.

Syntax: variable{.property1, .property2, key: expression, .*}

.property - include this property from the source
key: expression - add a computed value with the given key
.* - include all properties from the source

Property selection:

// Select specific properties
MATCH (p:Person)
WHERE p.name = 'Alice'
RETURN p{.name, .age} AS person
// Returns {name: 'Alice', age: 30}

With computed values:

// Add computed properties
MATCH (p:Person)
WHERE p.name = 'Alice'
RETURN p{.name, doubleAge: p.age * 2} AS person
// Returns {name: 'Alice', doubleAge: 60}

All properties:

// Include all properties
MATCH (p:Person)
WHERE p.name = 'Alice'
RETURN p{.*} AS person
// Returns {name: 'Alice', age: 30, salary: 50000}

Aggregation Functions

Aggregation functions process multiple values and return a single result.

count()

Returns the number of values or rows.

// Count nodes
MATCH (p:Person)
RETURN count(p)

// Count all rows
MATCH (p:Person)
RETURN count(*)

// Count non-null values
MATCH (p:Person)
RETURN count(p.email)

sum()

Returns the sum of numeric values.

MATCH (p:Person)
RETURN sum(p.salary)

avg()

Returns the average of numeric values.

MATCH (p:Person)
RETURN avg(p.age)

min()

Returns the minimum value.

MATCH (p:Person)
RETURN min(p.age) AS youngest

max()

Returns the maximum value.

MATCH (p:Person)
RETURN max(p.age) AS oldest

collect()

Collects values into a list.

// Collect all names
MATCH (p:Person)
RETURN collect(p.name) AS allNames

// Group and collect
MATCH (p:Person)-[:LIVES_IN]->(c:City)
RETURN c.name, collect(p.name) AS residents

String Functions

toUpper()

Converts string to uppercase.

RETURN toUpper('hello')  // Returns 'HELLO'

toLower()

Converts string to lowercase.

RETURN toLower('HELLO')  // Returns 'hello'

trim()

Removes leading and trailing whitespace.

RETURN trim('  hello  ')  // Returns 'hello'

substring()

Extracts a substring.

Syntax: substring(string, start [, length])

RETURN substring('hello world', 0, 5)  // Returns 'hello'
RETURN substring('hello world', 6)      // Returns 'world'

replace()

Replaces occurrences of a substring.

Syntax: replace(string, search, replacement)

RETURN replace('hello', 'l', 'L')  // Returns 'heLLo'

split()

Splits a string into a list.

Syntax: split(string, delimiter)

RETURN split('a,b,c', ',')  // Returns ['a', 'b', 'c']

left()

Returns the leftmost characters.

Syntax: left(string, length)

RETURN left('hello', 3)  // Returns 'hel'

right()

Returns the rightmost characters.

Syntax: right(string, length)

RETURN right('hello', 3)  // Returns 'llo'

reverse()

Reverses a string.

RETURN reverse('hello')  // Returns 'olleh'

toString()

Converts a value to string.

RETURN toString(123)      // Returns '123'
RETURN toString(3.14)     // Returns '3.14'
RETURN toString(true)     // Returns 'true'

Math Functions

abs()

Returns absolute value.

RETURN abs(-42)  // Returns 42

sqrt()

Returns square root.

RETURN sqrt(16)  // Returns 4.0

ceil()

Rounds up to nearest integer.

RETURN ceil(2.3)  // Returns 3.0

floor()

Rounds down to nearest integer.

RETURN floor(2.7)  // Returns 2.0

round()

Rounds to nearest integer.

RETURN round(2.5)  // Returns 3.0
RETURN round(2.4)  // Returns 2.0

rand()

Returns random float between 0 and 1.

RETURN rand()  // Returns e.g. 0.7324...

List Functions

size()

Returns the size of a list or string.

RETURN size([1, 2, 3])      // Returns 3
RETURN size('hello')        // Returns 5

head()

Returns the first element of a list.

RETURN head([1, 2, 3])  // Returns 1

tail()

Returns all but the first element.

RETURN tail([1, 2, 3])  // Returns [2, 3]

last()

Returns the last element of a list.

RETURN last([1, 2, 3])  // Returns 3

range()

Creates a list of integers.

Syntax: range(start, end [, step])

RETURN range(1, 5)     // Returns [1, 2, 3, 4, 5]
RETURN range(0, 10, 2) // Returns [0, 2, 4, 6, 8, 10]

reverse() (list)

Reverses a list.

RETURN reverse([1, 2, 3])  // Returns [3, 2, 1]

Node and Relationship Functions

id()

Returns the internal ID of a node or relationship as a numeric (Long) value, matching the Neo4j semantics where id() returns an INTEGER. The two components of an ArcadeDB RID (bucketId, position) are packed into a single non-negative number so it can be used in numeric predicates such as WHERE id(n) >= 0.

MATCH (p:Person)
RETURN id(p)  // Returns e.g. 786432

The packing uses 63 of the 64 bits of the Long (the sign bit is kept clear so the value is never negative): the upper bits hold the bucketId and the lower bits the record position. The split is controlled by the global setting arcadedb.opencypher.idBucketBits (see Settings). The default of 16 reserves 16 bits for the bucketId (up to 65536 buckets) and 47 bits for the position (~1.4e14 records per bucket), which covers the vast majority of use cases:

Increase it (e.g. 20) for databases that allocate a very large number of buckets across many types.
Decrease it for buckets that hold an extremely high number of records.

# Reserve 20 bits for the bucketId (up to ~1M buckets)
java ... -Darcadedb.opencypher.idBucketBits=20

To put the position field in perspective with the default 16: it reserves 47 bits, enough to address 2^47 ≈ 1.4e14 positions per bucket. A single bucket is internally limited to ~2.1 billion pages (the page index is a 32-bit integer), so with a standard 64KB page holding on average ~500 records that is about 2.1e9 × 500 ≈ 1 trillion records (~137 TB) in one bucket - and the 47-bit position field covers that roughly 30x over. In other words, the numeric id() never runs out of range before the bucket is physically full, so for almost every database the only setting worth tuning is the bucketId width (the bucket count), not the position width.

id() fails with an error if a RID cannot be packed with the configured split (for example a bucketId beyond the reserved bits), so an out-of-range value is reported instead of silently colliding with another id. Because the value is computed from the split, changing arcadedb.opencypher.idBucketBits alters the numeric output: encode and decode must use the same setting. For a stable, opaque identifier that does not depend on this setting, use elementId() instead.

From SQL, the numeric id can be resolved back to a record in O(1) with the cypherRID() function, e.g. SELECT FROM cypherRID(:id) (the inverse of id() / .asCypherRID()).

labels()

Returns the labels of a node as a list.

MATCH (p:Person)
RETURN labels(p)  // Returns ['Person']

type()

Returns the type of a relationship.

MATCH (a)-[r]->(b)
RETURN type(r)  // Returns e.g. 'KNOWS'

keys()

Returns the property keys of a node or relationship.

MATCH (p:Person {name: 'Alice', age: 30})
RETURN keys(p)  // Returns ['name', 'age']

properties()

Returns all properties as a map.

MATCH (p:Person {name: 'Alice', age: 30})
RETURN properties(p)  // Returns {name: 'Alice', age: 30}

startNode()

Returns the start node of a relationship.

MATCH (a)-[r:KNOWS]->(b)
RETURN startNode(r)  // Returns node a

endNode()

Returns the end node of a relationship.

MATCH (a)-[r:KNOWS]->(b)
RETURN endNode(r)  // Returns node b

Path Functions

length()

Returns the length of a path (number of relationships).

MATCH path = (a:Person)-[:KNOWS*]->(b:Person)
RETURN length(path)

nodes()

Returns the nodes in a path as a list.

MATCH path = (a:Person)-[:KNOWS*]->(b:Person)
RETURN nodes(path)

relationships()

Returns the relationships in a path as a list.

MATCH path = (a:Person)-[:KNOWS*]->(b:Person)
RETURN relationships(path)

Type Conversion Functions

toInteger()

Converts to integer.

RETURN toInteger('42')      // Returns 42
RETURN toInteger(3.7)       // Returns 3

toFloat()

Converts to float.

RETURN toFloat('3.14')      // Returns 3.14
RETURN toFloat(42)          // Returns 42.0

toBoolean()

Converts to boolean.

RETURN toBoolean('true')    // Returns true
RETURN toBoolean(1)         // Returns true
RETURN toBoolean(0)         // Returns false

SQL Function Bridge

ArcadeDB’s OpenCypher implementation includes a bridge to all ArcadeDB SQL functions. This provides access to 100+ additional functions.

Usage:

Call SQL functions directly in Cypher expressions:

// Date functions
MATCH (p:Person)
RETURN p.name, date() AS today

// Math functions
RETURN pow(2, 8) AS result  // Returns 256

// String functions
MATCH (p:Person)
RETURN normalize(p.name) AS normalizedName

// Geometric functions
RETURN distance(point(0, 0), point(3, 4)) AS dist  // Returns 5.0

For a complete list of available SQL functions, see the SQL Functions reference.

Extended Functions (APOC Compatible)

ArcadeDB provides a comprehensive set of extended functions that are fully compatible with Neo4j’s APOC library. These functions work in both Cypher and SQL queries.

APOC Prefix Compatibility

ArcadeDB automatically supports the apoc. prefix. Your existing APOC queries work without modification:

// Neo4j/APOC style (works in ArcadeDB)
RETURN apoc.text.join(["a", "b", "c"], ",") AS result

// ArcadeDB native style (also works)
RETURN text.join(["a", "b", "c"], ",") AS result

Available Function Namespaces

Namespace Description

Namespace	Description
`agg.*`	Aggregation functions (median, statistics, etc.)
`convert.*`	Type conversion (toJson, fromJsonMap, etc.)
`create.*`	Creation functions (uuid, virtual nodes/relationships)
`date.*`	Date/time operations (format, parse, add, etc.)
`map.*`	Map operations (merge, flatten, groupBy, etc.)
`math.*`	Mathematical functions (sigmoid, tanh, etc.)
`node.*`	Node operations (degree, labels, etc.)
`path.*`	Path operations (create, combine, slice, expand)
`rel.*`	Relationship operations (type, startNode, etc.)
`text.*`	String manipulation (join, split, replace, camelCase, etc.)
`util.*`	Utility functions (md5, sha256, compress, etc.)
`vector.*`	Vector/embedding operations (similarity, distance, quantization, neighbors)

agg.*

Aggregation functions (median, statistics, etc.)

convert.*

Type conversion (toJson, fromJsonMap, etc.)

create.*

Creation functions (uuid, virtual nodes/relationships)

date.*

Date/time operations (format, parse, add, etc.)

map.*

Map operations (merge, flatten, groupBy, etc.)

math.*

Mathematical functions (sigmoid, tanh, etc.)

node.*

Node operations (degree, labels, etc.)

path.*

Path operations (create, combine, slice, expand)

rel.*

Relationship operations (type, startNode, etc.)

text.*

String manipulation (join, split, replace, camelCase, etc.)

util.*

Utility functions (md5, sha256, compress, etc.)

vector.*

Vector/embedding operations (similarity, distance, quantization, neighbors)

Available Procedures

Procedure Description

Procedure	Description
`merge.relationship`	Create or match relationships
`merge.node`	Create or match nodes
`algo.dijkstra`	Weighted shortest path
`algo.astar`	A* pathfinding with heuristics
`algo.allsimplepaths`	Find all simple paths
`path.expand`	Expand paths from a node
`path.subgraphnodes`	Get all reachable nodes
`meta.graph`	Schema as virtual graph
`meta.schema`	Detailed schema information
`meta.stats`	Database statistics

merge.relationship

Create or match relationships

merge.node

Create or match nodes

algo.dijkstra

Weighted shortest path

algo.astar

A* pathfinding with heuristics

algo.allsimplepaths

Find all simple paths

path.expand

Expand paths from a node

path.subgraphnodes

Get all reachable nodes

meta.graph

Schema as virtual graph

meta.schema

Detailed schema information

meta.stats

Database statistics

Examples:

// Text functions
RETURN text.camelCase("hello world") AS result
// Returns: "helloWorld"

// Map functions
RETURN map.merge({a: 1}, {b: 2, a: 3}) AS result
// Returns: {a: 3, b: 2}

// JSON conversion
RETURN convert.toJson({name: "Alice", age: 30}) AS json
// Returns: '{"name":"Alice","age":30}'

// Hashing
RETURN util.sha256("password") AS hash

// Statistics
RETURN agg.statistics([1, 2, 3, 4, 5]) AS stats
// Returns: {count: 5, min: 1.0, max: 5.0, sum: 15.0, mean: 3.0, ...}

// Graph algorithms
MATCH (a:City {name: 'NYC'}), (b:City {name: 'LA'})
CALL algo.dijkstra(a, b, 'ROAD', 'distance') YIELD path, weight
RETURN path, weight

For the complete function reference, see reference/extended-functions.adoc#extended-functions.

Operators

Comparison Operators

Operator Description Example

Operator	Description	Example
`=`	Equal	`WHERE n.age = 30`
`<>` or `!=`	Not equal	`WHERE n.age <> 30`
`<`	Less than	`WHERE n.age < 30`
`>`	Greater than	`WHERE n.age > 30`
`⇐`	Less than or equal	`WHERE n.age ⇐ 30`
`>=`	Greater than or equal	`WHERE n.age >= 30`

=

Equal

WHERE n.age = 30

<> or !=

Not equal

WHERE n.age <> 30

<

Less than

WHERE n.age < 30

>

Greater than

WHERE n.age > 30

⇐

Less than or equal

WHERE n.age ⇐ 30

>=

Greater than or equal

WHERE n.age >= 30

Logical Operators

Operator Description Example

Operator	Description	Example
`AND`	Logical AND	`WHERE n.age > 20 AND n.age < 40`
`OR`	Logical OR	`WHERE n.city = 'NYC' OR n.city = 'LA'`
`NOT`	Logical NOT	`WHERE NOT n.active = false`

AND

Logical AND

WHERE n.age > 20 AND n.age < 40

OR

Logical OR

WHERE n.city = 'NYC' OR n.city = 'LA'

NOT

Logical NOT

WHERE NOT n.active = false

String Operators

Operator Description Example

Operator	Description	Example
`STARTS WITH`	String prefix match	`WHERE n.name STARTS WITH 'A'`
`ENDS WITH`	String suffix match	`WHERE n.email ENDS WITH '@gmail.com'`
`CONTAINS`	Substring match	`WHERE n.name CONTAINS 'son'`
`=~`	Regular expression	`WHERE n.phone =~ '[0-9]{3}-[0-9]{4}'`

STARTS WITH

String prefix match

WHERE n.name STARTS WITH 'A'

ENDS WITH

String suffix match

WHERE n.email ENDS WITH '@gmail.com'

CONTAINS

Substring match

WHERE n.name CONTAINS 'son'

=~

Regular expression

WHERE n.phone =~ '[0-9]{3}-[0-9]{4}'

List Operators

Operator Description Example

Operator	Description	Example
`IN`	Element in list	`WHERE n.name IN ['Alice', 'Bob']`
`+`	List concatenation	`RETURN [1,2] + [3,4]` (returns [1,2,3,4])

IN

Element in list

WHERE n.name IN ['Alice', 'Bob']

+

List concatenation

RETURN [1,2] + [3,4] (returns [1,2,3,4])

Null Operators

Operator Description Example

Operator	Description	Example
`IS NULL`	Check for null	`WHERE n.email IS NULL`
`IS NOT NULL`	Check for not null	`WHERE n.email IS NOT NULL`

IS NULL

Check for null

WHERE n.email IS NULL

IS NOT NULL

Check for not null

WHERE n.email IS NOT NULL

Parameters

Parameters allow you to pass values into queries without string concatenation, providing security and performance benefits.

Syntax: $parameterName

MATCH (p:Person)
WHERE p.name = $name AND p.age >= $minAge
RETURN p

Java Example:

Map<String, Object> params = Map.of(
    "name", "Alice",
    "minAge", 25
);
ResultSet result = database.query("opencypher",
    "MATCH (p:Person) WHERE p.name = $name AND p.age >= $minAge RETURN p",
    params);

Parameters are particularly useful for:

Preventing injection attacks
Query plan caching (same plan reused for different parameter values)
Handling special characters in values