Important

New database features are no longer connected to engine versions; features are now enabled independently during scheduled update windows, and “major” and “minor” releases no longer exist in Helios. Please visit the release notes to view the latest features available in your database clusters.

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