list_string_agg

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Aggregate a list. Joins the non-NULL elements of a list into one VARCHAR, separated by commas, and returns one string per row. Elements of other types are converted to text first. The separator is always a comma; for another one, use array_to_string or list_aggregate.

Syntax

list_string_agg(l)

Returns: VARCHAR.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_lists_list_string_agg. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), 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 five rows of lists: tags, numbers and measurements. Row 2 has NULL elements, row 3 has empty lists, row 4 has no values, and row 5 has accented letters, a flag emoji and an empty string:

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_list_string_agg"'

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, "<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}.samples"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, tags VARCHAR[], nums BIGINT[], vals DOUBLE[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['a', 'b', 'c'], [1, 2, 3], [0.5, 2.5, -1.0]), "
    "(2, ['b', NULL, 'B'], [3, NULL, 3], [2.0, NULL]), "
    "(3, [], [], []), "
    "(4, NULL, NULL, NULL), "
    "(5, ['\u00e9', 'Zo\u00eb', '\U0001F1EB\U0001F1F7', ''], [-5, 0, -5, 10], [1e308, 1e308, -1e-308])")

[]
[]
[(5,)]

What it does

list_string_agg(l) converts each non-NULL element of l to text and joins them in list order, with a comma and no space between them:

• list_string_agg(['a', 'b']) is 'a,b'.
• NULL elements are skipped, with no empty field left for them: list_string_agg(['a', NULL, 'b']) is 'a,b'.
• An empty string is an element like any other, so it still gets its comma: row 5's tags end in ,.
• Numbers are converted to text: list_string_agg([1, 2]) is '1,2', and a DOUBLE list gives values such as -1.0 and 1e+308. A list of lists joins each inner list as text: list_string_agg([[1], [2]]) is '[1],[2]'.
• A list with no non-NULL elements, empty or all NULL, returns NULL, not ''.

The result is always a VARCHAR, whatever the element type.

NULLs and edge cases

The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT list_string_agg(NULL::VARCHAR[]), list_string_agg([]::VARCHAR[]), list_string_agg([NULL]::VARCHAR[]), "
    "list_string_agg(['a', NULL, 'b']), list_string_agg([1, 2]), list_string_agg([[1], [2]])")
run("SELECT list_string_agg(['a', 'b'], '|')")
run(f"SELECT list_string_agg(123) FROM {TABLE} LIMIT 1")

[(None, None, None, 'a,b', '1,2', '[1],[2]')]
InternalError: Binder Error: Macro function list_string_agg(l) requires a single positional argument, but 2 positional arguments were provided.
InternalError: Binder Error: No function matches the given name and argument types 'list_aggr(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Join each row's tags, numbers and measurements. Rows 3 and 4 return NULL:

run(f"SELECT id, list_string_agg(tags), list_string_agg(nums), list_string_agg(vals) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'a,b,c', '1,2,3', '0.5,2.5,-1.0'), (2, 'b,B', '3,3', '2.0'), (3, None, None, None), (4, None, None, None), (5, 'é,Zoë,🇫🇷,', '-5,0,-5,10', '1e+308,1e+308,-1e-308')]

Keep the rows that have at least one tag, and show the tags as one string:

run(f"SELECT id, list_string_agg(tags) FROM {TABLE} WHERE list_string_agg(tags) IS NOT NULL ORDER BY id LIMIT 10")

[(1, 'a,b,c'), (2, 'b,B'), (5, 'é,Zoë,🇫🇷,')]

Use another separator with list_aggregate. The separator must be a constant:

run(f"SELECT id, list_aggregate(tags, 'string_agg', ', ') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'a, b, c'), (2, 'b, B'), (3, None), (4, None), (5, 'é, Zoë, 🇫🇷, ')]

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 a list column whole, and list_string_agg() always runs in the Query Engine:

• In the SELECT list, a projection step builds the string for each row after the scan.
• In WHERE, the scan sends the list for every row, and a FILTER step above the scan computes the condition.

The plan of the WHERE example shows the FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE list_string_agg(tags) IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (list_aggr(tags,      │
│ 'string_agg') IS NOT NULL)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            tags           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Add a condition on a plain column. A comparison of a column with a constant, such as id > 1, is evaluated in VAST DataBase, so the strings are built only for the rows that pass it. The plan for id > 1 alone has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE id > 1")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Limitations and errors

• The separator is always a comma. A second argument fails with Macro function list_string_agg(l) requires a single positional argument, but 2 positional arguments were provided. Use array_to_string(l, sep) or list_aggregate(l, 'string_agg', sep).
• list_aggregate's separator must be a constant. A column fails with Binder Error: Separator argument to StringAgg must be a constant.
• The argument must be a list. Anything else fails with Binder Error: No function matches the given name and argument types 'list_aggr(...)'; the message names list_aggr because the function is a form of list_aggregate.
• ORDER BY needs a LIMIT. Without one, the query fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.

Similar functions

run("SELECT list_string_agg(l), array_to_string(l, ','), array_to_string(l, ''), list_aggregate(l, 'string_agg', '|') "
    "FROM (SELECT ['a', NULL, 'b'] AS l)")

[('a,b', 'a,b', 'ab', '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: all the list aggregates.
• array_to_string: join a list with any separator.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.