Expressions and Functions

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

Literals

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}

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

A WHEN can hold several comma-separated values, a list or map literal, or a comparison applied to the tested value (since v26.10.1):

RETURN CASE x WHEN 1, 2 THEN 'low' WHEN > 5 THEN 'high' WHEN IS NULL THEN 'none' ELSE 'mid' END
RETURN CASE n.tags WHEN [] THEN 'empty' WHEN ['a'] THEN 'just a' ELSE 'other' END

Arithmetic Expressions

Perform arithmetic operations on numeric values.

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)

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

As of version 26.9.1, every STRING-typed argument of these functions (not just the first one) is validated: passing a non-STRING value - for example a list or a number - raises a client error instead of silently converting it to text. If you need to pass a non-string value on purpose, convert it explicitly first with toString(), e.g. split('a,1', toString(1)).

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'

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. A string is measured in Unicode characters, so an emoji counts as one (left(), right(), substring() and char_length() count the same way since v26.10.1).

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.

id() of a lightweight edge is null: a lightweight edge has no record, so there is no position to pack (since v26.11.1; earlier versions raised an error).

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. A node created with no label at all (CREATE (n {name: 'x'})) returns an empty list, exactly like Neo4j — including when the label a node carries happens to be literally V or Vertex, which are ordinary, writable labels like any other and are not treated specially.

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

CREATE (n {name: 'x'})
RETURN labels(n)  // Returns []

CREATE (n:V {name: 'y'})
RETURN labels(n)  // Returns ['V']

Before version 26.9.1, "no labels" was represented internally by the type name V (or Vertex), so a node created with no label and a node deliberately labeled :V were stored identically and both reported labels(n) = []. As of 26.9.1 this internal state uses a reserved type name instead, so V/Vertex become ordinary labels: existing vertices already stored under the old V/Vertex types will, after upgrading, report labels(n) as ['V']/['Vertex'] instead of [].

There is no automatic per-record migration, because the two cases were indistinguishable under the old storage convention — an existing V-typed vertex may be one that was genuinely created unlabelled, or one that was deliberately labeled :V. If a database’s entire V (or Vertex) type was only ever used for unlabelled nodes, its schema type can be renamed to restore the pre-26.9.1 behavior for that whole type; consult ArcadeDB support before doing this on a database where the distinction is not already known.

type()

Returns the type of a relationship. Calling it on a node is always an error.

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

As of 26.9.1, this check (and the equivalent one for labels(), size() and length()) is applied more precisely when the same variable name is reused later in the query with WITH. A query that called type() on a node and relied on a later WITH clause to mask the mistake now correctly reports the error instead of silently passing.

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 the ArcadeDB SQL functions. This provides access to 100+ additional functions. Some of them (such as sqrt(), date() and distance()) can be called by their bare name; others (such as pow(), uuid(), ifnull() and sysdate()) are not found unless you prefix them with sql., e.g. sql.pow(2, 8). When in doubt, use the sql. prefix.

Usage:

Call SQL functions directly in Cypher expressions:

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

// Math functions
RETURN sql.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 ~555811.94 (meters)

point(0, 0) creates a WGS-84 point, so distance() returns a geodesic distance in meters, not the cartesian distance of 5.0.

The Cypher point() function returns a map with the coordinates, the crs name and the srid, as Neo4j does:

Call crs srid

point({x: 1, y: 2})

cartesian

7203

point({x: 1, y: 2, z: 3})

cartesian-3D

9157

point({longitude: 1, latitude: 2})

WGS-84

4326

point({longitude: 1, latitude: 2, height: 3})

WGS-84-3D

4979

crs and srid can be given explicitly and are derived from each other. A pair that disagrees, or 2D/3D coordinates that do not match the crs/srid, raise an error.

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

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

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

=

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

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

STARTS WITH

String prefix match. Uses an ordered index on the property, except a case insensitive one

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}'
MATCH (n:Label) RETURN min(n.prop) (and max) reads one end of the index when prop has an ordered index on Label and the query has no other clause. A WHERE that is only a range of the same property (WHERE n.prop > 500000, <, >=, ⇐) reads the first matching entry of the range instead of the whole range. SQL does the same for SELECT min(prop) FROM Type WHERE prop > 500000. A range bound that is null matches nothing, with or without an index.

List Operators

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])

Null Operators

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

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