VECTOR_KTH_ELEMENT

Example 1 - Vector with F32 Element Type

First create a table of vectors of length 4 using the VECTOR data type and insert data into that table.

CREATE TABLE vectors (id int, vec VECTOR(4) not null);

INSERT INTO vectors VALUES (1, '[0.45, 0.55, 0.495, 0.5]');
INSERT INTO vectors VALUES (2, '[0.1, 0.8, 0.2, 0.555]');
INSERT INTO vectors VALUES (3, '[-0.5, -0.03, -0.1, 0.86]');
INSERT INTO vectors VALUES (4, '[0.5, 0.3, 0.807, 0.1]');

The following SQL extracts the 2nd element of each of those vectors. k is 0-indexed, so we use the value '1' to extract the 2nd element.

SELECT id, VECTOR_KTH_ELEMENT(vec, 1)
FROM vectors
ORDER BY id;

+------+----------------------------+
| id   | VECTOR_KTH_ELEMENT(vec, 1) |
+------+----------------------------+
|    1 |          0.550000011920929 |
|    2 |          0.800000011920929 |
|    3 |      -0.029999999329447746 |
|    4 |        0.30000001192092896 |
+------+----------------------------+

Notice that the results do not exactly match the values in the INSERT. This is because elements in the VECTOR data type are stored as floating-point numbers and the values in the INSERT statement are not perfectly representable in floating-point.

Example 2 - Vector with I16 Element Type

This example shows how to use VECTOR_KTH_ELEMENT with a VECTOR with an elementType of 16-bit integer (I16).

CREATE TABLE vectors_i16(id INT, vec VECTOR(3, I16));

INSERT INTO vectors_i16 VALUES(1, '[1, 2, 3]');
INSERT INTO vectors_i16 VALUES(2, '[4, 5, 6]');
INSERT INTO vectors_i16 VALUES(3, '[1, 4, 8]');

The following SQL extracts the 2nd element of each of those vectors.

SELECT id, VECTOR_KTH_ELEMENT(vec, 1)
FROM vectors_i16
ORDER BY id;

+------+----------------------------+
| id   | VECTOR_KTH_ELEMENT(vec, 1) |
+------+----------------------------+
|    1 |                          2 |
|    2 |                          5 |
|    3 |                          4 |
+------+----------------------------+

Example 3 - Using a Negative Value for k

Negative numbers can be used for k to count from the end of the vectors. As shown below, using k = -1 will extract the last element of the vectors.

SELECT id, VECTOR_KTH_ELEMENT(vec, -1)
FROM vectors_i16
ORDER BY id;

+------+-----------------------------+
| id   | VECTOR_KTH_ELEMENT(vec, -1) |
+------+-----------------------------+
|    1 |                           3 |
|    2 |                           6 |
|    3 |                           8 |
+------+-----------------------------+

Example 1 - BLOB Argument

Create a table with a column of type BLOB to store the vectors. The second column in this table, with column name vec and type BLOB, will store the vectors. This example demonstrates storing vector data using BLOBs, hence the column of type BLOB named vec.

Then use the JSON_ARRAY_PACK built-in function to easily insert properly formatted vectors.

CREATE TABLE vectors_b (id int, vec BLOB not null);

INSERT INTO vectors_b VALUES (1, JSON_ARRAY_PACK('[0.1, 0.8, 0.2, 0.555]')); 
INSERT INTO vectors_b VALUES (2, JSON_ARRAY_PACK('[0.45, 0.55, 0.495, 0.5]'));

The following SQL extracts the 2nd element of each of those vectors.

SELECT id, VECTOR_KTH_ELEMENT(vec, 1)
FROM vectors_b
ORDER BY id;

+------+----------------------------+
| id   | VECTOR_KTH_ELEMENT(vec, 1) |
+------+----------------------------+
|    1 |          0.800000011920929 |
|    2 |          0.550000011920929 |
+------+----------------------------+

The return type in this case will be a 32-bit floating-point number as that is the default type for vector element storage.

Example 2 - BLOBs with 16-bit Integers

Below is an example of using JSON_ARRAY_PACK and VECTOR_KTH_ELEMENT with 16-bit signed integers.

First create a table of vectors stored as 16-bit integers. Note the use of the _I16 suffix on JSON_ARRAY_PACK.

CREATE TABLE vectors_b_i (id int, vec BLOB not null);

INSERT INTO vectors_b_i VALUES (1, JSON_ARRAY_PACK_I16('[1, 3, 2, 5]')); 
INSERT INTO vectors_b_i VALUES(2, JSON_ARRAY_PACK_I16('[23, 4, 1, 8]'));

The following SQL extracts the 2nd element of each of those vectors.

SELECT id, VECTOR_KTH_ELEMENT_I16(vec, 1)
FROM vectors_b_i
ORDER BY id;

+------+--------------------------------+
| id   | VECTOR_KTH_ELEMENT_I16(vec, 1) |
+------+--------------------------------+
|    1 |                              3 |
|    2 |                              4 |
+------+--------------------------------+

Formatting Binary Vector Data for BLOBs

When using the BLOB type for vector operations, vector data can be formatted using JSON_ARRAY_PACK. If your vector data is already in a packed binary format, you can load that data into the BLOBs. The data must be encoded as a BLOB containing packed numbers in little-endian byte order. Vectors stored as BLOBs can be of any length; however, the input blob length must be divisible by the size of the packed vector elements (1, 2, 4 , or 8 bytes, depending on the vector element).