SingleStore DB

JSON_ARRAY_UNPACK

Converts an encoded blob representing a vector to a JSON array representing the same vector.

Syntax
JSON_ARRAY_UNPACK(vector_expression)
Arguments
  • vector_expression: An expression that evaluates to a vector. The vector must be encoded as a blob containing packed single-precision or double-precision floating-point numbers in little-endian byte order.

Return Type

A JSON array of zero or more floating point numbers.

Remarks

A vector can be of any length, but blob lengths must be divisible by 4 bytes (or 8 bytes, depending on the datatype specified).

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

The following examples use the t table.

CREATE TABLE t (x BLOB, y BLOB);

INSERT INTO t VALUES (JSON_ARRAY_PACK('[12.1, 6.4]'), JSON_ARRAY_PACK('[8.6,9.2]'));
INSERT INTO t VALUES (JSON_ARRAY_PACK('[5.2, 11.7]'), JSON_ARRAY_PACK('[4.6,7.3]'));
Querying Rows Using JSON_ARRAY_UNPACK

The following example queries the t table using the JSON_ARRAY_UNPACK() function:

SELECT JSON_ARRAY_UNPACK(t.x) "x", JSON_ARRAY_UNPACK(t.y) "y" from t;
****
+-------------------------+-------------------------+
| x                       | y                       |
+-------------------------+-------------------------+
| [12.1000004,6.4000001]  | [8.60000038,9.19999981] |
| [5.19999981,11.6999998] | [4.5999999,7.30000019]  |
+-------------------------+-------------------------+
Typecasting the Datatype using Suffixes

The values inserted in the t table are 32-bit floating point numbers, because no suffix was specified with the JSON_ARRAY_PACK() function. The following query returns the vector blobs as 32-bit integers.

SELECT JSON_ARRAY_UNPACK_I32(t.x) "x", JSON_ARRAY_UNPACK_I32(t.y) "y" from t;
****
+-------------------------+-------------------------+
| x                       | y                       |
+-------------------------+-------------------------+
| [1094818202,1087163597] | [1091148186,1091777331] |
| [1084647014,1094398771] | [1083388723,1089051034] |
+-------------------------+-------------------------+