JSON_ARRAY_PACK

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

It is a scalar function.

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.

Warning

Beginning with 8.5 the blob data type will be replaced with the vector data type. Blob data types will be deprecated for vector databases.

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 data type 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 data type. The following table lists the suffixes and their data type.

Suffix

Data Type

_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:

CREATE TABLE jp_t (b blob);
INSERT INTO jp_t VALUES (JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'));
SELECT HEX(b) FROM jp_t;
+--------------------------+
| HEX(b)                   |
+--------------------------+
| 0000803F0000003F00000040 |
+--------------------------+

You can also use the suffix to specify the data type as follows:

INSERT INTO jp_t VALUES (JSON_ARRAY_PACK_I16('[1.0, 0.5, 2.0]'));
SELECT HEX(b) FROM jp_t;
+--------------+
| HEX(b)       |
+--------------+
| 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 |
+--------------------------------------------------------------------------------------------+

Last modified: March 8, 2024

Was this article helpful?