Functions

SQL Functions are all the functions bundled with ArcadeDB SQL engine. Look also to SQL Methods.

Extended Functions Available

ArcadeDB provides 100+ additional functions organized by namespace (text.*, map.*, convert.*, date.*, util.*, agg.*, etc.). These functions are available in both SQL and Cypher queries. See reference/extended-functions.adoc#extended-functions for the complete reference.

These extended functions are also APOC-compatible, supporting the apoc. prefix for seamless Neo4j migration.

The functions expand() and distinct() are special functions, as those have constraints in terms of admissible use inside projections.

SQL Functions can work in 2 ways based on the fact that they can receive one or more parameters:

Aggregated mode

When only one parameter is passed, the function aggregates the result in only one record. The classic example is the sum() function:

SELECT sum(salary) FROM employee

This will always return one record: the sum of salary fields across every employee record.

Inline mode

When two or more parameters are passed:

SELECT sum(salary, extra, benefits) AS total FROM employee

This will return the sum of the field "salary", "extra" and "benefits" as "total".

In case you need to use a function inline, when you only have one parameter, then add "null" as the second parameter:

SELECT first(out('friends').name, NULL) AS firstFriend FROM Profiles

In the above example, the first() function doesn’t aggregate everything in only one record, but rather returns one record per Profile, where the firstFriend is the first item of the collection received as the parameter.

Function Reference

abs()

Returns the absolute value. It works with Integer, Long, Short, Double, Float, BigInteger, BigDecimal, and null.

Syntax: abs(<field>)

Examples

SELECT abs(score) FROM Account

SELECT abs(-2332) FROM Account

SELECT abs(999) FROM Account

astar()

The A* algorithm describes how to find the cheapest path from one node to another node in a directed weighted graph with a heuristic function.

The first parameter is source record. The second parameter is destination record. The third parameter is a name of property that represents weight and fourth represents the map of options.

If property is not defined in edge or is null, distance between vertices are 0. The resulting path is returned as list of RIDs.

Syntax: astar(<sourceVertex>, <destinationVertex>, <weightEdgeFieldName>, [<options>])

options:

{
  direction:"OUT", //the edge direction (OUT, IN, BOTH)
  edgeTypeNames:[],
  vertexAxisNames:[],
  parallel : false,
  tieBreaker:true,
  maxDepth:99999,
  dFactor:1.0,
  customHeuristicFormula:'custom_Function_Name_here'  // (MANHATTAN, MAXAXIS, DIAGONAL, EUCLIDEAN, EUCLIDEANNOSQR, CUSTOM)
}

Examples

