OrientDB Manual 1.7.8

SQL - Functions

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:

Aggregated mode

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.

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 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.

Bundled functions

Functions by category

Graph Math Collections Misc
out() eval() set() date()
in() min() map() sysdate()
both() max() list() format()
outE() sum() difference() distance()
inE() first() ifnull()
bothE() intersect() coalescence()
outV() avg() distinct()
inV() count() expand()
traversedElement() union()
traversedVertex() flatten()
traversedEdge() last()
shortestPath()
dijkstra()

Functions by name

avg() both() bothE() coalescence() count()
date() difference() dijkstra() distance() distinct()
eval() expand() format() first() flatten()
ifnull() in() inE() inV() intersect()
list() map() min() max() out()
outE() outV() set() shortestPath() sum()
sysdate() traversedElement() traversedEdge() traversedVertex() union()

out()

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

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

Available since: 1.4.0

Example

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'

in()

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

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

Available since: 1.4.0

Example

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

both()

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

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

Available since: 1.4.0

Example

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

outE()

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

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

Available since: 1.4.0

Example

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

inE()

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

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

Example

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'

bothE()

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

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

Available since: 1.4.0

Example

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'

outV()

Get outgoing vertices starting from the current record as Edge.

Syntax: outV()

Available since: 1.4.0

Example

SELECT outV() from E

inV()

Get incoming vertices starting from the current record as Edge.

Syntax: inV()

Available since: 1.4.0

Example

SELECT inV() from E

eval()

Syntax: eval('<expression>')

Evaluates the expression between quotes (or double quotes).

Available since: 1.4.0

Example

SELECT eval('price * 120 / 100 - discount') as finalPrice from Order

coalesce()

Returns the first field/value not null parameter. If no field/value is not null, returns null.

Syntax: coalesce(&lt;field&#124;value&gt;)

Available since: 1.3.0

Example

SELECT coalesce(amount, amount2, amount3) from Account

ifnull()

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(&lt;field&#124;value&gt;, &lt;return_value_if_null&gt; [,&lt;return_value_if_not_null&gt;](,&lt;field&.md#124;value&gt;]*)

Available since: 1.3.0

Example

SELECT ifnull(salary, 0) from Account

expand()

Expands the collection in the field and use it as result.

Available since: 1.4.0

Syntax: expand(<field>)

Example

select expand( addresses ) from Account.

This replaces the flatten() now deprecated


flatten()

Deprecated, use the EXPAND() instead.

Extracts the collection in the field and use it as result.

Syntax: flatten(<field>)

Available since: 1.0rc1

Example

select flatten( 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>)

Available since: 1.2.0

Example

select first( addresses ) from Account

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

Available since: 1.2.0

Example

select last( addresses ) from Account

count()

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

Example

select count(*) from Account

min()

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

Example

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

max()

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

Example

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

avg()

Returns the average value.

Syntax: avg(<field>)

Available since: 0.9.25

Example

select avg(salary) from Account

sum()

Syntax: sum(<field>)

Returns the sum of all the values returned.

Available since: 0.9.25

Example

select average(salary) from Account

date()

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

Example

select from Account where created <= date('2012-07-02', 'yyyy-MM-dd')

sysdate()

Returns the current date time.

Syntax: sysdate( [<format>] [,<timezone>] )

Available since: 0.9.25

Example

select sysdate('dd-MM-yyyy') from Account

format()

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

Example

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

dijkstra()

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

Example

select dijkstra($current, #8:10, 'weight') from V

shortestPath()

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

Example

select shortestPath(#8:32, #8:10, 'BOTH')

distance()

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

Example

select from POI where distance(x, y, 52.20472, 0.14056 ) <= 30

distinct()

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

Example

select distinct(name) from City

union()

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

Example

select union(friends) from profile
select union(inEdges, outEdges) from OGraphVertex where label = 'test'

intersect()

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

Example

select intersect(friends) from profile where jobTitle = 'programmer'
select intersect(inEdges, outEdges) from OGraphVertex

difference()

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

Example

select difference(tags) from book

```sql
select difference(inEdges, outEdges) from OGraphVertex

set()

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

Example

SELECT name, set(roles.name) as roles FROM OUser

list()

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

Example

SELECT name, list(roles.name) as roles FROM OUser

map()

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

Example

SELECT map(name, roles.name) FROM OUser

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

Available since: 1.7

Example

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 )

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

Available since: 1.7

Example

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 )

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

Available since: 1.7

Example

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 )

Custom functions

The SQL engine can be extended with custom functions written with a Scripting language or via Java.

Database's function

Look at the Functions page.

Custom functions in Java

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();