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.

<name>.<method>(…​) is read as a call of the namespaced function <name>.<method> when <name> is one of the function namespaces (ts, geo, text, math, convert, date, util, coll, map, agg, node, rel, path, create), even if <name> is a property of the record. Do not name properties like a namespace when you call methods on them (date.asLong() fails with Unknown function name 'date.asLong'); use another property name, such as created. Enclosing the name in backticks works for some of them (e.g. date), but not for all (map still fails).
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. A null argument is skipped, wherever it appears, and the remaining arguments are still appended: 'x'.append(null, 'A', 'B') returns xAB. A null receiver returns null.

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

A null value, an empty string, or a string of only whitespace returns null.

Applies to the following types:

  • short,

  • int,

  • long,

  • float,

  • double

asCypherRID()

Converts a record or RID into the numeric (Long) form returned by the native OpenCypher id() function, so SQL queries can produce values that round-trip with Cypher. The two components of an ArcadeDB RID (bucketId, position) are packed into a single non-negative number using 63 of the 64 bits (the sign bit is kept clear). The split between bucketId and position is controlled by the global setting arcadedb.opencypher.idBucketBits (default 16, see Settings); the same value must be used to encode and decode, and the method fails with an error if a component does not fit the reserved bits.

Syntax: <value>.asCypherRID()

