JSON_TO_ARRAY
Converts a JSON array to a SingleStoreDB 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_TO_ARRAY()function returns an error.The
JSON_TO_ARRAY()function returns a non-scalar value (array).For scalar output, create a UDF (user defined function) to convert the output array into a scalar.
Example
The following example demonstrates the usage of JSON_TO_ARRAY() function through UDFs. The UDFs capture the result of the JSON_TO_ARRAY() 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) AS
DECLARE result VARCHAR(255);
BEGIN
IF isnull(a) THEN
result = "NULL";
ELSE
result = "Values: [";
FOR i IN 0 .. LENGTH(a) - 1 LOOP
IF i < LENGTH(a) - 1 THEN
result = CONCAT(result, a[i], ", ");
ELSE
result = 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) AS
DECLARE
jsonArray array(JSON) NULL;
result VARCHAR(255);
BEGIN
jsonArray = JSON_TO_ARRAY(js);
result = array_as_string(jsonArray);
return result;
END //
DELIMITER ;The JSON_TO_ARRAY function converts the JSON array to a SingleStoreDB array, and the result is stored in jsonArray. The array_as_string() function then normalizes the result (jsonArray) from an array to a scalar value.
The following examples convert a JSON array to the SingleStoreDB 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"}] |
+-------------------------------------------------------+