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.
| Conversions | String manipulation | Collections | Misc |
|---|---|---|---|
| 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() |
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:
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
Appends a string to another one.
Syntax: <value>.append(<value>)
Applies to the following types:
select name.append(' ').append(surname) from Employee
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:
select from Users where online.asBoolean() = true
Transforms the field into a Date type.
Syntax: <value>.asDate()
Applies to the following types:
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'
Transforms the field into a Date type but parsing also the time information.
Syntax: <value>.asDateTime()
Applies to the following types:
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'
Transforms the field into an Decimal type. Use Decimal type when treat currencies.
Syntax: <value>.asDecimal()
Applies to the following types:
selct salary.asDecimal() from Employee
Transforms the field into a float type.
Syntax: <value>.asFloat()
Applies to the following types:
selct ray.asFloat() > 3.14
Transforms the field into an integer type.
Syntax: <value>.asInteger()
Applies to the following types:
Converts the first 3 chars of 'value' field in an integer:
selct value.left(3).asInteger() from Log
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:
selct tags.asList() from Friend
Transforms the field into a Long type.
Syntax: <value>.asLong()
Applies to the following types:
selct date.asLong() from Log
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:
selct tags.asMap() from Friend
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:
selct tags.asSet() from Friend
Transforms the field into a string type.
Syntax: <value>.asString()
Applies to the following types:
Get all the salaries with decimals:
select salary.asString().indexof('.') > -1
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:
Get the first character of the users' name:
select from User where name.charAt( 0 ) = 'L'
Convert a value to another type.
Syntax: <value>.convert(<type>)
Applies to the following types:
select dob.convert( 'date' ) from User
Excludes some properties in the resulting document.
Syntax: <value>.exclude(<field-name>[,]*)
Applies to the following types:
select expand( @this.exclude( 'password' ) ) from OUser
Returns the value formatted using the common "printf" syntax. For the complete reference goto Java Formatter JavaDoc.
Syntax: <value>.format(<format>)
Applies to the following types:
Formats salaries as number with 11 digits filling with 0 at left:
select salary.format("%-011d") from Employee
Returns the hash of the field. Supports all the algorithms available in the JVM.
Syntax: <value>.hash([
Applies to the following types:
Get the SHA-512 of the field "password" in the class User:
SELECT password.hash('SHA-512') from User
Include only some properties in the resulting document.
Syntax: <value>.include(<field-name>[,]*)
Applies to the following types:
select expand( @this.include( 'name' ) ) from OUser
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:
Returns all the UK numbers:
select from Contact where phone.indexOf('+44') > -1
Returns the corresponding Java Type.
Syntax: <value>.javaType()
Applies to the following types:
Prints the Java type used to store dates:
select from date.javaType() from Events
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:
select from Actor where 'Luke' IN map.keys()
Returns a substring of the original cutting from the begin and getting 'len' characters.
Syntax: <value>.left(<length>)
Applies to the following types:
select from Actors where name.left( 4 ) = 'Luke'
Returns the length of the string. If the string is null 0 will be returned.
Syntax: <value>.length()
Applies to the following types:
select from Providers where name.length() > 0
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:
select from V where name.normalize() and name.normalize('NFD')
Prefixes a string to another one.
Syntax: <value>.prefix('<string>')
Applies to the following types:
select name.prefix('Mr. ') from Profile
Removes the first occurrence of the passed items.
Syntax: <value>.remove(<item>*)
Applies to the following types:
select out().in().remove( @this ) from V
Removes all the occurrences of the passed items.
Syntax: <value>.removeAll(<item>*)
Applies to the following types:
select out().in().removeAll( @this ) from V
Replace a string with another one.
Syntax: <value>.replace(<to-find>, <to-replace>)
Applies to the following types:
select name.replace('Mr.', 'Ms.') from User
Returns a substring of the original cutting from the end of the string 'lenght' characters.
Syntax: <value>.right(<length>)
Applies to the following types:
Returns all the vertices where the name ends by "ke".
select from V where name.right( 2 ) = 'ke'
Returns the size of the collection.
Syntax: <value>.size()
Applies to the following types:
Returns all the items in a tree with children:
select from TreeItem where children.size() > 0
Returns a substring of the original cutting from 'begin' and getting 'length' characters. 'begin' starts from 0 to string length - 1.
Syntax: <value>.subString(<begin> [,<length>] )
Applies to the following types:
Get all the items where the name begins with an "L":
select name.substring( 0, 1 ) = 'L' from StockItems
Returns the original string removing white spaces from the begin and the end.
Syntax: <value>.trim()
Applies to the following types:
select name.trim() == 'Luke' from Actors
Returns the record in JSON format.
Syntax: <value>.toJSON([<format>])
Where:
Applies to the following types:
Returns the string in lower case.
Syntax: <value>.toLowerCase()
Applies to the following types:
select name.toLowerCase() == 'luke' from Actors
Returns the string in upper case.
Syntax: <value>.toUpperCase()
Applies to the following types:
select name.toUpperCase() == 'LUKE' from Actors
Returns the value's OrientDB Type.
Syntax: <value>.type()
Applies to the following types:
Prints the type used to store dates:
select from date.type() from Events
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:
select from Clients where map.values() CONTAINSALL ( name is not null)