- Categories:
Conversion functions ,Semi-structured and structured data functions (Array/Object)
TO_ARRAY¶
Converts the input expression to anARRAY value.
Syntax¶
TO_ARRAY(<expr>)
Arguments¶
exprAn expression of any data type.
Returns¶
This function returns a value of type ARRAY or NULL:
If the input is an ARRAY, or a VARIANT containing an ARRAY value, the value is returned unchanged.
If
expris a NULL orJSON null value, the function returns NULL.For any other value, the value returned is a single-element array that contains this value.
Usage notes¶
To create an array that contains more than one element, you can useARRAY_CONSTRUCTorSTRTOK_TO_ARRAY.
Examples¶
Create a table, and insert data by calling the TO_ARRAY function:
CREATEORREPLACETABLEarray_demo_2(IDINTEGER,array1ARRAY,array2ARRAY);INSERTINTOarray_demo_2(ID,array1,array2)SELECT1,TO_ARRAY(1),TO_ARRAY(3);SELECT*FROMarray_demo_2;
+----+--------+--------+| ID | ARRAY1 | ARRAY2 ||----+--------+--------|| 1 | [ | [ || | 1 | 3 || | ] | ] |+----+--------+--------+
Execute a query that shows the single-element arrays created during the insert andthe result of calling ARRAY_CAT to concatenate the two arrays:
SELECTarray1,array2,ARRAY_CAT(array1,array2)FROMarray_demo_2;
+--------+--------+---------------------------+| ARRAY1 | ARRAY2 | ARRAY_CAT(ARRAY1, ARRAY2) ||--------+--------+---------------------------|| [ | [ | [ || 1 | 3 | 1, || ] | ] | 3 || | | ] |+--------+--------+---------------------------+
This example demonstrates that TO_ARRAY converts a string input expression to an array with asingle element, even when the input expression includes delimiters (such as commas):
SELECTTO_ARRAY('snowman,snowball,snowcone')ASto_array_result;
+-------------------------------+| TO_ARRAY_RESULT ||-------------------------------|| [ || "snowman,snowball,snowcone" || ] |+-------------------------------+
To convert the same string input expression into an array with multiple elements, you can use theSTRTOK_TO_ARRAY function:
SELECTSTRTOK_TO_ARRAY('snowman,snowball,snowcone',',')ASstrtok_to_array_result;
+------------------------+| STRTOK_TO_ARRAY_RESULT ||------------------------|| [ || "snowman", || "snowball", || "snowcone" || ] |+------------------------+