list_contains

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Search a list. Returns true if a list has an element equal to a value, false if it doesn't. list_has, array_contains, array_has and contains (with a list) are the same function.

Syntax

list_contains(list, element)
list_has(list, element)
array_contains(list, element)
array_has(list, element)
contains(list, element)

Returns: BOOLEAN.

Aliases: list_has, array_contains, array_has, and contains when its first argument is a list.

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_contains. 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 four articles, each with a list of tags and a list of ratings. Article 2 has NULL elements, article 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_list_contains"'

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}.articles"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, tags VARCHAR[], ratings BIGINT[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['db', 'sql'], [4, 5, 5]), "
    "(2, ['vector', NULL], [3, NULL, 1]), "
    "(3, [], []), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

list_contains is true if any element of list equals element:

• Exact, typed comparison. Strings must match exactly, so the match is case-sensitive ('A' doesn't match 'a'), and an é written as one code point (U+00E9) doesn't match an e followed by a combining accent (U+0301). Numbers of different types compare by value: 2.0 matches 2 in an INTEGER[], 2.5 doesn't.
• Whole elements, not substrings. list_contains(['abc'], 'b') is false. To search inside a string, use contains with a string.
• Nested lists are searched one level down: list_contains([[1, 2], [3]], [1, 2]) is true.
• NaN matches NaN, and -0.0 matches 0.0.

NULLs and edge cases

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

run("SELECT list_contains(NULL, 1), list_contains([1, 2], NULL), list_contains([1, NULL], NULL), "
    "list_contains([1, NULL], 2), list_contains([], 1)")
run("SELECT list_contains([1, 2], 2.0), list_contains([1, 2], 2.5), list_contains(['a', 'b'], 'A'), "
    "list_contains(['\u00e9'], 'e\u0301')")
run("SELECT list_contains(['NaN'::DOUBLE, 1.0], 'NaN'::DOUBLE), list_contains([-0.0], 0.0), "
    "list_contains([[1, 2], [3]], [1, 2])")
run("SELECT list_contains([1, 2], 'a')")

[(None, None, None, False, False)]
[(True, False, False, False)]
[(True, True, True)]
InternalError: Binder Error: list_contains: Cannot match element of type 'VARCHAR' in a list of type 'INTEGER[]' - an explicit cast is required

Examples

Which articles are tagged sql, and which have a rating of 5. Article 2 has a NULL tag, but no sql tag, so the answer is false; the NULL lists of article 4 give NULL:

run(f"SELECT id, list_contains(tags, 'sql'), list_has(ratings, 5) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, True, True), (2, False, False), (3, False, False), (4, None, None)]

Keep only the articles with a rating of 1. The aliases return the same rows:

run(f"SELECT id FROM {TABLE} WHERE list_contains(ratings, 1) ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE array_has(ratings, 1) AND contains(ratings, 1) ORDER BY id LIMIT 10")

[(2,)]
[(2,)]

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 every condition on a list, list_contains() included, runs in the Query Engine:

• In the SELECT list, the scan sends the list column and the Query Engine searches it for each row it returns.
• In WHERE, the scan sends the list for every row, and a FILTER step in the Query Engine searches it and keeps the rows that match. No rows are skipped in storage.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE list_contains(ratings, 1)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ list_contains(ratings, 1) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          ratings          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Pair it with a pushed-down condition. 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. The plan shows which conditions are left in the FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE id > 1 AND list_contains(ratings, 1)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ list_contains(ratings, 1) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│          ratings          │
│                           │
└───────────────────────────┘

• Keep values you filter on often outside the list. If most queries ask whether one particular tag is present, store it in its own column when you write the rows, such as has_sql BIGINT set to 1 or 0: has_sql = 1 is pushed down, list_contains(tags, 'sql') isn't.
• Test several values in one call. To ask whether any of several values is present, list_has_any takes them as one list.

Limitations and errors

• The first argument must be a list. A number, string, date, boolean or BLOB fails with No function matches the given name and argument types, for example 'list_contains(INTEGER_LITERAL, INTEGER_LITERAL)'. The error lists the one form: list_contains(ANY[], ANY) -> BOOLEAN.
• The value must match the element type. A string in a list of numbers, or a number in a list of strings, fails with Cannot match element of type ... - an explicit cast is required. So does a scalar in a nested list: list_contains([[1, 2], [3]], 1). Cast the value, or flatten the list with flatten.
• contains has other forms. contains also takes a string (is one string inside another) and a map (is a key in the map), so contains(NULL, 1) fails with Could not choose a best candidate function for the function call. Use list_contains.
• NULL can't be found. list_contains(l, NULL) is NULL, even when the list holds a NULL element. To test for NULL elements, count them with list_filter: len(list_filter(l, x -> x IS NULL)) > 0.

run("SELECT list_contains([[1, 2], [3]], 1)")
run("SELECT contains(NULL, 1)")
run("SELECT contains('abc', 'b'), contains([1, 2], 2), contains(MAP {'a': 1}, 'a'), contains(['abc'], 'b')")
run("SELECT len(list_filter(l, x -> x IS NULL)) > 0 FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

InternalError: Binder Error: list_contains: Cannot match element of type 'INTEGER' in a list of type 'INTEGER[][]' - an explicit cast is required
InternalError: Binder Error: Could not choose a best candidate function for the function call "contains("NULL", INTEGER_LITERAL)". In order to select one, please add explicit type casts.
[(True, True, True, False)]
[(True,)]

Similar functions

run("SELECT list_contains(l, 3), list_position(l, 3), list_has_any(l, [3, 99]), list_has_all(l, [3, 10]), "
    "len(list_filter(l, x -> x = 3)) > 0 FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[(True, 1, True, True, True)]

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 functions.
• map_contains: the map form of contains.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.