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.

Syntax

SQL

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.
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_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.

SQL

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).

SQL

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.

SQL

SELECT UDF_JSON_TO_ARRAY('["alpha", "beta", "gamma"]') AS RESULT;

+------------------------------------+
| RESULT                             |
+------------------------------------+
| Values: ["alpha", "beta", "gamma"] |
+------------------------------------+

SQL

SELECT UDF_JSON_TO_ARRAY('["alpha",    "beta",  "gamma",   "delta","epsilon"]') AS RESULT;

+-------------------------------------------------------+
| RESULT                                                |
+-------------------------------------------------------+
| Values: ["alpha", "beta", "gamma", "delta", "epsilon"]|
+-------------------------------------------------------+

SQL

SELECT UDF_JSON_TO_ARRAY('[ "alpha", 1, {"beta2" : "gamma3", "delta" : "e4"} ]') AS RESULT;

+-------------------------------------------------------+
| RESULT                                                |
+-------------------------------------------------------+
| Values: ["alpha", 1, {"beta2":"gamma3","delta":"e4"}] |
+-------------------------------------------------------+