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 receive 1 or more parameters:
When only one parameter is passed. They aggregate the result in only one record. The classic example is the sum():
select sum(salary) from employee
This 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 employee
This will return the sum of the field "salary", "extra" and "benefits" as "total". In case you need to use a function as inline when you've only one parameter, then add a second one like "null":
SELECT first( out('friends').name, null ) as firstFriend FROM Profiles
In this case first() function doesn't aggregate everything in only one record, but returns one record per Profile where the firstFriend is the first item of the collection received as parameter.
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 V
Get 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 V
Get all the incoming vertices connected with edges with label (class) "Friend" and "Brother":
SELECT in('Friend','Brother') from V
Get 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:33
Get all the incoming and outgoing vertices connected with edges with label (class) "Friend" and "Brother":
SELECT both('Friend','Brother') from V
Get 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 V
Get all the outgoing edges of type "Eats" from all the SocialNetworkProfile vertices:
SELECT outE('Eats') from SocialNetworkProfile
Get 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 V
Get 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 V
Get all the incoming and outgoing edges of type "Friend" from the Profile with nick 'Jay'
SELECT bothE('Friend') from Profile where nick = 'Jay'
Get outgoing vertices starting from the current record as Edge.
Syntax: outV()
Available since: 1.4.0
SELECT outV() from E
Get incoming vertices starting from the current record as Edge.
Syntax: inV()
Available since: 1.4.0
SELECT inV() from E
Syntax: eval('<expression>')
Evaluates the expression between quotes (or double quotes).
Available since: 1.4.0
SELECT eval('price * 120 / 100 - discount') as finalPrice from Order
Returns the first field/value not null parameter. If no field/value is not null, returns null.
Syntax: coalesce(<field|value>)
Available since: 1.3.0
SELECT coalesce(amount, amount2, amount3) from Account
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> [,<return_value_if_not_null>](,<field&.md#124;value>]*)
Available since: 1.3.0
SELECT ifnull(salary, 0) from Account
Expands the collection in the field
Available since: 1.4.0
Syntax: expand(<field>)
select expand( addresses ) from Account.
This replaces the flatten() now deprecated
Deprecated, use the EXPAND() instead.
Extracts the collection in the field
Syntax: flatten(<field>)
Available since: 1.0rc1
select flatten( addresses ) from Account
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>)
Available since: 1.2.0
select first( addresses ) from Account
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>)
Available since: 1.2.0
select last( addresses ) from Account
Counts 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 Account
Returns the minimum value. If invoked with more than one parameters, 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 Account
Returns the minimum value between 'salary1', 'salary2' and 'salary3' fields.
select min(salary1, salary2, salary3) from Account
Returns the maximum value. If invoked with more than one parameters, 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 Account
Returns the average value.
Syntax: avg(<field>)
Available since: 0.9.25
select avg(salary) from Account
Syntax: sum(<field>)
Returns the sum of all the values returned.
Available since: 0.9.25
select average(salary) from Account
Returns 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.
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.
Syntax: sysdate( [<format>] [,<timezone>] )
Available since: 0.9.25
select sysdate('dd-MM-yyyy') from Account
Formats 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 Account
Returns 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 V
Returns the shortest path between two vertices. Direction can be OUT (default), IN or BOTH.
Syntax: shortestPath( <sourceVertex>, <destinationVertex> [, <direction>])
Available since: 1.3.0
select shortestPath(#8:32, #8:10, 'BOTH')
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 ) <= 30
Syntax: 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 City
Syntax: union(<field> [,<field-n>]*)
Works as aggregate or inline. If only one argument is passed than aggregates, otherwise executes, and returns, a UNION of the collections received as parameters. Works also with no collection values.
Available since: 1.0rc2
select union(friends) from profile
select union(inEdges, outEdges) from OGraphVertex where label = 'test'
Syntax: intersect(<field> [,<field-n>]*)
Works as aggregate or inline. If only one argument is passed than 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 OGraphVertex
Syntax: difference(<field> [,<field-n>]*)
Works as aggregate or inline. If only one argument is passed than aggregates, otherwise executes, and returns, the DIFFERENCE between the collections received as parameters.
Available since: 1.0rc2
select difference(tags) from book
```sql
select difference(inEdges, outEdges) from OGraphVertex
Adds 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 OUser
Adds 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 OUser
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.
Syntax: map(<key>, <value>)
Available since: 1.2.0
SELECT map(name, roles.name) FROM OUser
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 returnedAvailable 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 returnedAvailable 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 returnedAvailable 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 Account
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>)
Available since: 2.0-M1
select median(salary) from Account
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.
Syntax: percentile(<field> [, <quantile-n>]*)
Available since: 2.0-M1
select percentile(salary, 95) from Account
select percentile(salary, 25, 75) as IQR from Account
Returns 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 Account
Returns 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 Account
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.
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();