Functions

Built-In functions:

All AGE functions are defined in the ag_catalog namespace. When using built-in functions in a cypher query, the default namespace is always ag_catalog.This is independent of the configuration of search_path. These functions can be used outside a cypher command. No functions in ag_catalog that are not defined in this section can be used in a cypher command.

Predicate Functions

Predicates are boolean functions that return true or false for a given set of input. They are most commonly used to filter out subgraphs in the WHERE part of a query.

Exists(Property)

exists() returns true if the specified property exists in the node, relationship or map. This is different from the EXISTS clause.

Syntax:exists(property)

Returns:

An agtype boolean

Arguments:

Name	Description
property	A property from a vertex or edge

Query

SELECT *

FROM cypher('graph_name', $$ MATCH (n) WHERE exists(n.surname) RETURN n.first_name, n.last_name $$) as (first_name agtype, last_name agtype);

Results:

first_name	last_name
‘John	‘Smith’
‘Patty’	‘Patterson’
2 row(s) returned

Scalar Functions

id()

id() returns the id of a vertex or edge.

Syntax:id(expression)

Returns:

An agtype integer

Arguments:

Name	Description
expression	An expression that returns a vertex or edge.

Considerations:

Query:

SELECT *FROM cypher('graph_name', $$ MATCH (a) RETURN id(a) $$) as (id agtype);

Results

4 row(s) returned

start_id()

start_id() returns the id of the vertex that is the starting vertex for the edge.

Syntax: start_id(expression)

Returns:

An agtype integer

Arguments:

Name	Description
expression	An expression that evaluates to an edge.

Considerations:

Query:

SELECT *FROM cypher('graph_name', $$ MATCH ()-[e]->() RETURN start_id(e) $$) as (start_id agtype);

Results

start_id

4 row(s) returned

end_id()

end_id() returns the id of the vertex that is the ending vertex for the edge.

Syntax: end_id(expression)

Returns:

An agtype integer

Arguments:

Name	Description
expression	An expression that evaluates to an edge.

Query:

SELECT *FROM cypher('graph_name', $$ MATCH ()-[e]->() RETURN end_id(e) $$) as (end_id agtype);

Results

end_id

4 row(s) returned

type()

type() returns the string representation of the edge type

Syntax: type(edge)

Returns:

An agtype string

Arguments:

Name	Description
edge	An expression that evaluates to an edge.

Considerations:

Query:

SELECT *FROM cypher('graph_name', $$ MATCH ()-[e]->() RETURN type(e) $$) as (type agtype);

Results

type

“KNOWS”

2 row(s) returned

properties()

Returns an agtype map containing all the properties of a vertex or edge. If the argument is already a map, it is returned unchanged.

Syntax: properties(expression)

Returns:

An agtype Map.

Arguments:

Name

Description

Expression

An expression that returns a vertex, an edge, or an agtype map.

Considerations: properties(null) returns null.

Considerations:

properties(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ CREATE (p:Person {name: 'Stefan', city: 'Berlin'}) RETURN properties(p) $$) as (type agtype);

Results:

properties

{name: "Stefan"; city: "Berlin"}

1 row(s) returned

head()

returns the first element in an agtype list.

Syntax: head(list)

Returns:

The type of the value returned will be that of the first element of the list.

Arguments:

Name	Description
List	An expression that returns a list

Considerations:

head(null) returns null.
If the first element in the list is null, head(list) will return null.

Query

SELECT *FROM cypher('graph_name', $$ MATCH (a) WHERE a.name = 'Eskil' RETURN a.array, head(a.array) $$) as (lst agtype, lst_head agtype);

The first element in the list is returned.

Result:

lst	lst_head
["one","two","three"]	"one"
1 row(s) returned

last()

returns the last element in an agtype list.

Syntax:last(list)

Returns:

The type of the value returned will be that of the last element of the list.

Arguments:

Name	Description
List	An expression that returns a list

Considerations:

tail(null) returns null.
If the last element in the list is null, last(list) will return null.

Query