SELECT astar($current, #8:10, 'weight') FROM Vehicle

See also: Graph Algorithms: A* for the Cypher procedure variant with geographic heuristics.

avg()

Returns the arithmetic mean.

Syntax: avg(<field|value> [,<field|value>]*)

Examples

SELECT avg(salary) FROM Account

SELECT avg(1, 2, 3)

bool_and()

Aggregates a field, an expression or value by the logical AND operator and returns true or false, null values are ignored.

Syntax: bool_and(<field|expression|value>)

Examples

Test if all salaries are greater than zero:

SELECT bool_and((salary > 0)) FROM Account

bool_or()

Aggregates a field, an expression or value by the logical OR operator and returns true or false, null values are ignored.

Syntax: bool_or(<field|expression|value>)

Examples

Test if a null value is present in the salary field:

SELECT bool_or((salary IS NULL)) FROM Account

both()

Get the adjacent outgoing and incoming vertices starting from the current record as vertex.

Syntax: both([<label1>] [,<label-n>]*)

Examples

Get all the incoming and outgoing vertices from vertex with RID #13:33:

SELECT both() FROM #13:33

Get all the incoming and outgoing "Vehicle" vertices connected by edges with label (class) "Trucks" and "Cars":

SELECT both('Trucks', 'Cars') FROM Vehicle

bothE()

Get the adjacent outgoing and incoming edges starting from the current record as vertex.

Syntax: bothE([<label1>] [,<label-n>]*)

Examples

Get both incoming and outgoing edges from all the "Vehicle" vertices:

SELECT bothE() FROM Vehicle

Get all the incoming and outgoing edges of type "Friend" from the profiles with "nickname" "Jay"

SELECT bothE('Friend') FROM Profile WHERE nickname = 'Jay'

bothV()

Get the adjacent outgoing and incoming vertices starting from the current record as edge.

Syntax: bothV()

Examples

Get both incoming and outgoing vertices from the "Friend" edges:

SELECT bothV() FROM Friend

circle()

Creates a 2D circle from two numbers specifying X- and Y-coordinate of circle’s center and a number describing the circle’s radius.

Syntax: circle(<center-x>, <center-y>, <radius>)

Examples

SELECT circle(10, 10, 10) AS circle

coalesce()

Returns the first field/value argument not being null parameter. If no field/value is not null, null is returned.

Syntax: coalesce(<field|value> [,<field-n|value-n>]*)

Examples

SELECT coalesce(amount, amount2, amount3) FROM Account

concat()

Aggregates field (or string) by implicitly casting to string and concatenate. Optionally, a second field or string can be passed and is record-wise appended.

Syntax: concat(<field|string> [,<field|string>]*)

Examples

SELECT concat(name) FROM names

count()

Counts the records that match the query condition. If * is used as field, then all record will be counted, otherwise only records with field content that is not null.

Syntax: count(<field>)

Examples

SELECT count(*) FROM Account

date()

Returns a date as DATETIME from a string. <date-as-string> is the date in string format, and <format> is the date format of <date-as-string> following these rules. If no format is specified, then the default database format is used. To know more about it, look at Managing Dates.

Syntax: date(<date-as-string> [,<format>] [,<timezone>])

If no argument is given, date() returns the current time.

Examples

SELECT FROM Account WHERE created <= date('2012-07-02', 'yyyy-MM-dd')

decode()

Decodes a value into binary data (base64 and base64url are the only supported formats). The <value> must contain base64 encoded information.

Syntax: decode(<value>, <format>)

The decode function returns a binary type, which can be converted to a string via asString().

Examples

Decode a value into binary format from base64.

SELECT decode('QXJjYWRlREI=', 'base64')

SELECT decode('QXJjYWRlREI', 'base64url').asString()

difference()

Syntax: difference(<field> [,<field-n>]*)

Works as aggregate or inline. If only one argument is passed then it aggregates, otherwise it executes and returns the DIFFERENCE between the collections received as parameters.

Examples

SELECT difference(tags) FROM book

SELECT difference(inEdges, outEdges) FROM OGraphVertex

dijkstra()

Returns the cheapest path between two vertices using the Dijkstra’s algorithm where the weightEdgeFieldName parameter is the field containing the weight. Direction can be OUT (default), IN or BOTH. The resulting path is returned as list of RIDs.

Syntax: dijkstra(<sourceVertex>, <destinationVertex>, <weightEdgeFieldName> [, <direction>])

Examples

SELECT dijkstra($current, #8:10, 'weight') FROM Vehicle

See also: Graph Algorithms: Dijkstra for the Cypher procedure variant, and reference/graph-algorithms/sql-path-functions.adoc#algo-dijkstra-sql for the full SQL function reference in the appendix.

distance()

Returns the distance between two points in the globe using the Haversine algorithm. Coordinates must be in degrees.

Syntax: distance(<x-field>, <y-field>, <x-value>, <y-value>)

Examples

SELECT FROM POI WHERE distance(x, y, 52.20472, 0.14056 ) <= 30

distinct()

Retrieves only unique data entries depending on the field you have specified as argument. The main difference compared to standard SQL DISTINCT is that with ArcadeDB, a function with parenthesis and only one field can be specified.

Syntax: distinct(<field>)

The distinct() function has to be the sole projection component if used.

Examples

SELECT distinct(name) FROM City

duration()

Returns a Java duration object, which can be useful to compare periods of time.

Syntax: duration(<field|integer>, '<string>')

The admissible second argument values are given here.

Examples

SELECT duration(start, 'year') FROM Employees

encode()

Encode binary data into the specified format (base64 and base64url are the only supported formats). The <binaryfields> must be a property containing binary data.

Syntax: encode(<binaryfield|stringfield|string>, <format>)

To encode RIDs, they need to be converted to strings first via asString() otherwise the link target is encoded.

Examples

Encode binary data into base64.

SELECT encode(raw, 'base64') FROM Blob

expand()

This function has two meanings:

When used on a collection field, it unwinds the collection in the field <field> and use it as result.
When used on a link (RID) field, it expands the document pointed by that link.

Syntax: expand(<field>)

You can also use the SQL operator UNWIND in select to obtain the same result.

As expand() may change its return type based on the argument, no modifiers (method calls, suffix identifiers or array indexing) are permitted on the return value of expand().

Examples

SELECT expand(addresses) FROM Account

first()

Retrieves only the first item of multi-value fields (arrays, collections and maps). For non multi-value types just returns the value.

Syntax: first(<field>)

Examples

SELECT first(addresses) FROM Account

format() [Function]

Formats a value using the String.format() conventions. Look here for more information on the format string syntax.

Syntax: format(<format> [,<arg1> ] [,<arg-n>]*)

To escape the percent symbol (%) use %%.

Examples

SELECT format("%d - Mr. %s %s (%s)", id, name, surname, address) FROM Account

if()

Syntax: if(<expression>, <result-if-true>, <result-if-false>)

Evaluates a condition (first parameters) and returns the second parameter if the condition is true, and the third parameter otherwise.

Examples

SELECT if( (name = 'John'), "My name is John", "My name is not John") FROM Person

ifempty() [Function]

Returns the passed field/value, or optional parameter return_value_if_not_empty. If field/value is an empty string or collection, return_value_if_empty is returned.

Syntax: ifempty(<field|value>, <return_value_if_empty> [,<return_value_if_not_empty>])

Examples

SELECT ifempty(name, "No Name") FROM Account

ifnull() [Function]

Returns the passed field/value, or optional parameter return_value_if_not_null. If field/value is null, return_value_if_null is returned.

Syntax: ifnull(<field|value>, <return_value_if_null> [,<return_value_if_not_null>])

Examples

SELECT ifnull(salary, 0) FROM Account

in()

Get the adjacent incoming vertices starting from the current record as vertex.

Syntax: in([<label-1>] [,<label-n>]*)

Examples

Get all the incoming vertices from all the "Vehicle" vertices:

SELECT in() FROM Vehicle

Get all the incoming vertices connected with edges with label (class) "Trucks" and "Cars":

SELECT in('Trucks', 'Cars') FROM Vehicle

inE()

Get the adjacent incoming edges starting from the current record as Vertex.

Syntax: inE([<label1>] [,<label-n>]*)

Examples

Get all the incoming edges from all the "Vehicle" vertices:

SELECT inE() FROM Vehicle

Get all the incoming edges of type "Eats" from the "Restaurant" "Bella Napoli":

SELECT inE('Eats') FROM Restaurant WHERE name = 'Bella Napoli'

intersect()

Works as aggregate or inline. If only one argument is passed then it aggregates, otherwise executes and returns the INTERSECTION of the collections received as parameters.

Syntax: intersect(<field> [,<field-n>]*)

Examples

SELECT intersect(friends) FROM profile WHERE jobTitle = 'programmer'

SELECT intersect(inEdges, outEdges) FROM GraphVertex

inV()

Get incoming vertices starting from the current record as edge.

Syntax: inV()

Examples

Get incoming vertices from the "Friend" edges

SELECT inV() FROM Friend

last()

Retrieves only the last item of multi-value fields (arrays, collections and maps). For non multi-value types just returns the value.

Syntax: last(<field>)

Examples

SELECT last(addresses) FROM Account

list()

Creates or adds a value to a list. If <field|value> is a collection, then is merged with the list, otherwise <field|value> is added to the list.

Syntax: list(<field|value>[,]*)

Examples

SELECT name, list(roles.name) AS roles FROM User

lineString()

Creates a chain of 2D lines from a list of points. A string of lines is not necessarily closed.

Syntax: lineString([<point>*])

Examples

SELECT lineString([point(10, 10), point(20, 10), point(20, 20), point(10, 20), point(30, 30)]) AS linesString

map()

Creates a map. The arguments have to be pairs of keys and values, hence the number of arguments has to be even. The <key> argument(s) have to be strings.

Syntax: map(<key>, <value>[,]*)

Examples

SELECT map(name, roles.name) FROM User

max()

Returns the maximum value. If invoked with more than one parameter, the function doesn’t aggregate, but returns the maximum value between all the arguments.

Syntax: max(<field> [,<field-n>]*)

Examples

Returns the maximum salary of all the "Account" records:

SELECT max(salary) FROM Account.

Returns the maximum value between "salary1", "salary2" and "salary3" fields.

SELECT max(salary1, salary2, salary3) FROM Account

median()

Returns the middle value or an interpolated value that represent the middle value after the values are sorted. Nulls are ignored in the calculation.

Syntax: median(<field>)

Examples

SELECT median(salary) FROM Account

min()

Returns the minimum value. If invoked with more than one parameter, the function doesn’t aggregate but returns the minimum value between all the arguments.

Syntax: min(<field> [, <field-n>]*)

Examples

Returns the minimum salary of all the "Account" records:

SELECT min(salary) FROM Account

Returns the minimum value between "salary1", "salary2" and "salary3" fields.

SELECT min(salary1, salary2, salary3) FROM Account

mode()

Returns the values that occur with the greatest frequency. Nulls are ignored in the calculation.

Syntax: mode(<field>)

Examples

SELECT mode(salary) FROM Account

out()

Get the adjacent outgoing vertices starting from the current record as vertex.

Syntax: out([<label-1>] [,<label-n>]*)

Examples

Get all the outgoing vertices from all the "Vehicle" vertices:

SELECT out() FROM Vehicle

Get all the outgoing vertices connected with edges with label (class) "Eats" and "Favorited" from all the "Restaurant" vertices in "Rome":

SELECT out('Eats', 'Favorited') FROM Restaurant WHERE city = 'Rome'

outE()

Get the adjacent outgoing edges starting from the current record as vertex.

Syntax: outE([<label1>] [,<label-n>]*)

Examples

Get all the outgoing edges from all the "Vehicle" vertices:

SELECT outE() FROM Vehicle

Get all the outgoing edges of type "Eats" from all the "SocialNetworkProfile" vertices:

SELECT outE('Eats') FROM SocialNetworkProfile

outV()

Get outgoing vertices starting from the current record as edge.

Syntax: outV()

Examples

Get outgoing vertices from the "Friend" edges

SELECT outV() FROM Friend

percentile()

Returns the nth percentiles (the values that cut off the first n percent of the field values when it is sorted in ascending order). Nulls are ignored in the calculation. The quantiles have to be in the range 0—1

Syntax: percentile(<field> [, <quantile-n>]*)

Examples

SELECT percentile(salary, 0.95) FROM Account

SELECT percentile(salary, 0.25, 0.75) AS IQR FROM Account

point()

Creates a 2D point from two numbers specifying X- and Y-coordinate.

Syntax: point(<x>, <y>)

Examples

SELECT point(10, 20) AS point

polygon()

Creates a 2D polygon from a list of points. The lines making up a polygon are closed.

Syntax: polygon([<point>*])

Examples

SELECT polygon([point(10, 10), point(20, 10), point(20, 20), point(10, 20), point(10, 10)]) AS polygon

pow()

Returns the power of the base argument to the exponent argument. It works with Integer, Long, Short, Double, Float, BigInteger, BigDecimal, and null.

Syntax: pow(<base>, <exponent>)

Examples

SELECT pow(2, 10) AS oneKibi

randomInt()

Returns an integer drawn from a uniform pseudo-random distribution in the range from (inclusively) zero up to (exclusively) the argument max.

Syntax: randomInt(<max>)

Examples

SELECT randomInt(10) AS rand

You can use it in SQL Scripts to wait a random amount of milliseconds.

SLEEP randomInt(500);

rectangle()

Creates a 2D rectangle from four numbers specifying the left boundary X-, top boundary Y-, right boundary X- and bottom boundary Y-values.

Syntax: rectangle(<left-x>, <top-y>, <right-x>, <bottom-y>)

Examples

SELECT rectangle(10, 10, 20, 20) AS rectangle

set()

Creates or adds a value to a set. If <value> is a collection, then it is merged with the set, otherwise <field|value> is added to the set.

Syntax: set(<field|value>[,]*)

Examples

SELECT name, set(roles.name) AS roles FROM User

shortestPath()

Returns the shortest path between two vertices. Direction can be OUT (default), IN or BOTH.

Syntax: shortestPath(<sourceVertex>, <destinationVertex> [, <direction> [, <edgeClassName> [, <additionalParams>]]])

Where:

sourceVertex is the source vertex where to start the path
destinationVertex is the destination vertex where the path ends - direction, optional, is the direction of traversing. By default is "BOTH" (in+out). Supported values are "BOTH" (incoming and outgoing), "OUT" (outgoing) and "IN" (incoming)
edgeClassName, optional, is the edge class to traverse. By default all edges are crossed. This can also be a list of edge class names (eg. ["edgeType1", "edgeType2"])
additionalParams, optional, here you can pass a map of additional parameters (Map<String, Object> in Java, JSON from SQL). Currently allowed parameters are
'maxDepth': integer, maximum depth for paths (ignore path longer that 'maxDepth')

Examples

on finding the shortest path between vertices #8:32 and #8:10

SELECT shortestPath(#8:32, #8:10)

Examples

on finding the shortest path between vertices #8:32 and #8:10 only crossing outgoing edges

SELECT shortestPath(#8:32, #8:10, 'OUT')

Examples

on finding the shortest path between vertices `#8:32 and `#8:10 only crossing incoming edges of type "Friend"

SELECT shortestPath(#8:32, #8:10, 'IN', 'Friend')

Examples

on finding the shortest path between vertices `#8:32 and `#8:10 only crossing incoming edges of type "Friend" or "Colleague"

SELECT shortestPath(#8:32, #8:10, 'IN', ['Friend', 'Colleague'])

Examples

on finding the shortest path between vertices #8:32 and #8:10, long at most five hops

SELECT shortestPath(#8:32, #8:10, null, null, {"maxDepth": 5})

See also: Graph Algorithms: shortestPath() for the full function reference, and Graph Algorithms Appendix for a comprehensive reference of all graph algorithms.

sqrt()

Returns the absolute value. It works with Integer, Long, Short, Double, Float, BigInteger, BigDecimal, and null.

Integer arguments are rounded down and negative arguments result in null.

Syntax: sqrt(<field>)

Examples

SELECT sqrt(score) FROM Account

SELECT sqrt(2.0)

SELECT sqrt(63)

stddev()

Returns the standard deviation: the measure of how spread out values are. Nulls are ignored in the calculation.

Syntax: stddev(<field>)

Examples

SELECT stddev(salary) FROM Account

strcmpci()

Compares two string ignoring case. Return value is -1 if first string ignoring case is less than second, 0 if strings ignoring case are equals, 1 if second string ignoring case is less than first one. Before comparison both strings are transformed to lowercase and then compared.

Syntax: strcmpci(<first_string>, <second_string>)

Examples

Select all records where state name ignoring case is equal to "washington"

SELECT * FROM State WHERE strcmpci('washington', name) = 0

sum()

Returns the sum of all the values returned.

Syntax: sum(<field>)

Examples

SELECT sum(salary) FROM Account

symmetricDifference()

Works as aggregate or inline. If only one argument is passed then it aggregates, otherwise executes and returns the SYMMETRIC DIFFERENCE between the collections received as parameters.

Syntax: symmetricDifference(<field> [,<field-n>]*)

Examples

SELECT symmetricDifference(tags) FROM book

SELECT symmetricDifference(inEdges, outEdges) FROM GraphVertex

sysdate()

Returns the current date time as DateTime object. To know more about it, look at Managing Dates.

The default format is controlled by the setting arcadedb.dateFormat.

Syntax: sysdate()

Examples

SELECT sysdate() FROM Account

traversedEdge()

Returns the traversed edge(s) in Traverse commands.

Syntax: traversedEdge(<index> [,<items>])

Where:

<index> is the starting edge to retrieve. Value ≥ 0 means absolute position in the traversed stack. 0 means the first record. Negative values are counted from the end: -1 means last one, -2 means the edge before last one, etc.
<items>, optional, by default is 1. If >1 a collection of edges is returned

Examples

Returns last traversed edge(s) of TRAVERSE command:

SELECT traversedEdge(-1) FROM ( TRAVERSE outE(), inV() FROM #34:3232 WHILE $depth <= 10 )

Returns last 3 traversed edge(s) of TRAVERSE command:

SELECT traversedEdge(-1, 3) FROM ( TRAVERSE outE(), inV() FROM #34:3232 WHILE $depth <= 10 )

traversedElement()

Returns the traversed element(s) in Traverse commands.

Syntax: traversedElement(<index> [,<items>])

Where:

<index> is the starting item to retrieve. Value ≥ 0 means absolute position in the traversed stack. 0 means the first record. Negative values are counted from the end: -1 means last one, -2 means the record before last one, etc.
<items>, optional, by default is 1. If >1 a collection of items is returned

Examples

Returns last traversed item of TRAVERSE command:

SELECT traversedElement(-1) FROM ( TRAVERSE out() FROM #34:3232 WHILE $depth <= 10 )

Returns last 3 traversed items of TRAVERSE command:

SELECT traversedElement(-1, 3) FROM ( TRAVERSE out() FROM #34:3232 WHILE $depth <= 10 )

traversedVertex()

Returns the traversed vertex(es) in Traverse commands.

Syntax: traversedVertex(<index> [,<items>])

Where:

<index> is the starting vertex to retrieve. Value >= 0 means absolute position in the traversed stack. 0 means the first vertex. Negative values are counted from the end: -1 means last one, -2 means the vertex before last one, etc.
<items>, optional, by default is 1. If >1 a collection of vertices is returned

Examples

Returns last traversed vertex of TRAVERSE command:

SELECT traversedVertex(-1) FROM ( TRAVERSE out() FROM #34:3232 WHILE $depth <= 10 )

Returns last 3 traversed vertices of TRAVERSE command:

SELECT traversedVertex(-1, 3) FROM ( TRAVERSE out() FROM #34:3232 WHILE $depth <= 10 )

unionall()

Works as aggregate or inline. If only one argument is passed then aggregates, otherwise executes and returns a UNION of all the collections received as parameters. Also works with no collection values.

Syntax: unionall(<field> [,<field-n>]*)

Examples

SELECT unionall(friends) FROM profile

SELECT unionall(inEdges, outEdges) FROM GraphVertex WHERE label = 'test'

uuid()

Generates a UUID as a 128-bits value using the Leach-Salz variant. For more information look at: http://docs.oracle.com/javase/6/docs/api/java/util/UUID.html.

Syntax: uuid()

Examples

Insert a new record with an automatic generated id:

INSERT INTO Account SET id = UUID()

variance()

Returns the middle variance: the average of the squared differences from the mean. Nulls are ignored in the calculation.

Syntax: variance(<field>)

Examples

SELECT variance(salary) FROM Account

Vector Function Namespace

Vector functions are also available under the vector.* namespace for consistency with other extended function namespaces. The vectorXXX() function names remain available as aliases for backward compatibility.

Legacy Name Namespaced Name

vectorNeighbors()

vector.neighbors()

vectorDimension()

vector.dimension()

vectorNormalize()

vector.normalize()

vectorL2Distance()

vector.l2distance()

vectorCosineSimilarity()

vector.cosinesimilarity()

vectorDotProduct()

vector.dotproduct()

vectorMagnitude()

vector.magnitude()

vectorAdd()

vector.add()

vectorSubtract()

vector.subtract()

vectorScale()

vector.scale()

vectorQuantizeInt8()

vector.quantizeint8()

vectorQuantizeBinary()

vector.quantizebinary()

Both forms work identically and can be used interchangeably. The namespaced form (vector.*) is recommended for new code and is documented in reference/extended-functions.adoc#extended-functions.

vectorAdd()

Returns vector of element-wise sums of argument vectors, which are expected to be of the same size.

Syntax: vector(<vector|field>, <vector|field>)

Examples

SELECT vectorAdd([1.0, 2.0, 3.0], [2.0, 3.0, 4.0])

vectorApproxDistance()

Computes approximate distance between quantized vectors without intermediate full dequantization. Two modes are supported:

'INT8' faster than floats, preserves ranking order
'BINARY' very fast (count differing bits), reduces operations eight-fold, returns normalized Hamming distance

Syntax: vector(<vector|field>, <vector|field> [, <string>])

Examples

SELECT vectorApproxDistance(vectorQuantizeInt8([1.0, 2.0, 3.0]), vectorQuantizeInt8([1.0, 3.0, 3.0]),'INT8')

vectorAvg()

Aggregate function computing element-wise arithmetic average of vectors.

Syntax: vectorAvg(<field>)

Examples

SELECT vectorAvg(myvector) FROM doc

vectorClip()

Forces every vector element to the range between min and max arguments, where min less or equal to max is assumed. Elements exceeding this ranged are set to min or max respectively

Syntax: vectorClip(<vector|field>, <scalar>, <scalar>)

Examples

SELECT vectorClip([1.0, 5.0, 10.0], 2.0, 8.0)

vectorCosineSimilarity()

Computes the cosine similarity between two vectors. Returns a value between -1 and 1, where 1 means same proportional, 0 means orthogonal, and -1 means opposite vectors.

Syntax: vector(<vector|field>, <vector|field>)

Examples

SELECT vectorCosineSimilarity([1.0, 2.0, 3.0], [2.0, 2.0, 3.0])

vectorDenseToSparse()

converts a dense to a sparse vector representation, whereas a sparse element is determined by the optional threshold argument which is exact zero (0.0) by default.

Syntax: vectorDenseToSparse(<vector|field>)

Examples

SELECT vectorDenseToSparse([0.5, 0.0, 0.1], 0.2)

vectorDequantizeInt8()

Dequantized byte array to float vector using min-max scaling, recovering original vector only approximately.

Syntax: vectorDequantizeInt8(<vector|field>, <scalar>, <scalar>)

Examples

SELECT vectorDequantizeInt8(vectorQuantizeInt8([1.0, 2.0, 3.0]), 1.0, 3.0)

vectorDimension()

Returns the dimension of argument vector, being the length of its underlying array.

Syntax: vectorDimension(<vector|field>)

Examples

SELECT vectorDimension([1.0, 2.0, 3.0])

vectorDotProduct()

Computes standard dot product (inner product) between two argument vectors.

Syntax: vectorDotProduct(<vector|field>, <vector|field>)

Examples

SELECT vectorDotProduct([1.0, 0.0, 1.0], [0.0, 1.0, 1.0])

vectorHasInf()

Returns true if one or more elements in argument vector are positive or negative infinity.

Syntax: vectorHasInf(<vector|field>)

SELECT vectorHasInf([1.0, 10e400, 2.0])

vectorHasNaN()

Returns true if one or more elements in argument vector are NaN (not-a-number), a special value in IEEE floating point numbers.

Syntax: vectorHasNaN(<vector|field>)

vectorHybridScore()

Computes weighted average of two numbers with argument weight alpha.

Syntax: vectorHybridScore(<scalar>, <scalar>, <scalar>)

Examples

SELECT vectorHybridScore(1.0, 2.0, 0.5)

vectorIsNormalized()

Checks if the argument vector is normalized, meaning if its length (L2 norm) is one, up to an optional threshold argument, which is taken as 0.001 by default.

Syntax: vectorIsNormalized(<vector|field> [, <tolerance>])

Examples

SELECT vectorIsNormalized([1.0, 0.0, 1.0], 1.0)

vectorL1Norm()

Computes L1 norm (Manhattan norm) of argument vector.

Syntax: vectorL1Norm(<vector|field>)

Examples

SELECT vectorL1Norm([1.0, 2.0, 3.0])

vectorL2Distance()

Computes the L2 distance (Euclidean distance) between two argument vectors.

Syntax: vectorL2Distance(<vector|field>, <vector|field>)

Examples

SELECT vectorL2Distance([1.0, 2.0, 3.0], [2.0, 2.0, 3.0])

vectorLInfNorm()

Computes L∞-norm (Maximum norm) of argument vector.

Syntax: vectorLInfNorm(<vector|field>)

Examples

SELECT vectorLInfNorm([1.0, 2.0, 3.0])

vectorMagnitude()

Computes L2 norm (Euclidean norm) of argument vector.

Syntax: vectorMagnitude(<vector|field>)

Examples

SELECT vectorMagnitude([1.0, 2.0, 3.0])

vectorMax()

Aggregate function computing element-wise maximum of vectors.

Syntax: vectorMax(<field>)

Examples

SELECT vectorMax(myvector) FROM doc

vectorMin()

Aggregate function computing element-wise minimum of vectors.

Syntax: vectorMin(<field>)

Examples

SELECT vectorMin(myvector) FROM doc

vectorMultiScore()

Combines multiple scores form different vectors using a fusion method. The scores are assumed to be represented in a vector, the fusion method is selected by a string. The weights vector is only required for the weighted average method and expected to be of the same size as the score vector. Available fusion methods:

MAX maximum score (ColBERT style)
AVG arithmetic average of scores
MIN minimum score
WEIGHTED weighted average

Syntax: vectorMultiScore(<vector>, <string> [, <vector>])

Examples

SELECT vectorMultiScore([0.9, 0.7, 0.8], 'MAX')

vectorNeighbours()

Returns the k nearest neighbors from a vector index. When an index is defined on a parent type with inheritance, you can search across all types (using the parent type name) or filter to a specific child type (using the child type name).

Syntax: vectorNeighbours(<index-spec>, <vector|key>, <k>)

Where:

<index-spec> - Index specification in the format 'TypeName[propertyName]'. Use the parent type to search all records, or a child type to search only that type’s records.
<vector|key> - Query vector (array of floats) or a key identifier to look up the vector.
<k> - Number of nearest neighbors to return.

Examples

Basic usage:

SELECT vectorNeighbours('Document[embedding]', [0.1, 0.2, 0.3], 5)

Type-Specific Search with Inheritance

When a vector index is created on a parent type, sub-indexes are automatically created for child types. You can search within a specific child type by specifying the child type name:

-- Schema: EMBEDDING (parent) with children EMBEDDING_IMAGE, EMBEDDING_DOCUMENT
-- Index created on: EMBEDDING.vector

-- Search ONLY in EMBEDDING_IMAGE records
SELECT vectorNeighbours('EMBEDDING_IMAGE[vector]', $queryVector, 10) AS neighbors
FROM EMBEDDING_IMAGE LIMIT 1

-- Search across ALL types (parent + all children)
SELECT vectorNeighbours('EMBEDDING[vector]', $queryVector, 10) AS neighbors
FROM EMBEDDING LIMIT 1

vectorNormalize()

Normalizes vector to unit length (1.0), in the L2 norm.

Syntax: vectorNormalize(<vector|field>)

Examples

SELECT vectorNormalize([1.0, 2.0, 3.0])

vectorNormalizeScores()

Normalizes a vector to unit range ([0, 1]) using min-max normalization.

Syntax: vectorNormalizeScores(<vector>)

Examples

SELECT vectorNormalizeScores([1.0, 2.0, 3.0])

vectorQuantizeInt8()

Quantize argument vector to byte elements (8 bit per dimension) using min-max scaling.

Syntax: vectorQuantizeInt8(<vector|field>)

Examples

SELECT vectorQuantizeInt8([0.1, 0.5, 0.9])

vectorQuantizeBinary()

Quantize argument vector to binary elements (1 bit per dimension) using median threshold.

Syntax: vectorQuantizeBinary(<vector|field>)

Examples

SELECT vectorQuantizeBinary([0.1, 0.5, 0.9])

vectorRRFScore()

Compute reciprocal rank fusion (RRF) for combining multiple ranks. The last argument k is the center rank with a default value of 60.

Syntax: vectorRRFScore(<scalar>, <scalar>, … [, <scalar>])

Examples

SELECT vectorRRFScore(1.0, 2.0, 4.0, 60)

vectorScale()

Scales argument vector element-wise by argument scalar.

Syntax: vectorScale(<vector|field>, <scalar|field>)

Examples

SELECT vectorScale([1.0, 2.0, 3.0], 4.0)

vectorScoreTransform()

Transform score to ehsape score distribution. Supported transformations:

'LINEAR' no transformation
'SIGMOID' S-shaped curve, see Logistic function
'LOG' natural logarithm
'EXP' exponential function

Syntax: vectorScoreTransform(<scalar>, <string>)

Examples

SELECT vectorScoreTransform(0.5, 'SIGMOID')

vectorSparseCreate()

Create sparse vector from indices and value arrays. By default the dimension is inferred form the index array. Optionally, the dimension can be set via the dimension argument.

Syntax: vectorSparseCreate(<vector|field>, <vector|field> [, <integer>])

Examples

SELECT vectorSparseCreate([0, 2, 5], [0.5, 0.3, 0.8], 7)

vectorSparseDot()

Computes dot product (inner product) between two sparse vectors.

Syntax: vectorSparseDot(<sparsevector>, <sparsevector>)

Examples

SELECT vectorSparseDot(vectorDenseToSparse([1.0, 2.0, 3.0]), vectorDenseToSparse([1.0, 1.0, 1.0]))

vectorSparseToDense()

Converts a sparse vector to dense vector representation.

Syntax: vectorSparseToDense(<sparsevector>)

Examples

SELECT vectorSparseToDense(vectorSparseCreate([0, 2], [0.5, 0.3]))

vectorSparsity()

Returns the fraction of elements an argument vector with absolute values below an argument threshold.

Syntax: vectorSparsity(<vector>, <scalar>)

Examples

SELECT vectorSparsity([0.01, 0.1, 0.05, 0.02], 0.06)

vectorStdDev()

Computes the standard deviation (square-root of variance) of an argument vector.

Syntax: vectorStdDev(<vector|field>)

Examples

SELECT vectorStdDev([1.0, 2.0, 3.0])

vectorSubtract()

Returns vector of element-wise differences of argument vectors, which are expected to be of the same size.

Syntax: vectorSubtract(<vector|field>, <vector|field>)

Examples

SELECT vectorSubtract([1.0, 2.0, 3.0], [2.0, 3.0, 4.0])

vectorSum()

Aggregate function computing element-wise sum of vectors.

Syntax: vectorSum(<field>)

Examples

SELECT vectorSum(myvector) FROM doc

vectorToString()

Convert argument vector to string representation in various formats:

'COMPACT' Single line, i.e. "[1.0, 2.0, 3.0]" (default)
'PRETTY' Multi-line with formatting
'PYTHON' Python list format
'MATLAB' Matlab format

Syntax: vectorToString(<vector|field> [, <string>])

Examples

SELECT vectorToString([0.5, 0.25, 0.75])

vectorVariance()

Computes the variance (average spread) of an argument vector.

Syntax: vectorVariance(<vector|field>)

Examples

SELECT vectorVariance([1.0, 2.0, 3.0])

version()

Returns the ArcadeDB version number and build as string.

Syntax: version()

Examples

SELECT version()

Full-Text Function Namespace

The four full-text search functions live under the fulltext.* namespace. The original snake_case names remain available as aliases for backward compatibility.

Legacy alias Canonical name

search_fields()

fulltext.searchFields()

search_index()

fulltext.searchIndex()

search_fields_more()

fulltext.searchFieldsMore()

search_index_more()

fulltext.searchIndexMore()

Both forms dispatch to the same implementation. The namespaced form is recommended for new code.

fulltext.searchIndex()

Searches a named full-text index using Lucene query syntax. Returns true for matching documents; use $score to rank results. See how-to/data-modeling/full-text-index.adoc#full-text-index-searching for full documentation, Lucene syntax, and examples.

Syntax: fulltext.searchIndex(indexName, query) (alias: search_index)

Examples

SELECT title, $score
FROM Article
WHERE `fulltext.searchIndex`('Article[content]', '+java +spring')
ORDER BY $score DESC

fulltext.searchFields()

Searches a full-text index resolved automatically from field names. Equivalent to fulltext.searchIndex but without needing to know the index name. See how-to/data-modeling/full-text-index.adoc#full-text-index-searching for full documentation.

Syntax: fulltext.searchFields(fieldNames, query) (alias: search_fields)

Examples

SELECT title, $score
FROM Article
WHERE `fulltext.searchFields`(['title', 'body'], 'database tutorial')
ORDER BY $score DESC

fulltext.searchIndexMore()

Finds documents similar to one or more source documents ("More Like This"). Use $similarity (0.0-1.0) to rank results. See how-to/data-modeling/full-text-index.adoc#full-text-index-mlt for full documentation including configuration options.

Syntax:

fulltext.searchIndexMore(indexName, sourceRIDs)
fulltext.searchIndexMore(indexName, sourceRIDs, { <option>: <value>, ... })

Alias: search_index_more.

The optional trailing argument is a map of tuning options. Accepted keys: minTermFreq, minDocFreq, maxDocFreqPercent, maxQueryTerms, minWordLen, maxWordLen, boostByScore, excludeSource, maxSourceDocs. Unknown keys are rejected with a descriptive error.

Examples

SELECT title, $similarity
FROM Article
WHERE `fulltext.searchIndexMore`('Article[content]', [#10:3], { minTermFreq: 1, boostByScore: true })
ORDER BY $similarity DESC
LIMIT 5

fulltext.searchFieldsMore()

Same as fulltext.searchIndexMore but resolves the full-text index automatically from field names. See how-to/data-modeling/full-text-index.adoc#full-text-index-mlt for full documentation.

Syntax:

fulltext.searchFieldsMore(fieldNames, sourceRIDs)
fulltext.searchFieldsMore(fieldNames, sourceRIDs, { <option>: <value>, ... })

Alias: search_fields_more. Options map keys are the same as fulltext.searchIndexMore.

Examples

SELECT title, $similarity
FROM Article
WHERE `fulltext.searchFieldsMore`(['content'], [#10:3], { minTermFreq: 1, excludeSource: true })
ORDER BY $similarity DESC