CANNOT_READ_ARRAY_FROM_TEXT ClickHouse error¶

The CANNOT_READ_ARRAY_FROM_TEXT error in ClickHouse (and Tinybird) happens when you try to convert a text string to an Array value, but the string format doesn't match what ClickHouse expects. This commonly occurs when importing array data or using array conversion functions.

What causes this error¶

You'll typically see it when:

• Using toArray() on strings with unexpected formats
• Working with array strings that don't match ClickHouse's default format
• Importing data with non-standard array formats
• Using array functions with incorrectly formatted strings
• Working with arrays from different sources or formats

Example errors¶

SELECT splitByString(',', '1,2,3')

SELECT splitByString(',', 'not an array')

SELECT splitByString(';', '1;2;3')

SELECT splitByString(',', '1,2,3')

How to fix it¶

Use the correct array format¶

ClickHouse expects array format with square brackets:

SELECT splitByString(',', '1,2,3') as array_from_string

Use proper array functions¶

Use appropriate functions for array conversion:

SELECT splitByString(',', '1,2,3') as array_from_string

Handle different array formats¶

Convert different array formats to ClickHouse format:

SELECT toArray('[' || replace('1,2,3', ',', ',') || ']')

Use try functions for error handling¶

Use try functions to handle parsing errors gracefully:

SELECT try(toArray(array_string)) as parsed_array FROM events

Common patterns and solutions¶

Standard array format¶

ClickHouse expects arrays in JSON-like format:

SELECT splitByString(',', '1,2,3')  -- Array of integers
SELECT splitByString(',', 'a,b,c')  -- Array of strings
SELECT JSONExtractArrayRaw('[true,false,true]')  -- Array of booleans

Converting from comma-separated strings¶

Convert comma-separated strings to arrays:

SELECT splitByString(',', '1,2,3') as array_from_csv
SELECT splitByString(',', 'a,b,c') as string_array

Working with JSON arrays¶

Parse arrays from JSON data:

SELECT JSONExtractArrayRaw('{"items": [1,2,3]}', 'items') as json_array
SELECT JSONExtract('{"items": [1,2,3]}', 'items') as json_array

Array manipulation functions¶

SELECT arrayStringConcat(['a', 'b', 'c'], ',') as concatenated
SELECT arrayJoin(['a', 'b', 'c']) as exploded

SELECT JSONExtractArrayRaw(data, 'tags') as tags_array FROM events
SELECT JSONExtractArrayString(data, 'categories') as categories FROM events

Array type conversion¶

Convert arrays to different types:

SELECT toArrayInt32(splitByChar(',', '1,2,3')) as int_array
SELECT toArrayString(splitByChar(',', 'a,b,c')) as string_array

Advanced solutions¶

Handling various array formats¶

Create robust parsing for multiple formats:

SELECT CASE
    WHEN array_string LIKE '[%]' THEN toArray(array_string)
    WHEN array_string LIKE '%[%]%' THEN toArray(array_string)
    ELSE splitByChar(',', array_string)
END as parsed_array
FROM events

Working with nested arrays¶

Handle nested array structures:

SELECT toArray('[[1,2],[3,4]]') as nested_array
SELECT JSONExtractArrayRaw(data, 'nested_array') as json_nested FROM events

Array validation¶

Validate array strings before conversion:

SELECT CASE
    WHEN array_string REGEXP '^\\[.*\\]$' THEN toArray(array_string)
    WHEN array_string REGEXP '^[0-9,]+$' THEN splitByChar(',', array_string)
    ELSE NULL
END as validated_array
FROM events

Array format reference¶

ClickHouse array format¶

Arrays in ClickHouse use JSON-like syntax:

SELECT toArray('[1,2,3]')  -- Integer array
SELECT toArray('["a","b","c"]')  -- String array
SELECT toArray('[true,false]')  -- Boolean array
SELECT toArray('[[1,2],[3,4]]')  -- Nested array

Converting from other formats¶

Convert common formats to ClickHouse arrays:

-- From comma-separated
SELECT splitByChar(',', '1,2,3') as from_csv

-- From space-separated
SELECT splitByChar(' ', '1 2 3') as from_space

-- From JSON array
SELECT JSONExtractArrayRaw('{"tags":["a","b","c"]}', 'tags') as from_json

Tinybird-specific notes¶

In Tinybird, this error often occurs when:

• Ingesting data with non-standard array formats
• Using Schema Hints with mismatched array formats
• Processing JSON data with embedded array strings
• Working with Data Sources that have inconsistent array formats

To debug in Tinybird:

1. Check your Data Source schema for array column types
2. Use Schema Hints to specify array formats
3. Validate incoming array formats
4. Use try functions in your Pipes for robust parsing

Best practices¶

Array standardization¶

• Use ClickHouse array format [1,2,3] when possible
• Standardize array formats across your data sources
• Handle different array separators consistently
• Use appropriate array functions for your use case

Error handling¶

• Use try() functions for robust array parsing
• Validate array strings before conversion
• Provide fallback values for invalid arrays
• Log parsing errors for debugging

Performance considerations¶

• Use appropriate array data types
• Consider using Array functions for array operations
• Use array indexes when available
• Monitor array operation performance

See also¶

• ClickHouse Array Functions
• Working with Arrays
• Common function errors