Functions¶
This page lists commonly used MatrixOne built-in functions by category. Names follow the canonical forms recognized by the engine. Some functions have aliases, such as CEIL/CEILING and LCASE/LOWER. This page is a summary table and does not describe every function’s parameters.
For operators, CAST, and CASE/IF, see Operators.
Aggregate¶
Function |
Purpose |
|---|---|
|
Returns any value from a group |
|
Arithmetic mean |
|
Bitwise aggregation |
|
Bitmap-based deduplication and statistics |
|
Row count |
|
Concatenates strings within a group |
|
Maximum / minimum |
|
Median |
|
Population standard deviation |
|
Sum |
|
Population variance |
Date and time¶
Function |
Purpose |
|---|---|
|
Current date or time |
|
Extracts or normalizes date-time values |
|
Adds or subtracts time intervals |
|
Date or time difference |
|
Formatting and parsing |
|
Extracts components such as year, month, or day |
|
Component values |
|
Day of year, week, and weekday index |
|
Converts to or from a Unix timestamp |
|
Days or seconds relative to an epoch |
|
Time-zone conversion |
Mathematical¶
Function |
Purpose |
|---|---|
|
Absolute value |
|
Rounding |
|
Exponents and logarithms |
|
Pi |
|
Random number in (0,1) |
|
Trigonometric and hyperbolic functions |
Actual support depends on the instance. The table covers the common set found in the documentation.
String¶
Function |
Purpose |
|---|---|
|
Concatenation |
|
Length in bytes, characters, or bits |
|
Extraction and delimiter-based splitting |
|
Case conversion |
|
Whitespace and padding |
|
Position search |
|
Prefix, suffix, and empty-string tests |
|
Reverses a string |
|
Base and hexadecimal conversion |
|
Base64 conversion |
|
Digests |
|
Numeric formatting such as thousands separators |
Regular expressions¶
Function |
Purpose |
|---|---|
|
Tests whether a value matches |
|
Starting position of a match |
|
Extracts a matching substring |
|
Replaces matching content |
Vector¶
Used with the VECTOR type:
Function / capability |
Purpose |
|---|---|
Vector arithmetic |
Element-wise operations |
|
Negative dot product (see sign and ordering below) |
|
Distance and similarity |
|
Norms and normalization |
|
Dimensions |
|
Subvector |
|
Cluster centers |
|
Element-wise or aggregate helpers |
INNER_PRODUCT sign and ordering¶
The current MOI backend returns the negative dot product for INNER_PRODUCT(a, b): -(a · b). The mathematical dot product of [1,2,3] and [4,5,6] is 32, while the function returns -32. For [1,2,3] against itself, it returns -14.
Run this read-only query on the target instance to confirm the sign for its backend version:
SELECT INNER_PRODUCT('[1,2,3]', '[4,5,6]') AS negative_dot_product,
INNER_PRODUCT('[1,2,3]', '[1,2,3]') AS self_negative_dot_product;
Under the negative-dot-product convention, the results are -32 and -14. For similarity retrieval, use ORDER BY INNER_PRODUCT(vector_column, query_vector) ASC: a larger mathematical dot product produces a smaller negative dot product. Use -INNER_PRODUCT(a, b) when you need the mathematical dot product.
Run the constant query again after changing backend versions before choosing the sort direction. If it returns positive values, that instance uses a different sign convention and the ASC ordering above does not apply directly.
JSON¶
Function |
Purpose |
|---|---|
|
Extracts a value by path |
|
Extracts as a string / floating-point value |
|
Adds / removes quotes |
|
Converts a row to a JSON array |
|
Transforms with a jq expression; the latter tolerates errors |
Table functions¶
Function |
Purpose |
|---|---|
|
Generates a series of rows |
|
Expands a JSON array into a table |
Window¶
Function |
Purpose |
|---|---|
|
Consecutive row number |
|
Rank with gaps after ties |
|
Rank without gaps after ties |
System and session¶
Function |
Purpose |
|---|---|
|
Current user |
|
Current role |
|
Version information, if exposed |
|
Purges log data from system tables; requires elevated privileges |
Other¶
Function |
Purpose |
|---|---|
|
Generates a UUID |
|
Pauses for the specified number of seconds |
|
Samples data to reduce the scan range |
|
Reads or writes the file content referenced by a DATALINK |
|
Lists directories and files in a Stage |
|
Extracts an element from a sequence or tuple |
|
Most recent insert ID / query ID |