JSON_TO_ARRAY

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_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 SingleStore 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 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: September 7, 2022

Was this article helpful?