Applies to the following types:

  • record,

  • RID,

  • string (in the #bucketId:position form),

  • number (returned unchanged)

Examples

SELECT @rid.asCypherRID() AS ident FROM Person WHERE name = 'Alice'

To resolve a record back from a Cypher id, use the inverse cypherRID() function, e.g. SELECT FROM cypherRID(:id).

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

When the string names an instant, because the format reads an offset (XXX, Z) or a zone (VV, z) out of it, the result is the UTC wall clock of that instant, whether or not a format is named: '2020-01-02T03:04:05+02:00'.asDateTime("yyyy-MM-dd’T’HH:mm:ssXXX") is 2020-01-02T01:04:05 (since v26.11.1; before, a named format dropped the offset and it was 03:04:05). asDate() keeps the day written.

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

A null value, an empty string, or a string of only whitespace returns null.

Applies to the following types:

  • any

Examples

SELECT salary.asDecimal() FROM Employee

asDouble()

Transforms the field into a double type.

Syntax: <value>.asDouble()

A null value, an empty string, or a string of only whitespace returns null.

Applies to the following types:

  • any

Examples

SELECT (ray.asDouble() > 3.14) AS bigger FROM Rays

asFloat()

Transforms the field into a float type.

Syntax: <value>.asFloat()

A null value, an empty string, or a string of only whitespace returns null.

Applies to the following types:

  • any

Examples

SELECT (ray.asFloat() > 3.14) AS bigger FROM Rays

asInteger()

Transforms the field into an integer type.

Syntax: <value>.asInteger()

A null value, an empty string, or a string of only whitespace returns null.

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

The method takes no arguments.

Applies to the following types:

  • record

Examples

CREATE VERTEX TYPE Test

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

SELECT @this.asJSON() FROM Test

asList()

Transforms the value in a List. A collection, an array (for example a list parameter) or an iterable becomes a list of its elements. 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()

A null value, an empty string, or a string of only whitespace returns null.

Applies to the following types:

  • any

Examples

SELECT created.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()

A null value, an empty string, or a string of only whitespace returns null.

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.

For numeric vectors (a float[]/double[] field, or an array/list of numbers) it produces the same output as vector.toString(). An optional format argument selects the layout: 'COMPACT' (default), 'PRETTY', 'PYTHON', 'MATLAB' (space-separated row), 'MATLAB_COLUMN' (semicolon-separated column), 'JULIA', or 'NUMPY' (bare comma-separated for numpy.fromstring). Non-vector values are returned in their natural string form. A non-numeric array is rendered as the list of its elements (['a','b']), exactly like a list. The inverse (string to vector) is asVector().

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

Applies to the following types:

  • any

Examples

Get all the salaries with decimals:

SELECT (salary.asString().indexof('.') > -1) AS hasDecimals FROM Employee

Render an embedding vector field in different formats:

SELECT embedding.asString() FROM docs
-- e.g. [0.5, 0.25, 0.75]

SELECT embedding.asString('PYTHON') FROM docs

SELECT embedding.asString('NUMPY') FROM docs
-- e.g. 0.5, 0.25, 0.75   (no brackets, for numpy.fromstring)

asVector()

Parses a value into a vector (float[]). This is the inverse of asString() for vectors and accepts every format asString() / vector.toString() can emit: bracketed or bare, comma-separated (COMPACT/PYTHON/JULIA/NUMPY), space-separated (MATLAB), semicolon-separated (MATLAB_COLUMN) and multi-line (PRETTY). It also accepts an array/list of numbers (normalized to float[]) and a single number (wrapped into a one-element vector).

Syntax: <value>.asVector()

Applies to the following types:

  • string

  • array / list of numbers

  • number

Examples

SELECT '[1.0, 2.0, 3.0]'.asVector()
-- Returns: [1.0, 2.0, 3.0]

SELECT '[1.0 2.0 3.0]'.asVector()
-- Returns: [1.0, 2.0, 3.0]   (MATLAB form)

SELECT '1.0, 2.0, 3.0'.asVector()
-- Returns: [1.0, 2.0, 3.0]   (NUMPY form)

-- Round-trips with asString():
SELECT embedding.asString('MATLAB').asVector() FROM docs

asSparse()

Converts a dense vector value into a sparse vector, dropping elements whose absolute value is at or below an optional threshold (default 0.0, i.e. drops exact zeros). The method form of vector.denseToSparse(), consistent with asString() / asVector().

Syntax: <value>.asSparse([<threshold>])

Applies to the following types:

  • array / list of numbers

Examples

SELECT [0.5, 0.0, 0.3].asSparse()
-- sparse vector with indices [0, 2] and values [0.5, 0.3]

SELECT embedding.asSparse(0.01) FROM docs
-- drops values with |value| <= 0.01

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

Positions and lengths count Unicode characters: a character outside the Basic Multilingual Plane, such as an emoji, is one character and is never cut in two. Before 26.11.1 (issue #10248) the count was in UTF-16 code units, so such a character counted 2.

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

Answers a map with every property of the value except the excluded ones.

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

Applies to the following types:

  • record (document, vertex or edge)

  • map

  • result row

  • collection of the above (each element is projected, and the answer is a list of maps)

The answer is always a map, never a record. It carries no @rid, @type or @cat unless one of the names is a metadata field: exclude('@cat', 'secret') answers @rid, @type (and @in/@out on an edge) together with the properties. Because the answer is a value, UPDATE …​ SET copy = @this.exclude('secret') stores it as an embedded map, and a chained access such as @this.exclude('secret').name reads the map. A graph traversal like .out() needs the record, so call it before projecting: out().exclude('secret'), not exclude('secret').out().

(Since v26.10.2.) In v26.10.1 and earlier the answer was a record copy carrying the source RID (or a map when a metadata name was given), UPDATE …​ SET x = @this.exclude(..) stored a link back to the record, and an edge raised Cannot copy an edge.

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

On a row of a query (a sub-query result, for example) exclude() and include() read the row, not the record behind it: a column the projection overrode answers its projected value, and one it excluded (!@rid included) stays out. Before v26.10.2 they read the record, so (SELECT *, !m FROM D1)[0].include('m') answered the stored m.

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

first()

Returns the first item of a collection (list, set, array, or the values of a map), or null when the collection is empty. For scalar types this method is an identity. This is the method form of the first() SQL function.

Syntax: <value>.first()

Applies to the following types:

  • collection

Examples

SELECT 'a,b,c'.split(',').first()
SELECT tags.first() FROM Post

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

Number formatting (%f, %e, %d, etc.) is always locale-independent (e.g. . as decimal separator), regardless of the server’s configured locale.

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

Answers a map with only the included properties of the value.

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

Applies to the following types:

  • record (document, vertex or edge)

  • map

  • result row

  • collection of the above (each element is projected, and the answer is a list of maps)

As with exclude(), the answer is always a map with no @rid, @type or @cat unless one of the names is a metadata field (include('@rid', 'name')). A name the value does not have answers a null entry. A chained access reads the map, so @this.include('n', 'm').m works, but a graph traversal such as .out() must come before the projection. (Since v26.10.2; v26.10.1 and earlier answered a record copy carrying the source RID.)

Examples

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

You can specify a wildcard as ending character to include 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

A null field name is skipped wherever it appears, as by exclude(): include(null, 'name') is include('name'), and include(null) answers an empty map. Before v26.10.2 a null as the first argument made include() answer null.

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

Positions and lengths count Unicode characters: a character outside the Basic Multilingual Plane, such as an emoji, is one character and is never cut in two. Before 26.11.1 (issue #10248) the count was in UTF-16 code units, so such a character counted 2.

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 created.javaType() FROM Events

join()

Returns all elements of a collection converted to a string and concatenated, with strings separated by the argument string that has the default value ','. A scalar receiver is returned as its string form.

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

Applies to the following types:

  • lists

  • sets

  • arrays (for example a list parameter)

Examples

SELECT [1,2,3].join('|')
SELECT 'a,b,c'.split(',').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 attributes.keys()

last()

Returns the last item of a collection (list, set, array, or the values of a map), or null when the collection is empty. For scalar types this method is an identity. This is the method form of the last() SQL function.

Syntax: <value>.last()

Applies to the following types:

  • collection

Examples

SELECT 'a,b,c'.split(',').last()
SELECT tags.last() FROM Post

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

Positions and lengths count Unicode characters: a character outside the Basic Multilingual Plane, such as an emoji, is one character and is never cut in two. Before 26.11.1 (issue #10248) the count was in UTF-16 code units, so such a character counted 2.

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

Positions and lengths count Unicode characters: a character outside the Basic Multilingual Plane, such as an emoji, is one character and is never cut in two. Before 26.11.1 (issue #10248) the count was in UTF-16 code units, so such a character counted 2.

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, null is returned, as SQL’s LENGTH() does. An empty string returns 0.

Syntax: <value>.length()

Positions and lengths count Unicode characters: a character outside the Basic Multilingual Plane, such as an emoji, is one character and is never cut in two. Before 26.11.1 (issue #10248) the count was in UTF-16 code units, so such a character counted 2.

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

This behavior changed in 26.11.1 (issue #10193). Before, length() on a null value returned 0. A condition such as WHERE name.length() = 0 no longer matches records where name is null: write WHERE name IS NULL OR name.length() = 0 to keep matching them.

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

A null argument is honoured wherever it appears in the list: it removes a null element from the collection, and never discards the other arguments.

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

A null argument is honoured wherever it appears in the list: it removes every null element from the collection, and never discards the other arguments.

replace()

Replace a string with another one. If the value is null, or either argument is null, null is returned, as SQL’s REPLACE() does.

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

Applies to the following types:

  • string

Examples

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

This behavior changed in 26.11.1 (issue #10193). Before, replace() on a null value returned its first argument (<to-find>), so name.replace('Mr.', 'Ms.') answered 'Mr.' for a record without a name.

(Since v26.11.1, issue #10485) A character outside the Basic Multilingual Plane, such as an emoji, is never cut. With an empty <to-find> the replacement goes before every character and after the last, so 'a😀'.replace('', '-') is '-a-😀-', where it used to put a replacement between the two halves of the emoji. An occurrence is never found inside a character either. replace() of the SQL method, the replace() function of openCypher and text.replace() share one implementation.

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

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

Positions and lengths count Unicode characters: a character outside the Basic Multilingual Plane, such as an emoji, is one character and is never cut in two. Before 26.11.1 (issue #10248) the count was in UTF-16 code units, so such a character counted 2.

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, or the number of characters of a string.

Syntax: <value>.size()

Applies to the following types:

  • collection

  • string

The size of a string counts Unicode characters, as length() does: a character outside the Basic Multilingual Plane, such as an emoji, is one character. Before 26.11.1 (issue #10378) it counted UTF-16 code units, so such a character counted 2. The size of NULL is 0, since the method cannot tell a missing collection from a missing string; use length() to get NULL for a string that is NULL.

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 as a list. 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:

  • lists

  • sets

  • arrays (for example a list parameter)

Examples

Sort inline lists:

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

split()

Returns a list from a string separated by the provided delimiter. The delimiter is matched literally, not as a regular expression. The result is a list, so every collection method can be chained on it: 'a,b'.split(',').join('-'), .sort(), .first(), .last(), .size().

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

(Since v26.11.1, issue #10485) The empty pieces are kept, as openCypher split() keeps them: 'a,'.split(',') is ['a', ''] and ','.split(',') is ['', '']. Before, the empty pieces at the end of the list were dropped (['a'] and []). An empty delimiter splits a string into its characters, and a character outside the Basic Multilingual Plane, such as an emoji, is one character: 'a😀'.split('') is ['a', '😀'], where it used to be ['a', '\uD83D', '\uDE00'], the two halves of the emoji. The split() method and function and text.split() of SQL and the split() function of openCypher share one implementation, so they answer alike.

subString()

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

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

Positions and lengths count Unicode characters: a character outside the Basic Multilingual Plane, such as an emoji, is one character and is never cut in two. Before 26.11.1 (issue #10248) the count was in UTF-16 code units, so such a character counted 2.

Applies to the following types:

  • string

Examples

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

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

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

  • query result rows (a sub-query row, a LET variable)

keys() and values() are an index-aligned pair: the n-th value belongs to the n-th key. For a document or a result row the values are those of the properties keys() lists, without the record metadata (@cat, @type, @rid, and @in and @out of an edge). A row that overrides a property of its record answers the row’s value in that position (since v26.10.2; before, a result row also returned the metadata and the record’s value).

Examples

SELECT FROM Clients WHERE attributes.values() CONTAINSALL (@this IS NOT NULL)
A property named like a function namespace (map, geo, math, …​) must be back-ticked to be read as a property: `map`.values(). Unquoted, map.values() calls the map.* function.