SELECT *FROM cypher('graph_name', $$MATCH (a)WHERE a.name = 'Eskil'RETURN a.array, last(a.array) $$) as (lst agtype, lst_tail agtype);

The first element in the list is returned.

Result:

lst	lst_tail
["one","two","three"]	"three"
1 row(s) returned

length()

length() returns the length of a path.

Syntax: length(path)

Returns:

An agtype Integer.

Arguments:

Name	Description
path	An expression that returns a path.

Considerations:length(null) returns null.

Query

SELECT *FROM cypher('graph_name', $$ MATCH p = (a)-[]->(b)-[]->(c) WHERE a.name = 'Alice' RETURN length(p) $$) as (length_of_path agtype);

The length of the path p is returned.

Results:

length_of_path

3 row(s) returned

size()

size() returns the length of a list.

Syntax:size(path)

Returns:

An agtype Integer.

Arguments:

Name	Description
list	An expression that returns a list.

Considerations:

size(null) returns null.

Query

SELECT *FROM cypher('graph_name', $$ RETURN size(['Alice', 'Bob']) $$) as (size_of_list agtype);

The length of the path p is returned.

Results:

size_of_list

1 row(s) returned

startNode()

startNode() returns the start node of an edge.

Syntax:startNode(edge)

Returns:

A vertex.

Arguments:

Name	Description
edge	An expression that returns an edge.

Considerations:

startNode(null) returns null.

Query

SELECT *FROM cypher('graph_name', $$ MATCH (x:Developer)-[r]-() RETURN startNode(r) $$) as (v agtype);

Result

Node[0]{name:"Alice",age:38,eyes:"brown"}

2 row(s) returned

endNode()

endNode() returns the start node of an edge.

Syntax: endNode(edge)

Returns:

A vertex.

Arguments:

Name	Description
edge	An expression that returns an edge.

Considerations:

endNode(null) returns null.

Query

SELECT *FROM cypher('graph_name', $$ MATCH (x:Developer)-[r]-() RETURN endNode(r) $$) as (v agtype);

Result

Node[2]{name:"Charlie",age:53,eyes:"green"}

Node[1]{name:"Bob",age:25,eyes:"blue"}

2 row(s) returned

timestamp()

timestamp() returns the difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC.

Syntax:timestamp()

Returns:

An Agtype Integer.

Considerations:

timestamp() will return the same value during one entire query, even for long-running queries.

Query

SELECT *FROM cypher('graph_name', $$ RETURN timestamp() $$) as (t agtype);

The time in milliseconds is returned.

Results:

1613496720760

1 row(s) returned

toBoolean()

toBoolean() converts a string value to a boolean value.

Syntax: toBoolean(expression)

Returns:

An agtype Boolean.

Arguments:

Name	Description
expression	An expression that returns a boolean or string value.

Considerations:

toBoolean(null) returns null.
If expression is a boolean value, it will be returned unchanged.
If the parsing fails, null will be returned.

Query

SELECT *FROM cypher('graph_name', $$ RETURN toBoolean('TRUE'), toBoolean('not a boolean') $$) as (a_bool agtype, not_a_bool agtype);

Result:

a_bool	not_a_bool
true	NULL
1 row(s) returned

toFloat()

toFloat() converts an integer or string value to a floating point number.

Syntax:toFloat(expression)

Returns:A Float.

Name	Description
expression	An expression that returns an agtype number or agtype string value.

Considerations:

toFloat(null) returns null.
If expression is a floating point number, it will be returned unchanged.
If the parsing fails, null will be returned.

Query

SELECT *FROM cypher('graph_name', $$ RETURN toFloat('11.5'), toFloat('not a number') $$) as (a_float agtype, not_a_float agtype);

Result:

a_float	not_a_float
11.5	NULL
1 row(s) returned

toInteger()

toInteger() converts a floating point or string value to an integer value.

Syntax:toInteger(expression)

Returns:

An agtype Integer.

Arguments

Name	Description
expression	An expression that returns an agtype number or agtype string value.

Considerations:

