Matrix Functions and Operators - Ocient Documentation

In , a matrix is a fixed‑size, multi‑dimensional array of DOUBLE values. Many models and feature representations are naturally expressed as matrices, making them a core building block for in‑database machine learning. Matrices help you:

Store dense numeric features — Keep related numeric inputs together as a single matrix value instead of many scalar columns. This storage simplifies machine learning training and scoring queries.
Represent model internals and transformations — Learned weights, intermediate layers, and linear transformations are often matrix‑shaped. Keeping these as matrices in SQL lets more of the machine learning pipeline execute directly in Ocient.
Apply math and linear‑algebra style operations in SQL — Matrix functions and operators let you construct, inspect, and manipulate matrix values in the same queries that drive machine learning models and preprocessing steps.

For an overview of Ocient machine-learning capabilities, see Machine Learning in Ocient.

Matrix Usage

To define a matrix data type in SQL, use the syntax MATRIX[rows][columns], where the bracketed numbers specify the number of matrix rows and columns. Example This example creates a basic table matrix_example with a matrix column col_matrix that contains matrices with two rows and three columns.

SQL

CREATE TABLE matrix_example (
    id INT,
    col_matrix MATRIX[2][3] NOT NULL
);

Insert data for two matrices into the matrix column.

SQL

INSERT INTO matrix_example (id, col_matrix) VALUES
    (1, {{1, 2, 3}, {4, 5, 6}}),
    (2, {{0, 0, 0}, {7, 8, 9}});

For more DDL examples that use matrices, see CREATE TABLE SQL Statement Examples. For details about working with matrix values, see Data Types.

Matrix Functions

In the examples, _R{} and _C{} are vector notation, meaning _R{ 1,2,3 } is a row vector with values 1, 2, and 3.

