@Rocky Is there any way to convert

array<bigint(20)>

to JSON?

CAST(... AS JSON)

errors as an invalid type cast

To convert an

ARRAY<BIGINT>

to a JSON value in StarRocks, you have two primary options depending on your version. 1. Using

to_json()

(Recommended for v3.3.4+ and v3.4+) In recent versions of StarRocks, the

to_json

function has been updated to support

ARRAY

types. This is the cleanest way to perform the conversion.

sql
SELECT to_json(array_column) FROM your_table;

2. The String-Cast Workaround (For older versions) If your version does not yet support

to_json

for arrays, you can use a two-step approach: first cast the array to a

STRING

(which results in a format like

[1, 2, 3]

), then use

parse_json()

to turn that string into a JSON object.

sql
SELECT parse_json(CAST(array_column AS STRING)) FROM your_table;

Why

CAST(... AS JSON)

fails StarRocks does not currently support a direct

CAST

from the

ARRAY

data type to the

JSON

data type. The

CAST

function is generally limited to basic types (like

INT

,

STRING

, etc.) when converting to

JSON

. You must use a constructor function like

to_json

or

parse_json

. Related Functions *

json_array(val1, val2, ...)

: This is used to build a JSON array from individual scalar values (e.g.,

json_array(1, 2, 3)

), but it does not accept a single

ARRAY

object as an argument. *

array_join(array, sep)

: If you only need a string representation without the JSON data type properties, you can use this to flatten the array into a delimited by by character. array into a delimited. concatenate elements into-functions/json-functions/json-query-and-processing-functions/to_json.md • parse_json documentation • CAST documentation References • sql-reference/sql-functions/cast.md • sql-reference/sql-functions/array-functions/array_join.md • sql-reference/sql-functions/json-functions/json-constructor-functions/json_array.md • sql-reference/sql-functions/string-functions/money_format.md • sql-reference/sql-functions/bitmap-functions/bitmap_to_string.md • sql-reference/sql-functions/bitmap-functions/bitmap_to_array.md • knowledge/usage/best-practice.md

@Rocky None of them actually work

Converting an

ARRAY

type to a

JSON

type cannot be done via a direct

CAST(array AS JSON)

in StarRocks because the

CAST

function currently only supports basic types (like

STRING

,

INT

,

BIGINT

) for conversion to the

JSON

data type. Depending on your StarRocks version, use one of the following methods: 1. The

to_json()

Function (Best for v3.3+) Starting from v3.3.0, the

to_json()

function was updated to support the

ARRAY

data type. This is the most reliable and performant method.

sql
SELECT to_json(your_array_column) FROM your_table;

2. The

parse_json

+

CAST

Workaround (For v2.5 to v3.2) If you are on an older version where

to_json

only accepts

MAP

or

STRUCT

, you must first cast the array to a string and then parse it into JSON.

sql
SELECT parse_json(CAST(your_array_column AS STRING)) FROM your_table;

_Note: If this fails, it is usually because the string representation of your array (e.g.,

[1, 2, 3]

) contains special formatting or characters that

parse_json

doesn't expect in your specific version._ 3. Manual Construction (Safest for older versions) If the above methods fail, you can manually construct a JSON-compliant string using

array_join

and then parse it. This is specifically useful for

ARRAY<BIGINT>

because the elements don't require quotes:

sql
SELECT parse_json(CONCAT('[', array_join(your_array_column, ','), ']')) FROM your_table;

Why

CAST(... AS JSON)

fails StarRocks distinguishes between scalar types and complex types (ARRAY, MAP, STRUCT). While you can cast a scalar

BIGINT

to

JSON

, the logic to transform a complex

ARRAY

structure into the internal binary

JSON

format is handled by dedicated constructor functions like

to_json

rather than the general

CAST

operator. Summary of Version Support Function |

ARRAY

Support Started

CAST(expr AS JSON)

| Scalar types only

to_json(expr)

| v3.3.0 (Shared-nothing), v3.3.3 (Shared-data)

parse_json(str)

| v2.2.0 If you are still seeing errors, please check your StarRocks version using

SELECT VERSION();

and ensure the column is indeed an

ARRAY

type. References • sql-reference/data-types/semi_structured/JSON.md • sql-reference/sql-functions/json-functions/json-constructor-functions/json_array.md • sql-reference/sql-functions/cast.md • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/cast.md