toInteger(null) returns null.
If expression is an integer value, it will be returned unchanged.
If the parsing fails, null will be returned.

Query

SELECT *FROM cypher('graph_name', $$ RETURN toInteger('42'), toInteger('not a number') $$) as (an_integer agtype, not_an_integer agtype);

Result:

an_integer	not_an_integer
42	NULL
1 row(s) returned

coalesce()

coalesce() returns the first non-null value in the given list of expressions.

Syntax:coalesce(expression [, expression]*)

Returns:

The type of the value returned will be that of the first non-null expression.

Arguments:

Name	Description
expression	An expression which may return null.

Considerations:

null will be returned if all the arguments are null.

Query

SELECT *FROM cypher('graph_name', $$MATCH (a)WHERE a.name = 'Alice'RETURN coalesce(a.hairColor, a.eyes), a.hair_color, a.eyes $$) as (color agtype, hair_color agtype, eyes agtype);

Table 105.

Result

color	hair_color	eyes
“brown”	NULL	“Brown”
1 row(s) returned

Logarithmic Functions

e()

e() returns the base of the natural logarithm, e.

Syntax:e()

Returns:

An agtype float.

Query

SELECT *FROM cypher('graph_name', $$ RETURN e() $$) as (e agtype);

Results

2.71828182845905

1 row(s) returned

sqrt()

sqrt() returns the square root of a number.

Syntax:sqrt(expression)

Returns:

An agtype float.

Query

SELECT *FROM cypher('graph_name', $$ RETURN sqrt(144) $$) as (results agtype);

Results

results

1 row(s) returned

exp()

exp() returns e^n, where e is the base of the natural logarithm, and n is the value of the argument expression.

Syntax: e(expression)

Returns:

An agtype Float.

Arguments:

Name	Description
expression	An agtype number expression

Considerations:

exp(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN e(2) $$) as (e agtype);

e to the power of 2 is returned.

Result:

7.38905609893065

1 row(s) returned

log()

log() returns the natural logarithm of a number.

Syntax:log(expression)

Returns:

An agtype Float.

Arguments:

Name	Description
expression	An agtype number expression

Considerations:

log(null) returns null.
log(0) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN log(27) $$) as (natural_logarithm agtype);

The natural logarithm of 27 is returned.

Result:

natural_logarithm

3.295836866004329

1 row(s) returned

log10()

log10() returns the common logarithm (base 10) of a number.

Syntax:log10(expression)

Returns:

An agtype Float.

Arguments:

Name	Description
expression	An agtype number expression

Considerations:

log10(null) returns null.
log10(0) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN log(27) $$) as (natural_logarithm agtype);

The common logarithm of 27 is returned.

Result:

natural_logarithm

1.4313637641589874

1 row(s) returned

Trigonometric Functions

degrees()

degrees() converts radians to degrees.

Syntax:degrees(expression)

Returns:

A Float.

Arguments:

Name	Description
expression	An agtype number expression that represents the angle in radians.

Considerations:

degrees(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN degrees(3.14159) $$) as (deg agtype);

The number of degrees in something close to pi is returned.

Results:

deg

179.99984796050427

1 row(s) returned

radians()

radians() converts radians to degrees.

Syntax:radians(expression)

Returns:

A Float.

Arguments:

Name	Description
expression	An agtype number expression that represents the angle in degrees.

Considerations:

radians(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN radians(180) $$) as (rad agtype);

The number of degrees in something close to pi is returned.

Results:

rad

3.14159265358979

1 row(s) returned

pi()

pi() returns the mathematical constant pi.

Syntax: pi()

Returns:

An agtype float.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN pi() $$) as (p agtype);

The constant pi is returned.

Result:

3.141592653589793

1 row(s) returned

sin()

sin() returns the sine of a number.

Syntax:sin(expression)

Returns:

A Float.

Arguments:

Name	Description
expression	An agtype number expression that represents the angle in radians.

Considerations:

sin(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN sin(0.5) $$) as (s agtype);

The sine of 0.5 is returned.

Results:

