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"}] | +-------------------------------------------------------+