# JSON\_TO\_ARRAY

Converts a JSON array to a SingleStore 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)](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-function-udf.md) 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](https://docs.singlestore.com/db/v9.1/release-notes/singlestore-memsql/8-5-release-notes/maintenance-release-changelog-v-8-5.md) 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;


```

```output

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

```

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


```

```output

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

```

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


```

```output

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

```

***

Modified at: April 17, 2025

Source: [/db/v9.1/reference/sql-reference/json-functions/json-to-array/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-to-array/)

(An index of the documentation is available at /llms.txt)