0.479425538604203

1 row(s) returned

cos()

cos() returns the cosine of a number.

Syntax: cos(expression)

Returns:

A Float.

Arguments:

Name	Description
expression	An agtype expression that represents the angle in radians.

Considerations:

cos(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN cosin(0.5) $$) as (c agtype);

The cosine of 0.5 is returned.

Results:

0.8775825618903728

1 row(s) returned

tan()

tan() returns the tangent of a number.

Syntax: tan(expression)

Returns:

A Float.

Arguments:

Name	Description
expression	An agtype number expression that represents the angle in radians.

Considerations:

tan(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN tan(0.5) $$) as (t agtype);

The tangent of 0.5 is returned.

Results:

0.5463024898437905

1 row(s) returned

asin()

asin() returns the arcsine of a number.

Syntax:asin(expression)

Returns:

A Float.

Arguments:

Name	Description
expression	An agtype number expression that represents the angle in radians.

Considerations:

asin(null) returns null.
If (expression < -1) or (expression > 1), then (asin(expression)) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN asin(0.5) $$) as (s agtype);

The arcsine of 0.5 is returned.

Results:

0.523598775598299

1 row(s) returned

acos()

acos() returns the arccosine of a number.

Syntax:acos(expression)

Returns:

A Float.

Arguments:

Name	Description
expression	An agtype number expression that represents the angle in radians.

Considerations:

acos(null) returns null.
If (expression < -1) or (expression > 1), then (acos(expression)) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN acos(0.5) $$) as (arc_c agtype);

The arccosine of 0.5 is returned.

Results:

arc_c

1.0471975511965979

1 row(s) returned

atan()

atan() returns the arctangent of a number.

Syntax:atan(expression)

Returns:

A Float.

Arguments:

Name	Description
expression	An agtype number expression that represents the angle in radians.

Considerations:

atan(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN atan(0.5) $$) as (arc_t agtype);

The arccosine of 0.5 is returned.

Results:

arc_t

0.463647609000806

1 row(s) returned

atan2()

atan2() returns the arctangent2 of a set of coordinates in radians.

Syntax: atan2(expression1, expression2)

Returns:

A Float.

Arguments:

Name	Description
expression1	An agtype number expression for y that represents the angle in radians.
expression2	An agtype number expression for x that represents the angle in radians.

Considerations:

atan2(null, null), atan2(null, expression2) and atan(expression1, null) all return null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN atan2(0.5, 0.6) $$) as (arc_t2 agtype);

The arctangent2 of 0.5 and 0.6 is returned.

Results:

arc_t2

0.694738276196703

1 row(s) returned

String Functions

replace()

replace() returns a string in which all occurrences of a specified string in the original string have been replaced by another (specified) string.

Syntax: replace(original, search, replace)

Returns:

An agtype String.

Arguments:

Name	Description
original	An expression that returns a string.
search	An expression that specifies the string to be replaced in original.
replace	An expression that specifies the replacementstring.

Considerations:

If any argument is null, null will be returned.
If search is not found in original, original will be returned.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN replace('hello', 'l', 'w') $$) as (str_array agtype);

Result:

new_str

“Hewwo”

1 row(s) returned

split()

split() returns a list of strings resulting from the splitting of the original string around matches of the given delimiter.

Syntax: split(original, split_delimiter)

Returns:

An agtype list of agtype strings.

Arguments:

Name	Description
original	An expression that returns a string.
split_delimiter	The string with which to split original.

Considerations:

split(null, splitDelimiter) and split(original, null) both return null

Query:

SELECT *FROM cypher('graph_name', $$ RETURN split('one,two', ',') $$) as (split_list agtype);

Result:

split_list

[“one”,”two”]

1 row(s) returned

left()

left() returns a string containing the specified number of leftmost characters of the original string.

Syntax: left(original, length)

Returns:

An agtype String.

Arguments:

Name	Description
original	An expression that returns a string.
n	An expression that returns a positive integer.

Considerations:

