--- title: "array_to_string" slug: "dev-database-reference-sql-functions-lists-array-to-string" updated: 2026-10-30T18:19:11Z published: 2026-10-30T18:19:11Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-lists-array-to-string" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # array_to_string *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) โ€บ [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs) โ€บ Combine and change lists.* Joins the elements of a list into one string, with a separator between them. NULL elements are skipped. `array_to_string_comma_default` does the same with a comma. ## Syntax ``` array_to_string(list, separator) array_to_string_comma_default(list) ``` | Argument | Type | Description | | --- | --- | --- | | `list` | a list of any element type | The elements to join. Each one is converted to text. | | `separator` | `VARCHAR` | The text put between two elements. Can be `''`. | **Returns:** `VARCHAR`. `NULL` if the list is NULL, empty, or holds only NULLs. **Aliases:** `array_to_string_comma_default(list)` is `array_to_string(list, ',')`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_lists_array_to_string`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of four support tickets, each with a list of labels and a list of error codes. Ticket 2 has NULL elements, ticket 3 has empty lists, and the last row has no values: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_lists_array_to_string"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.tickets" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, labels VARCHAR[], codes BIGINT[])") run(f"INSERT INTO {TABLE} VALUES " "(1, ['bug', 'ui'], [404, 500]), " "(2, ['ops', NULL, 'db'], [NULL, 7]), " "(3, [], []), " "(4, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `array_to_string` converts each element of the list to text and joins the results, in order, with `separator` between them: `array_to_string([1, 2, 3], ',')` is `'1,2,3'`. - **NULL elements are skipped,** and no separator is written for them: `array_to_string(['a', NULL, 'b'], ',')` is `'a,b'`. - **Nothing to join gives NULL, not `''`.** An empty list, a list of NULLs and a NULL list all return `NULL`. Wrap the call in `coalesce(..., '')` if you need an empty string. - **How elements are written:** `true` and `false` for booleans, `2026-10-16` for a date, `nan` and `inf` for special doubles, `1e+308` for a large double, and an inner list in brackets (`[1, 2]`). ## NULLs and edge cases | Input | Result | | --- | --- | | `array_to_string(NULL, ',')` | `NULL` | | `array_to_string([], ',')` | `NULL` | | `array_to_string([NULL, NULL], ',')` | `NULL` | | `array_to_string(['a', NULL, 'b'], ',')` | `'a,b'` | | `array_to_string(['a'], NULL)` | `'a'` | | `array_to_string(['a', 'b'], '')` | `'ab'` | | `array_to_string(['a', 'b'], ' \\| ')` | `'a \\| b'` | | `array_to_string([0.5, 1e308, -0.0], ',')` | `'0.5,1e+308,0.0'` | | `array_to_string(['NaN'::DOUBLE, 'inf'::DOUBLE], ',')` | `'nan,inf'` | | `array_to_string([true, false], ',')` | `'true,false'` | | `array_to_string([[1, 2], [3]], ';')` | `'[1, 2];[3]'` | | `array_to_string(['\u00e9', '\U0001F1EB\U0001F1F7'], '\u00b7')` (non-ASCII text) | the elements joined with `\u00b7`, unchanged | | `array_to_string(['a', 'b'])` (no separator) | Error: `Macro function array_to_string(arr, sep) requires 2 positional arguments, but a single positional argument was provided.` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-lists-array-to-string#set-up-the-examples): ``` run("SELECT array_to_string(NULL, ','), array_to_string([], ','), array_to_string([NULL, NULL], ','), " "array_to_string(['a', NULL, 'b'], ','), array_to_string(['a'], NULL)") run("SELECT array_to_string(['a', 'b'], ''), array_to_string(['a', 'b'], ' | '), " "array_to_string([0.5, 1e308, -0.0], ','), array_to_string(['NaN'::DOUBLE, 'inf'::DOUBLE], ',')") run("SELECT array_to_string([true, false], ','), array_to_string([DATE '2026-10-16'], ','), " "array_to_string([[1, 2], [3]], ';'), array_to_string(['\u00e9', '\U0001F1EB\U0001F1F7'], '\u00b7')") run("SELECT array_to_string(['a', 'b'])") ``` ``` [(None, None, None, 'a,b', 'a')] [('ab', 'a | b', '0.5,1e+308,0.0', 'nan,inf')] [('true,false', '2026-10-16', '[1, 2];[3]', 'รฉยท๐Ÿ‡ซ๐Ÿ‡ท')] InternalError: Binder Error: Macro function array_to_string(arr, sep) requires 2 positional arguments, but a single positional argument was provided. ``` ## Examples The labels and codes of each ticket as text. The NULL elements of ticket 2 are skipped; the empty lists of ticket 3 and the NULL lists of ticket 4 give NULL: ``` run(f"SELECT id, array_to_string(labels, ', '), array_to_string(codes, '-') FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'bug, ui', '404-500'), (2, 'ops, db', '7'), (3, None, None), (4, None, None)] ``` The same labels joined with a comma, and an empty string instead of NULL: ``` run(f"SELECT id, array_to_string_comma_default(labels), coalesce(array_to_string(labels, ','), '') " f"FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'bug,ui', 'bug,ui'), (2, 'ops,db', 'ops,db'), (3, None, ''), (4, None, '')] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). The scan reads list columns whole, and `array_to_string()` is always computed by the Query Engine: - **In the `SELECT` list,** the scan sends the list column and the Query Engine joins its elements for each row it returns. - **In `WHERE`,** a `FILTER` step in the Query Engine joins the elements of every row the scan sends and keeps the rows that match. No rows are skipped in storage. `array_to_string` is a macro over list aggregation, which is why a separator of the wrong type fails with an error that names `string_agg` (see [Limitations and errors](/develop/docs/dev-database-reference-sql-functions-lists-array-to-string#limitations-and-errors)). The plan of a `LIKE` on the joined text shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE array_to_string(labels, ',') LIKE '%ui%'") ``` ``` โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ” โ”‚ PROJECTION โ”‚ โ”‚ โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€ โ”‚ โ”‚ #1 โ”‚ โ”‚ โ”‚ โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜ โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ” โ”‚ FILTER โ”‚ โ”‚ โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€ โ”‚ โ”‚ contains(list_aggr(labels,โ”‚ โ”‚ 'string_agg'), 'ui') โ”‚ โ”‚ โ”‚ โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜ โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ” โ”‚ VAST_SCAN โ”‚ โ”‚ โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€ โ”‚ โ”‚ Function: VAST_SCAN โ”‚ โ”‚ โ”‚ โ”‚ Projections: โ”‚ โ”‚ labels โ”‚ โ”‚ id โ”‚ โ”‚ โ”‚ โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜ ``` ## Make it faster - **Search the list, not its text.** To find rows with a given element, use [list_contains](/develop/docs/dev-database-reference-sql-functions-lists-list-contains) or [list_has_any](/develop/docs/dev-database-reference-sql-functions-lists-list-has-any): they compare elements directly and don't build a string for every row. `array_to_string(labels, ',') LIKE '%ui%'` also matches `'guide'`. - **Pair a list condition with a pushed-down one.** A comparison of a plain column with a constant, such as `id > 1`, is evaluated in VAST DataBase, so the rows it rules out never reach the `FILTER` step. ``` run(f"SELECT id FROM {TABLE} WHERE list_contains(labels, 'ui') ORDER BY id LIMIT 10") ``` ``` [(1,)] ``` ## Limitations and errors - **The first argument must be a list.** A string fails with `Type VARCHAR with value 'abc' can't be cast to the destination type LIST`. A number, date, boolean or `BLOB` fails with `Unimplemented type for cast`, for example `(INTEGER -> VARCHAR[]`. - **The separator must be text, and is required.** An integer separator fails with `No matching aggregate function`; leaving it out fails with `Macro function array_to_string(arr, sep) requires 2 positional arguments`. `array_to_string_comma_default` takes exactly one argument: a second one fails with `requires a single positional argument, but 2 positional arguments were provided`. - **Other SQL dialects' names aren't available.** `array_join` and `list_join` fail with `Scalar Function with name ... does not exist!`. Use `array_to_string`. ``` run("SELECT array_to_string('abc', ',')") run("SELECT array_to_string(['a', 'b'], 1)") run("SELECT array_join(['a', 'b'], ',')") ``` ``` OSError: Type VARCHAR with value 'abc' can't be cast to the destination type LIST InternalError: Binder Error: No matching aggregate function InternalError: Catalog Error: Scalar Function with name array_join does not exist! ``` ## Similar functions | Function | Separator | Result for `['a', NULL, 'b']` | | --- | --- | --- | | `array_to_string(l, sep)` | any text | `'a,b'` with `','`, `'ab'` with `''` | | `array_to_string_comma_default(l)` | `,` | `'a,b'` | | [list_string_agg](/develop/docs/dev-database-reference-sql-functions-lists-list-string-agg) | `,` | `'a,b'` | | [list_aggregate](/develop/docs/dev-database-reference-sql-functions-lists-list-aggregate) with `'string_agg'` | its third argument | `'a\\|b'` with `'\\|'` | | [concat_ws](/develop/docs/dev-database-reference-sql-functions-string-concat-ws) | its first argument | joins separate strings, not a list | ``` run("SELECT array_to_string(l, ','), array_to_string(l, ''), array_to_string_comma_default(l), " "list_string_agg(l), list_aggregate(l, 'string_agg', '|') FROM (SELECT ['a', NULL, 'b'] AS l)") ``` ``` [('a,b', 'ab', 'a,b', 'a,b', 'a|b')] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs): all the list functions. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.