| search | ||||
|---|---|---|---|---|
|
SQL Functions are all the functions bundled with OrientDB SQL engine. You can create your own Database Functions in any language supported by JVM. Look also to SQL Methods.
SQL Functions can work in 2 ways based on the fact that they can receive 1 or more parameters:
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 employeeThis will always return 1 record with the sum of salary field.
When two or more parameters are passed:
SELECT SUM(salary, extra, benefits) AS total FROM employeeThis 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 ProfilesIn 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
Get the adjacent outgoing vertices starting from the current record as Vertex.
Syntax: out([<label-1>][,<label-n>]*)
Available since: 1.4.0
Get all the outgoing vertices from all the Vehicle vertices:
SELECT out() FROM VGet all the incoming 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'Get the adjacent incoming vertices starting from the current record as Vertex.
Syntax:
in([<label-1>][,<label-n>]*)
Available since: 1.4.0
Get all the incoming vertices from all the Vehicle vertices:
SELECT in() FROM VGet all the incoming vertices connected with edges with label (class) "Friend" and "Brother":
SELECT in('Friend','Brother') FROM VGet the adjacent outgoing and incoming vertices starting from the current record as Vertex.
Syntax:
both([<label1>][,<label-n>]*)
Available since: 1.4.0
Get all the incoming and outgoing vertices from vertex with rid #13:33:
SELECT both() FROM #13:33Get all the incoming and outgoing vertices connected with edges with label (class) "Friend" and "Brother":
SELECT both('Friend','Brother') FROM VGet the adjacent outgoing edges starting from the current record as Vertex.
Syntax:
outE([<label1>][,<label-n>]*)
Available since: 1.4.0
Get all the outgoing edges from all the vertices:
SELECT outE() FROM VGet all the outgoing edges of type "Eats" from all the SocialNetworkProfile vertices:
SELECT outE('Eats') FROM SocialNetworkProfileGet the adjacent incoming edges starting from the current record as Vertex.
Syntax:
inE([<label1>][,<label-n>]*)
Get all the incoming edges from all the vertices:
SELECT inE() FROM VGet all the incoming edges of type "Eats" from the Restaurant 'Bella Napoli':
SELECT inE('Eats') FROM Restaurant WHERE name = 'Bella Napoli'Get the adjacent outgoing and incoming edges starting from the current record as Vertex.
Syntax: bothE([<label1>][,<label-n>]*)
Available since: 1.4.0
Get both incoming and outgoing edges from all the vertices:
SELECT bothE() FROM VGet all the incoming and outgoing edges of type "Friend" from the Profile with nick 'Jay'
SELECT bothE('Friend') FROM Profile WHERE nick = 'Jay'Get the adjacent outgoing and incoming Vertices starting from the current record as Edge.
Syntax: bothV()
Available since: 1.4.0
Get both incoming and outgoing vertices from all the edges:
SELECT bothV() FROM EGet outgoing vertices starting from the current record as Edge.
Syntax:
outV()
Available since: 1.4.0
SELECT outV() FROM EGet incoming vertices starting from the current record as Edge.
Syntax:
inV()
Available since: 1.4.0
SELECT inV() FROM ESyntax: eval('<expression>')
Evaluates the expression between quotes (or double quotes).
Available since: 1.4.0
SELECT eval('price * 120 / 100 - discount') AS finalPrice FROM OrderReturns the first field/value not null parameter. If no field/value is not null, returns null.
Syntax:
coalesce(<field|value> [, <field-n|value-n>]*)
Available since: 1.3.0
SELECT coalesce(amount, amount2, amount3) FROM AccountSyntax:
if(<expression>, <result-if-true>, <result-if-false>)
Evaluates a condition (first parameters) and returns the second parameter if the condition is true, the third one otherwise
SELECT if(eval("name = 'John'"), "My name is John", "My name is not John") FROM Person
Returns the passed field/value (or optional parameter return_value_if_not_null). If field/value is not null, otherwise it returns return_value_if_null.
Syntax:
ifnull(<field/value>, <return_value_if_null>)Available since: 1.3.0
SELECT ifnull(salary, 0) FROM AccountAvailable since: 1.4.0
This function has two meanings:
- When used on a collection field, it unwinds the collection in the field and use it as result.
- When used on a link (RID) field, it expands the document pointed by that link.
Syntax: expand(<field>)
Since version 2.1 the preferred operator to unwind collections is UNWIND. Expand usage for this use case will probably be deprecated in next releases
on collectinos:
SELECT EXPAND( addresses ) FROM Account //where addresses is a link liston RIDs
SELECT EXPAND( country ) FROM City //where country is a linkThis replaces the flatten() now deprecated
Deprecated, use the EXPAND() instead.
Extracts the collection in the field and use it as result.
Syntax:
flatten(<field>)
Available since: 1.0rc1
SELECT flatten( addresses ) FROM AccountRetrieves only the first item of multi-value fields (arrays, collections and maps). For non multi-value types just returns the value.
Syntax: first(<field>)
Available since: 1.2.0
select first( addresses ) from AccountRetrieves only the last item of multi-value fields (arrays, collections and maps). For non multi-value types just returns the value.
Syntax: last(<field>)
Available since: 1.2.0
SELECT last( addresses ) FROM AccountCounts the records that match the query condition. If * is not used as a field, then the record will be counted only if the field content is not null.
Syntax: count(<field>)
Available since: 0.9.25
SELECT COUNT(*) FROM AccountReturns 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>]* )
Available since: 0.9.25
Returns the minimum salary of all the Account records:
SELECT min(salary) FROM AccountReturns the minimum value between 'salary1', 'salary2' and 'salary3' fields.
SELECT min(salary1, salary2, salary3) FROM AccountReturns 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>]* )
Available since: 0.9.25
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 AccountReturns the absolute value. It works with Integer, Long, Short, Double, Float, BigInteger, BigDecimal, null.
Syntax: abs(<field>)
Available since: 2.2
SELECT abs(score) FROM Account
SELECT abs(-2332) FROM Account
SELECT abs(999) FROM AccountReturns the average value.
Syntax: avg(<field>)
Available since: 0.9.25
SELECT avg(salary) FROM AccountSyntax: sum(<field>)
Returns the sum of all the values returned.
Available since: 0.9.25
SELECT sum(salary) FROM AccountReturns a date formatting a string. <date-as-string> is the date in string format, and <format> is the date format 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>] )
Available since: 0.9.25
SELECT FROM Account WHERE created <= date('2012-07-02', 'yyyy-MM-dd')Returns the current date time. If executed with no parameters, it returns a Date object, otherwise a string with the requested format/timezone. To know more about it, look at Managing Dates.
Syntax: sysdate( [<format>] [,<timezone>] )
Available since: 0.9.25
SELECT sysdate('dd-MM-yyyy') FROM AccountFormats a value using the String.format() conventions. Look here for more information.
Syntax: format( <format> [,<arg1> ](,<arg-n>]*.md)
Available since: 0.9.25
SELECT format("%d - Mr. %s %s (%s)", id, name, surname, address) FROM AccountA*'s algorithm describes how to find the cheapest path from one node to another node in a directed weighted graph with husrestic 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 represnts the map of options.
If property is not defined in edge or is null, distance between vertexes are 0 .
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' // (MANHATAN, MAXAXIS, DIAGONAL, EUCLIDEAN, EUCLIDEANNOSQR, CUSTOM)
}
SELECT astar($current, #8:10, 'weight') FROM VReturns the cheapest path between two vertices using the [http://en.wikipedia.org/wiki/Dijkstra's_algorithm Dijkstra algorithm] where the weightEdgeFieldName parameter is the field containing the weight. Direction can be OUT (default), IN or BOTH.
Syntax: dijkstra(<sourceVertex>, <destinationVertex>, <weightEdgeFieldName> [, <direction>])
Available since: 1.3.0
SELECT dijkstra($current, #8:10, 'weight') FROM VReturns the shortest path between two vertices. Direction can be OUT (default), IN or BOTH.
Available since: 1.3.0
Syntax: shortestPath( <sourceVertex>, <destinationVertex> [, <direction> [, <edgeClassName> [, <additionalParams>]]])
Where:
sourceVertexis the source vertex where to start the pathdestinationVertexis the destination vertex where the path endsdirection, 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. Since 2.0.9 and 2.1-rc2. This can also be a list of edge class names (eg.["edgeType1", "edgeType2"])additionalParams(since v 2.1.12), optional, here you can pass a map of additional parametes (Map<String, Object> in Java, JSON from SQL). Currently allowed parameters are- 'maxDepth': integer, maximum depth for paths (ignore path longer that 'maxDepth')
SELECT shortestPath(#8:32, #8:10)SELECT shortestPath(#8:32, #8:10, 'OUT')Example 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')Example 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'])SELECT shortestPath(#8:32, #8:10, null, null, {"maxDepth": 5})Syntax: distance( <x-field>, <y-field>, <x-value>, <y-value> )
Returns the distance between two points in the globe using the Haversine algorithm. Coordinates must be as degrees.
Available since: 0.9.25
SELECT FROM POI WHERE distance(x, y, 52.20472, 0.14056 ) <= 30Syntax: distinct(<field>)
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 OrientDB, a function with parenthesis and only one field can be specified.
Available since: 1.0rc2
SELECT distinct(name) FROM CitySyntax: unionall(<field> [,<field-n>]*)
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.
Available since: 1.7
SELECT unionall(friends) FROM profileselect unionall(inEdges, outEdges) from OGraphVertex where label = 'test'Syntax: intersect(<field> [,<field-n>]*)
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.
Available since: 1.0rc2
SELECT intersect(friends) FROM profile WHERE jobTitle = 'programmer'SELECT intersect(inEdges, outEdges) FROM OGraphVertexSyntax: 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.
Available since: 1.0rc2
SELECT difference(tags) FROM bookSELECT difference(inEdges, outEdges) FROM OGraphVertexSyntax: symmetricDifference(<field> [,<field-n>]*)
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.
Available since: 2.0.7
SELECT difference(tags) FROM bookSELECT difference(inEdges, outEdges) FROM OGraphVertexAdds a value to a set. The first time the set is created. If <value> is a collection, then is merged with the set, otherwise <value> is added to the set.
Syntax: set(<field>)
Available since: 1.2.0
SELECT name, set(roles.name) AS roles FROM OUserAdds a value to a list. The first time the list is created. If <value> is a collection, then is merged with the list, otherwise <value> is added to the list.
Syntax: list(<field>)
Available since: 1.2.0
SELECT name, list(roles.name) AS roles FROM OUserCreates a map from single values.
Syntax: map(<key>, <value>, [<key>, <value>]*)
-
With two or less parameters, it works as an aggregate function and adds a value to a map. The first time the map is created. If
<value>is a map, then is merged with the map, otherwise the pair<key>and<value>is added to the map as new entry. -
With more than 2 params, it creates a map from single key/value pairs, eg.
SELECT map("name", "foo", "surname", "bar") as theMapreturns
{
"theMap": {
"name": "foo",
"surname": "bar"
}
}
Available since: 1.2.0
SELECT map(name, roles.name) FROM OUserReturns 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
Available since: 1.7
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 )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
Available since: 1.7
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 )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
Available since: 1.7
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 )Returns the values that occur with the greatest frequency. Nulls are ignored in the calculation.
Syntax: mode(<field>)
Available since: 2.0-M1
SELECT mode(salary) FROM AccountReturns 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>)
Available since: 2.0-M1
select median(salary) from AccountReturns 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.
Syntax: percentile(<field> [, <quantile-n>]*)
The quantiles have to be in the range 0-1
Available since: 2.0-M1
SELECT percentile(salary, 0.95) FROM Account
SELECT percentile(salary, 0.25, 0.75) AS IQR FROM AccountReturns the middle variance: the average of the squared differences from the mean. Nulls are ignored in the calculation.
Syntax: variance(<field>)
Available since: 2.0-M1
SELECT variance(salary) FROM AccountReturns the standard deviation: the measure of how spread out values are. Nulls are ignored in the calculation.
Syntax: stddev(<field>)
Available since: 2.0-M1
SELECT stddev(salary) FROM AccountGenerates 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.
Available since: 2.0-M1
Syntax: uuid()
Insert a new record with an automatic generated id:
INSERT INTO Account SET id = UUID()The SQL engine can be extended with custom functions written with a Scripting language or via Java.
Look at the Functions page.
Before to use them in your queries you need to register:
// REGISTER 'BIGGER' FUNCTION WITH FIXED 2 PARAMETERS (MIN/MAX=2)
OSQLEngine.getInstance().registerFunction("bigger",
new OSQLFunctionAbstract("bigger", 2, 2) {
public String getSyntax() {
return "bigger(<first>, <second>)";
}
public Object execute(Object[] iParameters) {
if (iParameters[0] == null || iParameters[1] == null)
// CHECK BOTH EXPECTED PARAMETERS
return null;
if (!(iParameters[0] instanceof Number) || !(iParameters[1] instanceof Number))
// EXCLUDE IT FROM THE RESULT SET
return null;
// USE DOUBLE TO AVOID LOSS OF PRECISION
final double v1 = ((Number) iParameters[0]).doubleValue();
final double v2 = ((Number) iParameters[1]).doubleValue();
return Math.max(v1, v2);
}
public boolean aggregateResults() {
return false;
}
});Now you can execute it:
List<ODocument> result = database.command(
new OSQLSynchQuery<ODocument>("SELECT FROM Account WHERE bigger( salary, 10 ) > 10") )
.execute();