Example 1 - Sum Vectors with Element Type F32

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 query sums the vectors in the vec column.

SET vector_type_project_format = JSON; /* to make vector output readable */

SELECT VECTOR_SUM(vec)
FROM vectors;

+---------------------------------------------------+
| VECTOR_SUM(vec)                                   |
+---------------------------------------------------+
| [0.550000012,1.62000012,1.40200007,2.0150001]     |
+---------------------------------------------------+

The results of the SELECT do not exactly match the values in the INSERT due to the finite precision in floating-point arithmetic. The elements in the VECTOR data type are stored as floating-point numbers and are not perfectly representable in floating-point representation, thus the results are very close to, but not exactly the sum of the values in the INSERT statement.

Example 2 - Sum Vectors with Element Type I16

This example shows how to use VECTOR_SUM with a column of type VECTOR where the element type is 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 sums the vectors in the vec column.

SET vector_type_project_format = JSON; /* to make vector output readable */

SELECT VECTOR_SUM(vec)
FROM vectors_i16;

+-----------------------+
| VECTOR_SUM(vec)       |
+-----------------------+
| [6,11,17]             |
+-----------------------+

Example 1 - Adding Two Vectors

Create a table with a column of type BLOB to store the vectors and insert data into that table. The second column in this table, with column name vec and type BLOB, will store the 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 query sums the vectors in the vec column and uses JSON_ARRAY_UNPACK to see the results in JSON format.

SELECT JSON_ARRAY_UNPACK(VECTOR_SUM(vec)) AS vector_sum
FROM vectors_b;

+---------------------------------------------------+
| vector_sum                                        |
+---------------------------------------------------+
| [0.550000012,1.35000002,0.694999993,1.05500007]   |
+---------------------------------------------------+
1 row in set (0.01 sec)

The following query uses HEX to view the results in hexadecimal format.

SELECT HEX(VECTOR_SUM(vec)) AS vector_sum
FROM vectors_b;

+---------------------------------------------------+
| vector_sum                                        |
+---------------------------------------------------+
| CDCC0C3FCDCCAC3F85EB313F3E0A873F                  |
+---------------------------------------------------+

Example 2 - BLOBs with 16-bit Integers

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

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 query sums the vectors in the vectors_b_i table.

SELECT JSON_ARRAY_UNPACK_I16(VECTOR_SUM_I16(vec)) AS vector_sum
FROM vectors_b_i;

+--------------+
| vector_sum   |
+--------------+
| [24,7,3,13]  |
+--------------+

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 .