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 type. 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.asJSON() 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.

methods are case-insensitive.

Method Reference

[]

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

append()

Appends one or more string to the result.

Syntax: <value>.append(<value>[,<value>]*)

Applies to the following types:

  • string

Examples

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

asBoolean()

Transforms the field into a Boolean type. If the origin type is a string, then "true" (or "TRUE", "True", …​) means TRUE, all other strings transform to FALSE. If it is a number type, then 0 means FALSE while all other numbers transform to TRUE. In case a NULL is passed then NULL is also returned.

Syntax: <value>.asBoolean()

Applies to the following types:

  • string,

  • short,

  • int,

  • long

Examples

SELECT FROM Users WHERE online.asBoolean() = true

asByte()

Transforms the field into a Byte type.

Syntax: <value>.asByte()

Applies to the following types:

  • short,

  • int,

  • long,

  • float,

  • double

asDate()

Transforms the field into a Date type.

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

Where:

  • format, optional, is the format of the date to convert if the value is a string

Applies to the following types:

  • string,

  • long

Examples

Returns all the records where time is before the year 2010:

SELECT FROM Log WHERE time.asDate() < '01-01-2010'

asDateTime()

Transforms the field into a Date type but parsing also the time information.

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

Where:

  • format, optional, is the format of the date to convert if the value is a string

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'

This example returns the dates stored as strings following the ISO 8601 format:

SELECT timeAsString.asDateTime("yyyy-MM-dd'T'HH:mm:ss'Z'") AS time FROM Log

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

asDouble()

Transforms the field into a double type.

Syntax: <value>.asDouble()

Applies to the following types:

  • any

Examples

SELECT ray.asDouble() > 3.14

asFloat()

Transforms the field into a float type.

Syntax: <value>.asFloat()

Applies to the following types:

  • any

Examples

SELECT ray.asFloat() > 3.14

asInteger()

Transforms the field into an integer type.

Syntax: <value>.asInteger()

Applies to the following types:

  • any

Float values are rounded towards zero (truncated).

Examples

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

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

asJSON()

Returns the record(s) in JSON format. If it’s executed on a result set, then the result set is completely browsed and the result set iteration exhausted (if browsed again, it will contain no records). If you need to access to the result set multiple times, transform it into a list with asList() and work with the list instead.

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

Where:

  • format optional, allows custom formatting rules (separate multiple options by comma).

Rules are the following:

  • rid to include records’s RIDs as attribute "@rid"

  • type to include the type name in the attribute "@type"

  • 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 fetching strategy 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 numbers

  • prettyPrint indent the returning JSON in readable (pretty) way.

Applies to the following types:

  • record

Examples

CREATE VERTEX TYPE Test

INSERT INTO Test CONTENT {"attr1": "value 1", "attr2": "value 2"}

SELECT @this.asJSON('rid,version,fetchPlan:in_*:-2 out_*:-2') FROM Test

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

asLong()

Transforms the field into a Long type.

Syntax: <value>.asLong()

Applies to the following types:

  • any

Examples

SELECT date.asLong() FROM Log

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

asRecord()

Transforms the field into the linked record type

Syntax: <value>.asRecord()

Applies to the following types:

  • link

  • string

Examples

Transform link to a record:

SELECT "#1:0".asRecord()

asRID()

Transforms the field into an RID link type.

Syntax: <value>.asRID()

Applies to the following types:

  • string

Examples

Transform string holding an RID to a link:

SELECT "#1:0".asRID()

asSet()

Transforms the value in a Set. If it’s a single item, a new set is created. Sets do not allow duplicates.

Syntax: <value>.asSet()

Applies to the following types:

  • any

Examples

SELECT tags.asSet() FROM Friend

asShort()

Transforms the field into a short type.

Syntax: <value>.asShort()

Applies to the following types:

  • any

Float values are rounded towards zero (truncated).

Examples

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

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

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

capitalize()

Returns a string where each word (group of characters bounded by whitespace) is transformed such that its first character (if it is a letter) is converted to upper case, and the remaining characters (that are letters) are converted to lower case.

Syntax: <value>.capitalize()

Applies to the following types:

  • string

Examples

Return a capitalized string:

SELECT 'hi there'.capitalize()

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'

convert()

Convert a value to another type.

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

Applies to the following types:

  • any

Examples

SELECT dob.convert( 'date' ) FROM User

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 User

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 incoming edges:

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

This function can be used to remove internal properties like @rid, @type, etc.

SELECT @this.exclude('@*') FROM doc

field()

Returns a sub-property from an embedded document property. This method is useful for dynamic property names of types.

Syntax: <property>.field(<string>)

Applies to the following types:

  • embedded document

Examples

Returns a field named test from the embedded document property embdoc:

SELECT embdoc.field('test') FROM doc

format() [Method]

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:

  • any

Examples

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

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

hash()

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

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

Applies to the following types:

  • string

Examples

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

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

ifempty()

Return argument if empty results from value/field/expression, otherwise return result.

