SingleStore DB

JSON_ARRAY_PACK

Converts a JSON array of zero or more floating point numbers to an encoded blob.

Syntax
JSON_ARRAY_PACK('[float [, ...]]')
Arguments

A JSON array.

Return Type

A blob containing packed single-precision floating-point numbers in little-endian byte order.

Remarks

JSON_ARRAY_PACK() can be used with other vector built-in functions, namely DOT_PRODUCT(), VECTOR_ADD(), VECTOR_MUL(),VECTOR_SUB(), and EUCLIDEAN_DISTANCE(). These functions require two input vectors that are encoded as blobs containing packed single-precision or double-precision floating-point numbers in little-endian byte order. The vector returned by JSON_ARRAY_PACK() is appropriately formatted for use as an input parameter to these functions.

You can specify the datatype of the vector elements in which this operation is performed on the vector by adding a suffix to the function. Omitting the suffix from the function is equivalent to suffixing it with _F32. All operations are done using the specified datatype. The following table lists the suffixes and their datatype.

Suffix

Datatype

_I8

8-bit signed integer

_I16

16-bit signed integer

_I32

32-bit signed integer

_I64

64-bit signed integer

_F32

32-bit floating-point number (IEEE standard format)

_F64

64-bit floating-point number (IEEE standard format)

Examples

Example: Inserting Data Using JSON_ARRAY_PACK()

The following example inserts data into a table with a column of the BLOB data type. In this example, the HEX() built-in function is also used to return a readable form of the binary output:

INSERT INTO t VALUES (JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'));

SELECT HEX(b) FROM t;
****
+--------------------------+
| HEX(b)                   |
+--------------------------+
| 0000803F0000003F00000040 |
+--------------------------+

You can also use the suffix to specify the datatype as follows:

INSERT INTO t VALUES (JSON_ARRAY_PACK_I16('[1.0, 0.5, 2.0]'));

SELECT HEX(b) FROM t;
****
+--------------+
| HEX(a)       |
+--------------+
| 010001000200 |
+--------------+

Example: Using JSON_ARRAY_PACK() with DOT_PRODUCT()

The following example uses JSON_ARRAY_PACK() for input parameters to the DOT_PRODUCT() built-in function:

SELECT DOT_PRODUCT(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'));
****
+-------------------------------------------------------------------------------------+
| DOT_PRODUCT(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[1.0, 0.5, 2.0]')) |
+-------------------------------------------------------------------------------------+
|                                                                                5.25 |
+-------------------------------------------------------------------------------------+

Example: Using JSON_ARRAY_PACK() with VECTOR_SUB()

The following example uses JSON_ARRAY_PACK() for input parameters to the VECTOR_SUB() built-in function. The HEX() built-in function is also used to return a readable form of the binary output:

SELECT HEX(VECTOR_SUB(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[0.7, 0.2, 1.7]')));
****
+-----------------------------------------------------------------------------------------+
| HEX(VECTOR_SUB(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[0.7, 0.2, 1.7]'))) |
+-----------------------------------------------------------------------------------------+
| 9A99993E9A99993E9899993E                                                                |
+-----------------------------------------------------------------------------------------+

Example: Using JSON_ARRAY_PACK() with EUCLIDEAN_DISTANCE()

The following example uses JSON_ARRAY_PACK() for input parameters to the EUCLIDEAN_DISTANCE() built-in function:

SELECT EUCLIDEAN_DISTANCE(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[0.7, 0.2, 1.7]'));
****
+--------------------------------------------------------------------------------------------+
| EUCLIDEAN_DISTANCE(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[0.7, 0.2, 1.7]')) |
+--------------------------------------------------------------------------------------------+
|                                                                         0.5196152239171921 |
+--------------------------------------------------------------------------------------------+