Function	Syntax	Purpose	Example	Result
Constructor	make_matrix_ixj(i, j, e_00, …, e_ij)	Creates an `ixj` matrix with elements `e_00`, `…`, `e_ij`	make_matrix_1x3(1,3,1,2,3)	{ {1.0,2.0,3.0} }
Constructor (2)	matrix_from_text(string)	Creates a matrix from the specified string	matrix_from_text(’{ {1,2,3}, {1,2,3} }’)	{ {1.0,2.0,3.0}, {1.0,2.0,3.0} }
+ (matrix addition)	matrix + matrix	Performs matrix addition	{ {1,2,3}, {4,5,6} } + { {4,5,6}, {1,2,3} }	{ {5.0,7.0,9.0}, {5.0,7.0,9.0} }
- (matrix subtraction)	matrix - matrix	Performs matrix subtraction	{ {1,2,3}, {4,5,6} } - { {4,5,6}, {1,2,3} }	{ {-3.0,-3.0,-3.0}, {3.0,3.0,3.0} }
* (scalar multiplication)	numeric * matrix	Performs scalar multiplication	2 * { {4,5,6}, {1,2,3} }	{ {2.0,4.0,6.0}, {8.0,10.0,12.0} }
* (matrix multiplication)	matrix * matrix	Performs matrix multiplication	{ {1,2,3}, {4,5,6} } * { {1,2}, {3,4}, {5,6} }	{ {22.0,28.0}, {49.0,64.0} }
/ (scalar division)	matrix / numeric	Performs scalar division	{ {1,2,3}, {4,5,6} } / 2	{ {0.5,1.0,1.5}, {2.0,2.5,3.0} }
transpose	transpose(matrix)	Returns the transpose of the matrix	transpose( { {1,2,3}, {4,5,6} } )	{ {1.0,4.0}, {2.0,5.0}, {3.0,6.0} }
determinant	det(matrix)	Returns the determinant of the matrix as a double	det( { {1,2}, {3,4} } )	-2.0
inverse	inverse(matrix)	Returns the inverse of a square, invertible matrix	inverse( { {1,2}, {3,4} } )	{ {-2.0, 1.0}, {1.5, -0.5} }
trace	matrix_trace(matrix)	Returns the trace of a square matrix as a double	matrix_trace( { {1,2,3}, {4,5,6}, {7,8,9} } )	15.0
vector sum	vector_sum(matrix)	Returns the sum of elements in a one-dimensional matrix/vector	vector_sum( _R ) vector_sum( _C )	6.0
vector min	vector_min(matrix)	Returns the minimum of elements in a one-dimensional matrix/vector	vector_min( _R ) vector_min( _C )	1.0
vector max	vector_max(matrix)	Returns the maximum of elements in a one-dimensional matrix/vector	vector_max( _R ) vector_max( _C )	3.0
vector argmin	vector_argmin(matrix)	Returns the 1-based position (index) of the minimum element in a one-dimensional matrix or vector (row or column vector). The first element has index 1.	vector_argmin(_R)	2
vector argmax	vector_argmax(matrix)	Returns the 1-based position (index) of the maximum element of a one-dimensional matrix or vector. The first element has index 1.	vector_argmax(_C)	3
softmax	softmax(matrix)	Returns the softmax of a one-dimensional matrix or vector.	softmax(_R)	{}
cross entropy loss	cross_entropy_loss(matrix, matrix)	Returns the cross entropy loss of two one-dimensional matrices or vectors.	cross_entropy_loss( _R, _C ) cross_entropy_loss( _C, _R ) cross_entropy_loss( _R, _R ) cross_entropy_loss( _C, _C )	0.69
log loss	log_loss(matrix, matrix)	Returns the log loss of two one-dimensional matrices or vectors.	log_loss( _R, _C ) log_loss( _C, _R ) log_loss( _R, _R ) log_loss( _C, _C )	2.53
logits loss	logits_loss(matrix, matrix)	Returns the logits loss of two one-dimensional matrices or vectors.	logits_loss( _R, _C ) logits_loss( _C, _R ) logits_loss( _R, _R ) logits_loss( _C, _C )	2.44
hinge loss	hinge_loss(matrix, matrix)	Returns the hinge loss of two one-dimensional matrices or vectors.	hinge_loss( _R, _C ) hinge_loss( _C, _R ) hinge_loss( _R, _R ) hinge_loss( _C, _C )	3.5
dot product	dot(matrix, matrix)	Returns the dot product of two one-dimensional matrices/vectors	dot( _R, _C ) dot( _C, _R ) dot( _R, _R ) dot( _C, _C )	14.0
magnitude	abs(matrix)	Returns the magnitude of one-dimensional matrix/vector	abs( _R ) abs( _C )	3.74
LUPQ decomp	LUPQ_decomp(matrix)	Returns LUPQ decomposition of a square matrix A as a tuple of 4 matrices where PAQ = LU
LUP decomp	LUP_decomp(matrix)	Returns LUP decomposition of a square matrix as a tuple of 3 matrices
QR decomp	qrdecomp(matrix)	Returns QR decomposition of a matrix as a tuple of 2 matrices
SVD decomp	SVD_decomp(matrix)	Returns SVD decomposition of a matrix as a tuple of 3 matrices
eigenvalues/vectors	eigen(matrix)	Returns eigenvalues and eigenvalues of a square matrix as a vector of pairs
identity matrix	identity_matrix(unsigned int)	Returns an identity matrix of the specified dimension	identity_matrix(3)	{ {1.0,0.0,0.0}, {0.0,1.0,0.0}, {0.0,0.0,1.0} }
NULL matrix	null_matrix(unsigned int, unsigned int)	Returns a NULL matrix of the specified (row, col) dimensions	null_matrix(2, 3)	NULL
zero matrix	zero_matrix(unsigned int, unsigned int)	Returns a zero matrix of the specified (row, col) dimensions	zero_matrix(2, 3)	{ {0.0,0.0,0.0}, {0.0,0.0,0.0} }
matrix dimension	matrix_dim(matrix)	Returns the dimensions of the specified matrix as a tuple of (row, col) integers. When the input matrix is NULL, the function returns NULL.	matrix_dim(_R{1, 2, 3})	TUPLE<INT,INT>(1, 3)
Frobenius norm	frobenius(matrix)	Returns the Frobenius norm of a matrix. This norm is the matrix norm of an M`` x ``N matrix defined as the square root of the sum of the absolute squares of its elements. \|a\|^2 is the same as a^2, where a is a real number.	frobenius(matrix_from_text(‘{{3,4},{8,6}}‘))	11.180339887498949