left(null, length) and left(null, null) both return null
left(original, null) will raise an error.
If length is not a positive integer, an error is raised.
If length exceeds the size of original, original is returned.

Query:

SELECT * FROM cypher('graph_name', $$ RETURN left('Hello', 3) $$) as (new_str agtype);

Result:

new_str

“Hel”

1 row(s) returned

right()

right() returns a string containing the specified number of rightmost characters of the original string.

Syntax: right(original, length)

Returns:

An agtype String.

Arguments:

Name	Description
original	An expression that returns a string.
n	An expression that returns a positive integer.

Considerations:

right(null, length) and right(null, null) both return null
right(original, null) will raise an error.
If length is not a positive integer, an error is raised.
If length exceeds the size of original, original is returned.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN right('hello', 3) $$) as (new_str agtype);

Result:

new_str

“llo”

1 row(s) returned

substring()

substring() returns a substring of the original string, beginning with a 0-based index start and length.

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

Returns:

An agtype String.

Arguments:

Name	Description
original	An expression that returns a string.
start	An expression denoting the position at which the substring will begin.
length	An optional expression that returns a positive integer, denoting how many characters of the original expression will be returned.

Considerations:

start uses a zero-based index.
If length is omitted, the function returns the substring starting at the position given by start and extending to the end of original.
If original is null, null is returned.
If either start or length is null or a negative integer, an error is raised.
If start is 0, the substring will start at the beginning of original.
If length is 0, the empty string will be returned.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN substring('hello', 1, 3), substring('hello', 2) $$) as (sub_str1 agtype, sub_str2 agtype);

Result:

sub_str1	sub_str2
‘ell’	‘llo’
1 row(s) returned

rTrim()

rTrim() returns the original string with trailing whitespace removed.

Syntax: rTrim(original)

Returns:

An agtype String.

Arguments:

Name	Description
original	An expression that returns a string

Considerations:

rTrim(null) returns null

Query:

SELECT *FROM cypher('graph_name', $$ RETURN rTrim(' hello ') $$) as (trimmed_str agtype);

Result:

trimmed_str

" hello"

1 row(s) returned

lTrim()

lTrim() returns the original string with trailing whitespace removed.

Syntax: lTrim(original)

Returns:

A String.

Arguments:

Name	Description
original	An expression that returns a string

Considerations:

lTrim(null) returns null

Query:

SELECT *FROM cypher('graph_name', $$ RETURN lTrim(' hello ') $$) as (trimmed_str agtype);

Result:

left_trimmed_str

“hello ”

1 row(s) returned

trim()

trim() returns the original string with leading and trailing whitespace removed.

Syntax: trim(original)

Returns:

An agtype String.

Arguments:

Name	Description
original	An expression that returns a string

Considerations:

trim(null) returns null

Query:

SELECT *FROM cypher('graph_name', $$ RETURN trim(' hello ') $$) as (trimmed_str agtype);

Result:

trimmed_str

“hello”

1 row(s) returned

toLower

toLower() returns the original string in lowercase.

Syntax: toLower(original)

Returns:

An agtype String.

Arguments:

Name	Description
original	An expression that returns a string

Considerations:

toLower(null) returns null

Query:

SELECT *FROM cypher('graph_name', $$ RETURN toLower('HELLO') $$) as (lower_str agtype);

Result:

lower_str

“hello”

1 row(s) returned

toUpper()

toUpper() returns the original string in lowercase.

Syntax: toUpper(original)

Returns:

An agtype String.

Arguments:

Name	Description
original	An expression that returns a string

Considerations:

toUpper(null) returns null

Query:

SELECT *FROM cypher('graph_name', $$ RETURN toUpper('hello') $$) as (upper_str agtype);

Result:

upper_str

“HELLO”

1 row(s) returned

reverse()

reverse() returns a string in which the order of all characters in the original string have been reversed.

Syntax: reverse(original)

Returns:

An agtype String.

Arguments:

Name	Description
original	An expression that returns a string

Considerations:

reverse(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN reverse("hello") $$) as (upper_str agtype);

Result:

upper_str