Syntax: <value/field/expression>.ifempty(<value>)

Applies to the following types:

  • string

  • list

Examples

SELECT "".ifempty("Empty")

ifnull() [Method]

Return argument if null results from value/field/expression, otherwise return result.

Syntax: <value/field/expression>.ifnull(<value>)

Applies to the following types:

  • any

Examples

SELECT name.ifnull("John Doe") FROM names

include()

Include only some properties in the resulting document.

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

Applies to the following types:

  • document record

Examples

SELECT expand( @this.include('name') ) FROM User

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 amount:

SELECT expand( @this.include('amount*') ) FROM V

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

intersectsWith()

Returns Boolean answering if argument shape intersects with shape instance.

Syntax: <point|circle|rectangle|linestring|polygon>.intersectsWith(<point|circle|rectangle|linestring|polygon>)

Examples

SELECT linestring( [ [10,10], [20,10], [20,20], [10,20], [10,10] ] ).intersectsWith( rectangle(10,10,20,20) ) AS collision

isWithin()

Returns Boolean answering if argument shape is fully inside shape instance.

Syntax: <point|circle|rectangle|linestring|polygon>.isWithin(<point|circle|rectangle|linestring|polygon>)

Examples

SELECT point(11,11).isWithin( circle(10,10,10) ) AS inside

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

join()

Returns all elements of a list converted to a string and concatenated, with strings separated by the argument string that has the default value ','.

Syntax: <value>.join([<separator>])

Applies to the following types:

  • lists

  • sets

Examples

SELECT [1,2,3].join('|')

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

lastIndexOf()

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

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

Applies to the following types:

  • string

SELECT 'Hello Albert'.lastIndexOf('l')

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'

length()

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

Syntax: <value>.length()

Applies to the following types:

  • any

This method first converts its input to a string and then returns the string’s length. For obtaining a collection’s lengths use the size() method.

Examples

SELECT FROM Providers WHERE name.length() > 0

normalize()

Unicode normalization form can be NDF, NFD, NFKC, NFKD, the default is NDF. Pattern-matching, if not defined is the regular expression "\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')

precision()

Adapts the returned date or time format to the argument target precision.

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

The admissible argument values are given here.

Applies to the following types:

  • datetime

  • date

Examples

SELECT sysdate().precision('millisecond')

prefix()

Prefixes a string to another one. A null base value results in a null.

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

Applies to the following types:

  • string

Examples

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

remove()

Removes the first occurrence of the passed items.

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

Applies to the following types:

  • collection

Examples

SELECT [1,2,3,1].remove(1)
SELECT out().in().remove( @this ) FROM V

removeAll()

Removes all the occurrences of the passed items.

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

Applies to the following types:

  • collection

Examples

SELECT [1,2,3,1].removeAll(1)
SELECT out().in().removeAll( @this ) FROM V

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

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'

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

sort()

Returns a sorted copy of the collection. The direction is controlled by a boolean argument, which sets ascending direction for a true value (default), and descending for false.

Only list of a single type can be sorted.
For scalar types this method is an identity.

Syntax: <value>.sort([<dir>])

Applies to the following types:

  • collection

Examples

Sort inline lists:

SELECT [4,1,9,23].sort()
SELECT ["a","Y","b","9"].sort(false)

split()

Returns a list from a string separated by the provided delimiter.

Syntax: <value>.split(<string>)

Applies to the following types:

  • string

Examples

Returns string of comma separated values as list

SELECT 'a,b,c,d,e'.split(',')

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 ArcadeDB

SELECT "ArcadeDB".substring(0,6)

returns Arcade

transform()

Returns the underlying collection to which one or more methods (passed as string arguments) are applied element-wise.

Syntax: <value>.transform(<string>[,<string>]*)

Applies to the following types:

  • collection

The argument methods cannot take arguments themselves.

Examples

SELECT FROM Car WHERE options.transform( 'trim', 'toLowercase' ) CONTAINSALL ['a/c', 'airbags']

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() FROM Actors

trimPrefix()

Returns the original string removing argument from the front, if exists.

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

Applies to the following types:

  • string

Examples

SELECT name.trimPrefix("Mr. ") FROM Actors

trimSuffix()

Returns the original string removing argument from the end, if exists.

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

Applies to the following types:

  • string

Examples

SELECT name.trimSuffix(";") FROM Actors

toLowerCase()

Returns the string in lower case.

Syntax: <value>.toLowerCase()

Applies to the following types:

  • string

Examples

SELECT name.toLowerCase() FROM Actors

toUpperCase()

Returns the string in upper case.

Syntax: <value>.toUpperCase()

Applies to the following types:

  • string

Examples

SELECT name.toUpperCase() FROM Actors

type()

Returns the value’s ArcadeDB type as string. The potential return values are listed as "SQL type" here.

The returned strings are all capitals.

Syntax: <value>.type()

Applies to the following types:

  • any

Examples

Prints the type used to store dates:

SELECT date.type() FROM Events

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)