Matrix Operators

Operator	Syntax	Purpose
<	matrix < matrix	Less than comparison
>	matrix > matrix	Greater than comparison
<=	matrix <= matrix	Less than or equal to comparison
>=	matrix >= matrix	Greater than or equal to comparison
=	matrix = matrix	Equal to comparison
<>	matrix <> matrix	Not equal to comparison
[,]	matrix[rowIndex,colIndex]	Access the matrix at the specified row and column. 1-Indexed. Accessing a NULL matrix or using a NULL index returns NULL. Access out of bounds returns a NULL.
[:,:]	matrix[r1:c1,r2:c2]	Slice the matrix at the specified row range and column range. * The index starts at `1`. * Slicing a NULL matrix or using a NULL index returns NULL. * Start indices omitted or below `1` become `1`. End indices omitted or above the number of rows/columns become the number of rows/columns. Example — `matrix_10X10[:10,-1:20]` is equivalent to `matrix[1:10,1:10]`. * Ranges completely out of bounds throw an `INVALID_ARGUMENT` error. * Sequential slices like `matrix[1:1,1:1][1:1,1:1]` throw an error. * Slices cannot be combined with access. `matrix[1,1:10]` is not allowed, but `matrix_10X10[1:1,1:10]` is. * Trying to slice with non-constant indexes throws an error.

Operator Examples

Operator	SQL Predicate	Result
`<`	`{ {1,2,3}, {4,5,6} } < { {4,5,6}, {1,2,3} }`	true
	`{ {1,2,3}, {4,5,6} } < { {1,2,2}, {1,2,3} }`	false
	`{ {1,2,3}, {4,5,6} } < { {1,2,3}, {4,5,6} }`	false
`>`	`{ {4,5,6}, {4,5,6} } > { {1,2,3}, {7,8,9} }`	true
	`{ {1,2,3}, {4,5,6} } > { {4,5,6}, {1,2,3} }`	false
	`{ {1,2,3}, {4,5,6} } > { {1,2,3}, {4,5,6} }`	false
`<=`	`{ {1,2,3}, {4,5,6} } <= { {4,5,6}, {1,2,3} }`	true
	`{ {1,2,3}, {4,5,6} } <= { {1,2,2}, {1,2,3} }`	false
	`{ {1,2,3}, {4,5,6} } <= { {1,2,3}, {4,5,6} }`	true
`>=`	`{ {4,5,6}, {4,5,6} } >= { {1,2,3}, {7,8,9} }`	true
	`{ {1,2,3}, {4,5,6} } >= { {1,5,6}, {1,2,3} }`	false
	`{ {1,2,3}, {4,5,6} } >= { {1,2,3}, {4,5,6} }`	true
`=`	`{ {1,2,3}, {4,5,6} } = { {1,2,3}, {4,5,6} }`	true
	`{ {1,2,3}, {4,5,6} } = { {1,2,4}, {4,5,6} }`	false
`<>`	`{ {1,2,3}, {4,5,6} } <> { {1,2,3}, {4,5,6} }`	false
	`{ {1,2,3}, {4,5,6} } <> { {1,2,4}, {4,5,6} }`	true
`matrix_access_operator`	`{ {1,2,3}, {4,5,6} }[1,2]`	2.0
	`{ {1,2,3}, {4,5,6} }[NULL,2]`	NULL
`matrix_slice_operator`	`{ {1,2,3}, {4,5,6} }[1:,2:3]`	{ {2.0,3.0}, {5.0,6.0} }
	`{ {1,2,3}, {4,5,6} }[1:NULL,2:3]`	NULL

Bibliography

Golub, Gene H., and Charles F. Van Loan. Matrix Computations. 3rd ed, Johns Hopkins University Press, 1996. Array Functions and Operators Math Functions and Operators Tuple Functions and Operators Data Types Data Types for Data Pipelines Query Ocient

​Matrix Usage

​Matrix Functions

​Matrix Operators

​Operator Examples

​Bibliography

​Related Links

Matrix Usage

Matrix Functions

Matrix Operators

Operator Examples

Bibliography

Related Links