JSON_TO_ARRAY

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.

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