Array, Tuple, and Matrix Overview

arrays, tuples, and matrix data types are complex data types in the {{ocient}} system arrays the array data type is a one dimensional list of values with the same data type this type enables the support of a one to many relationship without creating another table you create secondary indexes on arrays, which index the elements of the array for details about secondary indexes, see docid\ ssxi4zc3p mqdtr0b7qdn key features variable size can contain many elements indexing into the array starts at 1 invalid access of the array returns null for semi structured data in json format, you can express the json multi value column in an array data type traditionally in the normalized relational data model, the database represents such data types as a separate table each row stores the value of the primary key of the record in the parent table therefore, the entity relationship between the tables is always one to many, which can result in billions of rows the array data type eliminates the child tables, which helps reduce the storage footprint and avoids the additional join operations examples create a one dimensional integer array \[1, 2, 3] using the sys dummy virtual table select int\[]\(1,2,3) from sys dummy1; output array int(int((1)), int((2)), int((3))) \ \[1,2,3] fetched 1 row create a two dimensional integer array \[\[1, 2, 3], \[4, 5, 6]] select int\[]\[]\(int\[]\(1,2,3),int\[]\(4,5,6)) from sys dummy1; output array array(int)(array int(int((1)), int((2)), int((3))), array int(int((4)), int((5)), int((6)))) \ \[\[1,2,3],\[4,5,6]] fetched 1 row you can get the same result by casting the array and integers explicitly select int\[]\[]\(array\[int(1),int(2),int(3)],array\[int(4),int(5),int(6)]) from sys dummy1; for more examples, see docid 89kk83 gif3icefcy1kuw tuples the tuple data type is a row this type is a fixed sized collection of heterogeneous values tuples support more complex recursive computational functions the memory structure of the tuple is identical to the memory structure of the array type the only difference is that each entry in a tuple can be any type, so the ocient system can simultaneously store both fixed and variable sized values in a tuple examples create a tuple with fixed size types in this case, use three integers select tuple(1,2,3) from sys dummy1; output tuple((1), (2), (3)) \ <<1, 2, 3>> fetched 1 row you can get the same result by using the tuple type constructor select tuple<\<int,int,int>>(1,2,3) from sys dummy1; create a tuple with variable size types in this case, use a one dimensional and two dimensional array select tuple(array\[int(1),int(2),int(3)], int\[]\[]\(array\[int(1),int(2),int(3)],array\[int(4),int(5),int(6)])) from sys dummy1; output tuple(array int(int((1)), int((2)), int((3))), array array(int)(array int(int((1)), int((2)), int((3))), array int(int((4)), int((5)), int((6))))) \ <<\[1,2,3], \[\[1,2,3],\[4,5,6]]>> fetched 1 row create a tuple with different types in this case, an integer, string, and array of integers select tuple<\<int,varchar,int\[]>>(1, 'test string', int\[]\(2,4,6)) from sys dummy1; output tuple(int((1)), ('test string'), array int(int((2)), int((4)), int((6)))) \ <<1, test string, \[2,4,6]>> fetched 1 row for more examples, see docid\ xunz45zvbfsvnbhzy99v5 matrixes a matrix is a fixed size two dimensional array of double values a matrix can be a row vector (1xn matrix) or column vector (nx1 matrix) matrixes support machine learning model calculations for details, see docid\ dqt aansysg4i2sm sixn examples create a 1x4 matrix with numbers 1 through 4 using the make matrix function that expects values in row major order the first two arguments indicate the dimensions of the matrix, and the remaining arguments specify the values for the matrix select make matrix 1x4(1,4,1,2,3,4) from sys dummy1; output make matrix 1x4((1), (4), (1), (2), (3), (4)) \ \[\[1 0,2 0,3 0,4 0]] fetched 1 row create a 2x3 matrix with numbers 1 through 6 select make matrix 2x3(2,3,1,2,3,4,5,6) from sys dummy1; output make matrix 2x3((2), (3), (1), (2), (3), (4), (5), (6)) \ \[\[1 0,2 0,3 0],\[4 0,5 0,6 0]] fetched 1 row create a row vector with four doubles select r{1,2,3,4} from sys dummy1; output r{1,2,3,4} \ \[\[1 0,2 0,3 0,4 0]] fetched 1 row for more examples, see docid\ dwjpaeks9otz2u sav2lj related links docid 89kk83 gif3icefcy1kuw docid\ xunz45zvbfsvnbhzy99v5 docid\ dwjpaeks9otz2u sav2lj docid\ xh 8jfelifhxgjnei ffy