JSON_ TO_ ARRAY
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
On this page
Converts a JSON array to a SingleStore array.
Syntax
JSON_TO_ARRAY (JSON)
Arguments
-
JSON
: A JSON type array.
Return Type
-
An
array(JSON)
.
Remarks
-
Passing a non-array JSON input to the
JSON_
function returns an error.TO_ ARRAY() -
The
JSON_
function returns a non-scalar value (array).TO_ ARRAY() -
For scalar output, create a UDF (user defined function) to convert the output array into a scalar.
-
In versions prior to 8.
5. 22 there is a potential crash issue when using TABLE(JSON_
.TO_ ARRAY(. . . )) See the release notes for more information.
Example
The following example demonstrates the usage of JSON_
function through UDFs.JSON_
function and convert the array output to a scalar value.
DELIMITER //CREATE OR REPLACE FUNCTION array_as_string(a ARRAY(JSON) NULL)RETURNS VARCHAR(255) ASDECLARE result VARCHAR(255);BEGINIF isnull(a) THENresult = "NULL";ELSEresult = "Values: [";FOR i IN 0 .. LENGTH(a) - 1 LOOPIF i < LENGTH(a) - 1 THENresult = CONCAT(result, a[i], ", ");ELSEresult = CONCAT(result, a[i], "");END IF;END LOOP;END IF;RETURN CONCAT(result, "]");END //
A regular string split with delimiter (does not trim).
DELIMITER //CREATE OR REPLACE FUNCTION udf_json_to_array(js JSON NULL)RETURNS VARCHAR(255) ASDECLAREjsonArray array(JSON) NULL;result VARCHAR(255);BEGINjsonArray = JSON_TO_ARRAY(js);result = array_as_string(jsonArray);return result;END //DELIMITER ;
The JSON_
function converts the JSON array to a SingleStore array, and the result is stored in jsonArray
.array_
function then normalizes the result (jsonArray
) from an array to a scalar value.
The following examples convert a JSON array to the SingleStore array and displays the result in scalar format.
SELECT UDF_JSON_TO_ARRAY('["alpha", "beta", "gamma"]') AS RESULT;
+------------------------------------+
| RESULT |
+------------------------------------+
| Values: ["alpha", "beta", "gamma"] |
+------------------------------------+
SELECT UDF_JSON_TO_ARRAY('["alpha", "beta", "gamma", "delta","epsilon"]') AS RESULT;
+-------------------------------------------------------+
| RESULT |
+-------------------------------------------------------+
| Values: ["alpha", "beta", "gamma", "delta", "epsilon"]|
+-------------------------------------------------------+
SELECT UDF_JSON_TO_ARRAY('[ "alpha", 1, {"beta2" : "gamma3", "delta" : "e4"} ]') AS RESULT;
+-------------------------------------------------------+
| RESULT |
+-------------------------------------------------------+
| Values: ["alpha", 1, {"beta2":"gamma3","delta":"e4"}] |
+-------------------------------------------------------+
Last modified: April 17, 2025