“olleh”

1 row(s) returned

toString()

toString() converts an integer, float or boolean value to a string.

Syntax:toString(expression)

Returns:

A String.

Arguments:

Name	Description
expression	An expression that returns a number, a boolean, or a string.

Considerations:

toString(null) returns null
If expression is a string, it will be returned unchanged.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN toString(11.5),toString('a string'), toString(true) $$) as (float_to_str agtype, str_to_str agtype, bool_to_string);

Result:

float_to_str	str_to_str	bool_to_str
"11.5"	"a string"	"true"
1 row(s) returned

Numeric Functions

rand()

rand() returns a random floating point number in the range from 0 (inclusive) to 1 (exclusive); i.e.[0,1). The numbers returned follow an approximate uniform distribution.

Syntax: rand()

Returns:

A Float.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN rand() $$) as (r agtype);

A random number is returned.

Result:

0.3586784748902053

1 row(s) returned

abs()

abs() returns the absolute value of the given number.

Syntax: abs(expression)

Returns:

The type of the value returned will be that of expression.

Arguments:

Name	Description
expression	An agtype number expression

Considerations:

abs(null) returns null.
If expression is negative, -(expression) (i.e. the negation of expression) is returned.

Query:

SELECT *FROM cypher('graph_name', $$ MATCH (a), (e) WHERE a.name = 'Alice' AND e.name = 'Eskil' RETURN a.age, e.age, abs(a.age - e.age) $$) as (alice_age agtype, eskil_age agtype, difference agtype);

The absolute value of the age difference is returned.

Result:

alice_age	eskil_age	difference
38	41	3
1 row(s) returned

ceil()

ceil() returns the smallest floating point number that is greater than or equal to the given number and equal to a mathematical integer.

Syntax: ceil(expression)

Returns:

A Float.

Arguments:

Name	Description
expression	An agtype number expression

Considerations:

ceil(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN ceil(0.1) $$) as (cil agtype);

The ceiling of 0.1 is returned.

Result:

cil

1 row(s) returned

floor()

floor() returns the greatest floating point number that is less than or equal to the given number and equal to a mathematical integer.

Syntax: floor(expression)

Returns:

A Float.

Arguments:

Name	Description
expression	An agtype number expression

Considerations:

floor(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN floor(0.1) $$) as (flr agtype);

The floor of 0.1 is returned.

Result:

flr

1 row(s) returned

round()

round() returns the value of the given number rounded to the nearest integer.

Syntax: round(expression)

Returns:

A Float.

Arguments:

Name	Description
expression	An agtype number expression

Considerations:

round(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN round(3.141592) $$) as (rounded_value agtype);

3.0 is returned.

Result:

rounded_value

3.0

1 row(s) returned

sign()

sign() returns the signum of the given number: 0 if the number is 0, -1 for any negative number, and 1 for any positive number

Syntax:sign(expression)

Returns:

An Integer.

Arguments:

Name	Description
expression	An agtype number expression

Considerations:

sign(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ RETURN sign(-17), sign(0.1), sign(0) $$) as (negative_sign agtype, positive_sign agtype, zero_sign agtype);

The signs of -17 and 0.1 are returned.

Result:

negative_sign	positive_sign	zero_sign
-1	1	0
1 row(s) returned

Aggregation Functions

min()

min() returns the minimum value in a set of values.

Syntax: min(expression)

Returns:

A property type, or a list, depending on the values returned by expression.

Arguments:

Name	Description
expression	An expression returning a set containing any combination of property types and lists thereof.

Considerations:

Any null values are excluded from the calculation.
In a mixed set, any string value is always considered to be lower than any numeric value, and anylist is always considered to be lower than any string.
Lists are compared in dictionary order, i.e. list elements are compared pairwise in ascending order from the start of the list to the end.
min(null) returns null.

Query

SELECT *FROM cypher('graph_name', $$ MATCH (v:Person) RETURN min(v.age) $$) as (min_age agtype);

Result:

min_age

1 row(s) returned

Using min() with Lists

Data Setup:

