list_position

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Search a list. Returns the position, counted from 1, of the first element of a list that equals a value, or NULL if no element does. list_indexof, array_position and array_indexof are the same function.

Syntax

list_position(list, element)
list_indexof(list, element)
array_position(list, element)
array_indexof(list, element)

Returns: INTEGER: the position of the first match, starting at 1, or NULL if there's none.

Aliases: list_indexof, array_position, array_indexof.

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_position. 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 delivery routes, each with its list of stops in order and a list of stop numbers. Route 2 has a NULL stop number, route 3 has no stops, 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_position"'

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}.routes"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, stops VARCHAR[], stop_ids BIGINT[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['depot', 'mill', 'port', 'depot'], [10, 20, 30, 10]), "
    "(2, ['port', 'depot'], [30, NULL]), "
    "(3, [], []), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

list_position looks for the first element of list that equals element and returns its position. Positions start at 1, as in list_extract and l[i], so l[list_position(l, x)] is x when x is in the list.

• The first match wins. list_position([5, 5], 5) is 1.
• No match gives NULL, not 0: test the result with IS NOT NULL.
• The comparison is exact and typed, as in list_contains: case-sensitive for strings, by value for numbers (2.0 is found at position 2 of [1, 2]), whole elements for nested lists, and NaN finds NaN.

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_position(NULL, 3), list_position([1, 2], NULL), list_position([1, NULL], NULL), "
    "list_position([1, 2, 3], 42), list_position([], 1), list_position([5, 5], 5)")
run("SELECT list_position([1, 2], 2.0), list_position(['a', 'b'], 'B'), list_position([[1], [2]], [2]), "
    "list_position([1.0, 'NaN'::DOUBLE], 'NaN'::DOUBLE)")
run("SELECT list_position([1, 2], 'a')")

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

Examples

Where each route first stops at the depot and at stop 30. Route 1 visits the depot twice; the first visit counts:

run(f"SELECT id, list_position(stops, 'depot'), array_position(stop_ids, 30) FROM {TABLE} ORDER BY id LIMIT 10")

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

The routes that stop at the port, and the stop right after it. If the port were a route's last stop, the result would be NULL, because a position past the end of a list gives NULL:

run(f"SELECT id, stops[list_position(stops, 'port') + 1] FROM {TABLE} "
    f"WHERE list_position(stops, 'port') IS NOT NULL ORDER BY id LIMIT 10")

[(1, 'depot'), (2, 'depot')]

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_position() 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 a WHERE on the position shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE list_position(stops, 'port') IS NOT NULL")

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

Make it faster

• To test membership, use list_contains. list_position(l, x) IS NOT NULL and list_contains(l, x) keep the same rows; list_contains says what you mean and returns a BOOLEAN.
• 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.

run(f"SELECT id FROM {TABLE} WHERE id > 1 AND list_contains(stops, 'port') ORDER BY id LIMIT 10")

[(2,)]

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_position(STRING_LITERAL, STRING_LITERAL)'. To find a substring, use strpos.
• The value must match the element type. Otherwise the call fails with Cannot match element of type ... - an explicit cast is required.
• Only the first match, and never a NULL. list_position returns one position, and a NULL element can't be found. To test for NULL elements, see list_contains.

run("SELECT list_position('abc', 'b')")

InternalError: Binder Error: No function matches the given name and argument types 'list_position(STRING_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Similar functions

run("SELECT list_position(l, 3), list_contains(l, 3), list_extract(l, 2), l[2], list_position(l, NULL) "
    "FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

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

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.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.