SQL Methods

SQL Methods are similar to SQL functions but they apply to values. In Object Oriented paradigm they are called "methods", as functions related to a class. So what's the difference between a function and a method?

This is a SQL function:

SELECT FROM sum( salary ) FROM employee

This is a SQL method:

SELECT FROM salary.toJSON() FROM employee

As you can see the method is executed against a field/value. Methods can receive parameters, like functions. You can concatenate N operators in sequence.

Note: operators are case-insensitive.

Bundled methods

Methods by category

ConversionsString manipulationCollectionsMisc
convert()append()[]exclude()
asBoolean()charAt()size()include()
asDate()indexOf()remove()javaType()
asDatetime()left()removeAll()toJSON()
asDecimal()right()keys()type()
asFloat()prefix()values()
asInteger()trim()
asList()replace()
asLong()length()
asMap()subString()
asSet()toLowerCase()
asString()toUpperCase()
normalize()hash()
format()

Methods by name
[]append()asBoolean()asDate()asDatetime()
asDecimal()asFloat()asInteger()asList()asLong()
asSet()asString()charAt()convert()exclude()
hash()include()indexOf()javaType()keys()
length()normalize()prefix()remove()removeAll()
right()size()subString()trim()toJSON()
toUpperCase()type()values()

[]

Execute an expression against the item. An item can be a multi-value object like a map, a list, an array or a document. For documents and maps, the item must be a string. For lists and arrays, the index is a number.

Syntax: <value>[<expression>]

Applies to the following types:

  • document,
  • map,
  • list,
  • array

Examples

Get the item with key "phone" in a map:

SELECT FROM Profile WHERE '+39' IN contacts[phone].left(3)

Get the first 10 tags of posts:

SELECT FROM tags[0..9] FROM Posts

#### History

  • 1.0rc5: First version

.append()

Appends a string to another one.

Syntax: <value>.append(<value>)

Applies to the following types:

  • string

Examples

SELECT name.append(' ').append(surname) FROM Employee

#### History

  • 1.0rc1: First version

.asBoolean()

Transforms the field into a Boolean type. If the origin type is a string, then "true" and "false" is checked. If it's a number then 1 means TRUE while 0 means FALSE.

Syntax: <value>.asBoolean()

Applies to the following types:

  • string,
  • short,
  • int,
  • long

Examples

SELECT FROM Users WHERE online.asBoolean() = true

#### History

  • 0.9.15: First version

.asDate()

Transforms the field into a Date type. To know more about it, look at Managing Dates.

Syntax: <value>.asDate()

Applies to the following types:

  • string,
  • long

Examples

Time is stored as long type measuring milliseconds since a particular day. Returns all the records where time is before the year 2010:

SELECT FROM Log WHERE time.asDateTime() < '01-01-2010 00:00:00'

#### History

  • 0.9.14: First version

.asDateTime()

Transforms the field into a Date type but parsing also the time information. To know more about it, look at Managing Dates.

Syntax: <value>.asDateTime()

Applies to the following types:

  • string,
  • long

Examples

Time is stored as long type measuring milliseconds since a particular day. Returns all the records where time is before the year 2010:

SELECT FROM Log WHERE time.asDateTime() < '01-01-2010 00:00:00'

#### History

  • 0.9.14: First version

.asDecimal()

Transforms the field into an Decimal type. Use Decimal type when treat currencies.

Syntax: <value>.asDecimal()

Applies to the following types:

  • any

Examples

SELECT salary.asDecimal() FROM Employee

#### History

  • 1.0rc1: First version

.asFloat()

Transforms the field into a float type.

Syntax: <value>.asFloat()

Applies to the following types:

  • any

Examples

SELECT ray.asFloat() > 3.14

#### History

  • 0.9.14: First version

.asInteger()

Transforms the field into an integer type.

Syntax: <value>.asInteger()

Applies to the following types:

  • any

Examples

Converts the first 3 chars of 'value' field in an integer:

SELECT value.left(3).asInteger() FROM Log

#### History

  • 0.9.14: First version

.asList()

Transforms the value in a List. If it's a single item, a new list is created.

Syntax: <value>.asList()

Applies to the following types:

  • any

Examples

SELECT tags.asList() FROM Friend

#### History

  • 1.0rc2: First version

.asLong()

Transforms the field into a Long type. To know more about it, look at Managing Dates.

Syntax: <value>.asLong()

Applies to the following types:

  • any

Examples

SELECT date.asLong() FROM Log

#### History

  • 1.0rc1: First version

.asMap()

Transforms the value in a Map where even items are the keys and odd items are values.

Syntax: <value>.asMap()