To clarify the following example, assume the next three commands are run first:

SELECT * FROM cypher('graph_name', $$

CREATE (:min_test {val:'d'})

$$) as (result agtype); SELECT * FROM cypher('graph_name', $$

CREATE (:min_test {val:['a', 'b', 23]})

$$) as (result agtype); SELECT * FROM cypher('graph_name', $$

CREATE (:min_test {val:['a', 'b', 23]})

$$) as (result agtype);

Query

SELECT *FROM cypher('graph_name', $$ MATCH (v:min_test) RETURN min(v.val) $$) as (min_val agtype);

The lowest of all the values in the set—in this case, the list ['a', 'c', 23]—is returned, as (i) the two lists are considered to be lower values than the string "d", and (ii) the string "a" is considered tobe a lower value than the numerical value 1.

Result:

min_age

["a", "b", 23]

1 row(s) returned

max()

max() returns the maximum value in a set of values.

Syntax: max(expression)

Returns:

A property type, or a list, depending on the values returned by expression.

Arguments:

Name	Description
expression	An expression returning a set containing any combination of property types and lists thereof.

Considerations:

Any null values are excluded from the calculation.
In a mixed set, any numeric value is always considered to be higher than any string value, and anystring value is always considered to be higher than any list.
Lists are compared in dictionary order, i.e. list elements are compared pairwise in ascending order from the start of the list to the end.
max(null) returns null.

Query:

SELECT *FROM cypher('graph_name', $$ MATCH (n:Person) RETURN max(n.age) $$) as (max_age agtype);

The highest of all the values in the property age is returned.

Result:

min_age

1 row(s) returned

stDev()

stDev() returns the standard deviation for the given value over a group. It uses a standard two-pass method, with N - 1 as the denominator, and should be used when taking a sample of the population for an unbiased estimate. When the standard variation of the entire population is being calculated, stdDevP should be used.

Syntax: stDev(expression)

Returns:

An agtype Float.

Arguments:

Name	Description
expression	An agtype number expression

Considerations:

Any null values are excluded from the calculation.
stDev(null) returns 0.

Query

SELECT *FROM cypher('graph_name', $$ MATCH (n:Person) RETURN stDev(n.age) $$) as (stdev_age agtype);

The standard deviation of the values in the property age is returned.

Result:

stdev_age

15.716233645501712

1 row(s) returned

stDevP()

stDevP() returns the standard deviation for the given value over a group. It uses a standard two-pass method, with N as the denominator, and should be used when calculating the standard deviation for an entire population. When the standard variation of only a sample of the population is being calculated, stDev should be used.

Syntax: stDevP(expression)

Returns:

An agtype Float.

Arguments:

Name	Description
expression	An agtype number expression

Considerations:

Any null values are excluded from the calculation.
stDevP(null) returns 0.

Query

