Converts a JSON array to a SingleStore DB 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 //

-- Regular string split with delimiter (does not trim)
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 DB 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 JSON array to the SingleStore DB array and displays the result in scalar format.

SELECT udf_json_to_array('[1, 2, 3]') AS RESULT;
****
+-------------------+
| RESULT            |
+-------------------+
| Values: [1, 2, 3] |
+-------------------+
1 row in set (0.27 sec)

SELECT udf_json_to_array('[1,    2,  3,   4,5]') AS RESULT;
****
+-------------------------+
| RESULT                  |
+-------------------------+
| Values: [1, 2, 3, 4, 5] |
+-------------------------+
1 row in set (0.01 sec)

SELECT udf_json_to_array('[ "foo", 1, {"k1" : "v1", "k2" : "v2"} ]') AS RESULT;
****
+-------------------------------------------+
| RESULT                                    |
+-------------------------------------------+
| Values: ["foo", 1, {"k1":"v1","k2":"v2"}] |
+-------------------------------------------+
1 row in set (0.00 sec)

Was this Article Helpful?

Thanks For Your Feedback!