Applies to the following types:

  • collections

Examples

SELECT tags.asMap() FROM Friend

#### History

  • 1.0rc2: First version

.asSet()

Transforms the value in a Set. If it's a single item, a new set is created. Sets doesn't allow duplicates.

Syntax: <value>.asSet()

Applies to the following types:

  • any

Examples

SELECT tags.asSet() FROM Friend

#### History

  • 1.0rc2: First version

.asString()

Transforms the field into a string type.

Syntax: <value>.asString()

Applies to the following types:

  • any

Examples

Get all the salaries with decimals:

SELECT salary.asString().indexof('.') > -1

#### History

  • 0.9.14: First version

.charAt()

Returns the character of the string contained in the position 'position'. 'position' starts from 0 to string length.

Syntax: <value>.charAt(<position>)

Applies to the following types:

  • string

Examples

Get the first character of the users' name:

SELECT FROM User WHERE name.charAt( 0 ) = 'L'

#### History

  • 0.9.7: First version

.convert()

Convert a value to another type.

Syntax: <value>.convert(<type>)

Applies to the following types:

  • any

Examples

SELECT dob.convert( 'date' ) FROM User

#### History

  • 1.0rc2: First version

.exclude()

Excludes some properties in the resulting document.

Syntax: <value>.exclude(<field-name>[,]*)

Applies to the following types:

  • document record

Examples

SELECT EXPAND( @this.exclude( 'password' ) ) FROM OUser

Starting from 2.2.19 you can specify a wildcard as ending character to exclude all the fields that start with a certain string. Example to exclude all the outgoing and incloming edges:

SELECT EXPAND( @this.exclude( 'out_*', 'in_*' ) ) FROM V

.format()

Returns the value formatted using the common "printf" syntax. For the complete reference goto Java Formatter JavaDoc. To know more about it, look at Managing Dates.

Syntax: <value>.format(<format>)

Applies to the following types:

  • any

Examples

Formats salaries as number with 11 digits filling with 0 at left:

SELECT salary.format("%-011d") FROM Employee

#### History

  • 0.9.8: First version

.hash()

Returns the hash of the field. Supports all the algorithms available in the JVM.