SELECT *FROM cypher('graph_name', $$ MATCH (n:Person) RETURN stDevP(n.age) $$ as (stdevp_age agtype);

The population standard deviation of the values in the property age is returned.

Result:

stdevp_age

12.832251036613439

1 row(s) returned

percentileCont()

percentileCont() returns the percentile of the given value over a group, with a percentile from 0.0 to 1.0. It uses a linear interpolation method, calculating a weighted average between two values if the desired percentile lies between them. For nearest values using a rounding method, see percentileDisc.

Syntax: percentileCont(expression, percentile)

Returns:

An agtype Float.

Arguments:

Name	Description
expression	An agtype number expression
percentile	An agtype number value between 0.0 and 1.0

Considerations:

Any null values are excluded from the calculation.
percentileCont(null, percentile) returns null.

Query

SELECT *FROM cypher('graph_name', $$ MATCH (n:Person) RETURN percentileCont(n.age, 0.4) $$ as (percentile_cont_age agtype);

The 40th percentile of the values in the property age is returned, calculated with a weighted average. In this case, 0.4 is the median, or 40th percentile.

Result:

percentile_cont_age

29.0

1 row(s) returned

percentileDisc()

percentileDisc() returns the percentile of the given value over a group, with a percentile from 0.0to 1.0. It uses a rounding method and calculates the nearest value to the percentile. For interpolated values, see percentileCont.

Syntax: percentileDisc(expression, percentile)

Returns:

An agtype Float.

Arguments:

Name	Description
expression	An agtype number expression
percentile	An agtype number value between 0.0 and 1.0

Considerations:

Any null values are excluded from the calculation.
percentileDisc(null, percentile) returns null.

Query

SELECT *FROM cypher('graph_name', $$ MATCH (n:Person) RETURN percentileDisc(n.age, 0.5) $$ as (percentile_disc_age agtype);

The 50th percentile of the values in the property age is returned.

Result:

percentile_cont_age

1 row(s) returned

count()

count() returns the number of values or records, and appears in two variants:

count(*) returns the number of matching records
count(expr) returns the number of non-null values returned by an expression.

Syntax: count(expression)

Returns:

An agtype Integer.

Arguments:

Name	Description
expression	An expression

Considerations:

count(*) includes records returning null.
count(expr) ignores null values.
count(null) returns 0.
Using count(*) to return the number of nodes
count(*) can be used to return the number of nodes; for example, the number of nodes connected to some node n.

Query

SELECT *FROM cypher('graph_name', $$ MATCH (n {name: 'A'})-[]->(x) RETURN n.age, count(*) $$ as (age agtype, number_of_people agtype);

The labels and age property of the start node n and the number of nodes related to n are returned.

Result:

age	number_of_people
13	3
1 row(s) returned

Using count(*) to group and count relationship typescount(*) can be used to group relationship types and return the number.

Query

SELECT *FROM cypher('graph_name', $$ MATCH (n {name: 'A'})-[r]->() RETURN type(r), count(*) $$ as (label agtype, count agtype);

The relationship types and their group count are returned.

Result:

label	count
“KNOWS”	3
1 row(s) returned

Using count(expression) to return the number of values

Instead of simply returning the number of records with count(*), it may be more useful to return the actual number of values returned by an expression.

Query

SELECT *FROM cypher('graph_name', $$ MATCH (n {name: 'A'})-[]->(x) RETURN count(x) $$) as (count agtype);

The number of nodes connected to the start node is returned.

Result:

count

1 row(s) returned

Counting non-null values

count(expression) can be used to return the number of non-null values returned by the expression.

Query

SELECT *FROM cypher('graph_name', $$ MATCH (n:Person) RETURN count(n.age) $$) as (count agtype);

The number of :Person nodes having an age property is returned.

Result:

count

1 row(s) returned

avg()

avg() returns the average of a set of numeric values.

Syntax: avg(expression)

Returns:

An agtype Integer

Arguments:

Name	Description
expression	An expression returning a set of numeric values.

Considerations:

Any null values are excluded from the calculation.
avg(null) returns null.

Query

SELECT *FROM cypher('graph_name', $$MATCH (n:Person)RETURN avg(n.age) $$) as (avg_age agtype);

The average of all the values in the property age is returned.

Result:

avg_age

30.0

1 row(s) returned

sum

sum() returns the sum of a set of numeric values.

Syntax: sum(expression)

Returns:

An agtype Float

Arguments:

Name	Description
expression	An expression returning a set of numeric values.

Considerations:

Any null values are excluded from the calculation.
sum(null) returns 0.

Query

SELECT *FROM cypher('graph_name', $$MATCH (n:Person)RETURN sum(n.age) $$ as (total_age agtype);

The sum of all the values in the property age is returned.

Result:

total_age

1 row(s) returned

User defined functions

Users may add custom functions to the AGE. When using the Cypher function, all function calls with a Cypher query use the default namespace of: ag_catalog. However if a user want to use a function outside this namespace, they may do so by adding the namespace before the function name.

Syntax: namespace_name.function_name

Query

SELECT *FROM cypher('graph_name', $$RETURN pg_catalog.sqrt(25) $$ as (result agtype);

Result:

result

1 row(s) returned