Syntax: <value>.hash([])```

Applies to the following types:

  • string

Example

Get the SHA-512 of the field "password" in the class User:

SELECT password.hash('SHA-512') FROM User

History

  • 1.7: First version

.include()

Include only some properties in the resulting document.

Syntax: <value>.include(<field-name>[,]*)

Applies to the following types:

  • document record

Examples

SELECT EXPAND( @this.include( 'name' ) ) FROM OUser

Starting from 2.2.19 you can specify a wildcard as ending character to inclide all the fields that start with a certain string. Example to include all the fields that starts with amonut:

SELECT EXPAND( @this.exclude( 'amount*' ) ) FROM V

#### History

  • 1.0rc2: First version

.indexOf()

Returns the position of the 'string-to-search' inside the value. It returns -1 if no occurrences are found. 'begin-position' is the optional position where to start, otherwise the beginning of the string is taken (=0).

Syntax: <value>.indexOf(<string-to-search> [, <begin-position>)

Applies to the following types:

  • string

Examples

Returns all the UK numbers:

SELECT FROM Contact WHERE phone.indexOf('+44') > -1

#### History

  • 0.9.10: First version

.javaType()

Returns the corresponding Java Type.

Syntax: <value>.javaType()

Applies to the following types:

  • any

Examples

Prints the Java type used to store dates:

SELECT FROM date.javaType() FROM Events

#### History

  • 1.0rc1: First version

.keys()

Returns the map's keys as a separate set. Useful to use in conjunction with IN, CONTAINS and CONTAINSALL operators.

Syntax: <value>.keys()

Applies to the following types:

  • maps
  • documents

Examples

SELECT FROM Actor WHERE 'Luke' IN map.keys()

#### History

  • 1.0rc1: First version

.left()

Returns a substring of the original cutting from the begin and getting 'len' characters.

Syntax: <value>.left(<length>)

Applies to the following types:

  • string

Examples

SELECT FROM Actors WHERE name.left( 4 ) = 'Luke'

#### History

  • 0.9.7: First version

.length()

Returns the length of the string. If the string is null 0 will be returned.

Syntax: <value>.length()

Applies to the following types:

  • string

Examples

SELECT FROM Providers WHERE name.length() > 0

#### History

  • 0.9.7: First version

.normalize()

Form can be NDF, NFD, NFKC, NFKD. Default is NDF. pattern-matching if not defined is "\p{InCombiningDiacriticalMarks}+". For more information look at Unicode Standard.

Syntax: <value>.normalize( [<form>] [,<pattern-matching>] )

Applies to the following types:

  • string

Examples

SELECT FROM V WHERE name.normalize() AND name.normalize('NFD')

#### History

  • 1.4.0: First version

.prefix()

Prefixes a string to another one.

Syntax: <value>.prefix('<string>')

Applies to the following types:

  • string

Examples

SELECT name.prefix('Mr. ') FROM Profile

#### History

  • 1.0rc1: First version

.remove()

Removes the first occurrence of the passed items.

Syntax: <value>.remove(<item>*)

Applies to the following types:

  • collection

Examples

SELECT out().in().remove( @this ) FROM V

#### History

  • 1.0rc1: First version

.removeAll()

Removes all the occurrences of the passed items.

Syntax: <value>.removeAll(<item>*)

Applies to the following types:

  • collection

Examples

SELECT out().in().removeAll( @this ) FROM V

#### History

  • 1.0rc1: First version

.replace()

Replace a string with another one.

Syntax: <value>.replace(<to-find>, <to-replace>)

Applies to the following types:

  • string

Examples

SELECT name.replace('Mr.', 'Ms.') FROM User

#### History

  • 1.0rc1: First version

Returns a substring of the original cutting from the end of the string 'length' characters.

Syntax: <value>.right(<length>)

Applies to the following types:

  • string

Examples

Returns all the vertices where the name ends by "ke".

SELECT FROM V WHERE name.right( 2 ) = 'ke'

#### History

  • 0.9.7: First version

.size()

Returns the size of the collection.

Syntax: <value>.size()

Applies to the following types:

  • collection

Examples

Returns all the items in a tree with children:

SELECT FROM TreeItem WHERE children.size() > 0

#### History

  • 0.9.7: First version

.subString()

Returns a substring of the original cutting from 'begin' index up to 'end' index (not included).

Syntax: <value>.subString(<begin> [,<end>] )

Applies to the following types:

  • string

Examples

Get all the items where the name begins with an "L":

SELECT name.substring( 0, 1 ) = 'L' FROM StockItems

Substring of OrientDB

SELECT "OrientDB".substring(0,6)

returns Orient

#### History

  • 0.9.7: First version

.trim()

Returns the original string removing white spaces from the begin and the end.

Syntax: <value>.trim()

Applies to the following types:

  • string

Examples

SELECT name.trim() == 'Luke' FROM Actors

#### History

  • 0.9.7: First version

.toJSON()

Returns the record in JSON format.

Syntax: <value>.toJSON([<format>])

Where:

  • format optional, allows custom formatting rules (separate multiple options by comma). Rules are the following:
  • type to include the fields' types in the "@fieldTypes" attribute
  • rid to include records's RIDs as attribute "@rid"
  • version to include records' versions in the attribute "@version"
  • class to include the class name in the attribute "@class"
  • attribSameRow put all the attributes in the same row
  • indent is the indent level as integer. By Default no ident is used
  • fetchPlan is the FetchPlan to use while fetching linked records
  • alwaysFetchEmbedded to always fetch embedded records (without considering the fetch plan)
  • dateAsLong to return dates (Date and Datetime types) as long numers
  • prettyPrint indent the returning JSON in readeable (pretty) way

Applies to the following types:

  • record

Examples

create class Test extends V
insert into Test content {"attr1": "value 1", "attr2": "value 2"}

select @this.toJson('rid,version,fetchPlan:in_*:-2 out_*:-2') from Test

#### History

  • 0.9.8: First version

.toLowerCase()

Returns the string in lower case.

Syntax: <value>.toLowerCase()

Applies to the following types:

  • string

Examples

SELECT name.toLowerCase() == 'luke' FROM Actors

#### History

  • 0.9.7: First version

.toUpperCase()

Returns the string in upper case.

Syntax: <value>.toUpperCase()

Applies to the following types:

  • string

Examples

SELECT name.toUpperCase() == 'LUKE' FROM Actors

#### History

  • 0.9.7: First version

.type()

Returns the value's OrientDB Type.

Syntax: <value>.type()

Applies to the following types:

  • any

Examples

Prints the type used to store dates:

SELECT FROM date.type() FROM Events

#### History

  • 1.0rc1: First version

.values()

Returns the map's values as a separate collection. Useful to use in conjunction with IN, CONTAINS and CONTAINSALL operators.

Syntax: <value>.values()

Applies to the following types:

  • maps
  • documents

Examples

SELECT FROM Clients WHERE map.values() CONTAINSALL ( name is not null)

#### History

  • 1.0rc1: First version