array_pop_front

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Combine and change lists. Returns a list without its first element.

Syntax

array_pop_front(list)

Returns: a value of the same type as list: a list, or a VARCHAR or BLOB for those.

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_array_pop_front. 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 sensors, each with a list of readings (readings) and a list of status codes (codes). Sensor 2 has a NULL reading, sensor 3 has one reading and an empty list of codes, and sensor 4 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_pop_front"'

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}.sensors"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, readings DOUBLE[], codes VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [0.5, 2.5, -1.0], ['ok', 'ok', 'warn']), "
    "(2, [2.0, NULL], ['ok', NULL]), "
    "(3, [1.5], []), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

array_pop_front returns the list without its first element: array_pop_front([1, 2, 3]) is [2, 3]. A list of one element gives an empty list, and an empty list stays empty. On a list of lists it removes the first inner list: array_pop_front([[1, 2], [3]]) is [[3]].

To remove more than one element, call it again (array_pop_front(array_pop_front([1, 2, 3])) is [3]) or take a slice with list_slice.

It also takes a VARCHAR, and removes the first character (array_pop_front('abc') is 'bc'), and a BLOB: the one-byte BLOB '\xAA'::BLOB gives an empty BLOB.

NULLs and edge cases

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

run("SELECT array_pop_front(NULL), array_pop_front(NULL::BIGINT[]), array_pop_front([]), array_pop_front([]::BIGINT[]), "
    "array_pop_front([1]), array_pop_front([NULL, NULL])")
run("SELECT array_pop_front([[1, 2], [3]]), array_pop_front(array_pop_front([1, 2, 3])), array_pop_front('abc'), "
    "array_pop_front('\\xAA'::BLOB)")
run("SELECT array_pop_front(123)")

[(None, None, [], [], [], [None])]
[([[3]], [3], 'bc', b'')]
InternalError: Binder Error: ARRAY_SLICE can only operate on LISTs and VARCHARs

Examples

Drop the first reading and the first code of each sensor. Sensor 3's single reading leaves an empty list, and sensor 4's NULL lists stay NULL:

run(f"SELECT id, array_pop_front(readings), array_pop_front(codes) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [2.5, -1.0], ['ok', 'warn']), (2, [None], [None]), (3, [], []), (4, None, None)]

Keep the sensors that reported 'warn' after their first code:

run(f"SELECT id FROM {TABLE} WHERE list_contains(array_pop_front(codes), 'warn') ORDER BY id LIMIT 10")

[(1,)]

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 VAST DataBase doesn't evaluate list functions, so array_pop_front() is always computed by the Query Engine:

• In the SELECT list, the scan reads the list column and the Query Engine computes array_pop_front() for each row it returns.
• In WHERE, the scan sends the list for every row, and a FILTER step in the Query Engine computes the condition 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(array_pop_front(codes), 'warn')")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ list_contains(array_slice │
│  (codes, 2, []), 'warn')  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           codes           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Pair a list condition with a pushed-down condition. In id > 1 AND list_contains(array_pop_front(codes), 'warn'), id > 1 is evaluated in VAST DataBase, so the list condition runs only on the rows that pass it. The FILTER step in the plan holds only the list condition:

plan(f"SELECT id FROM {TABLE} WHERE id > 1 AND list_contains(array_pop_front(codes), 'warn')")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ list_contains(array_slice │
│  (codes, 2, []), 'warn')  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│           codes           │
│                           │
└───────────────────────────┘

• Keep values you filter on outside lists. If queries often test the first reading, store it in its own column when you write the rows, so the filter can be pushed down.

Limitations and errors

• Takes a list, a VARCHAR or a BLOB. A number, date or boolean fails with ARRAY_SLICE can only operate on LISTs and VARCHARs: array_pop_front is defined with a slice.
• One element at a time. It takes no count. Nest the calls, or use list_slice to skip the first n elements.
• A NULL list returns NULL; an empty list returns an empty list. Test with IS NULL or len() if you need to tell them apart.

Similar functions

run("SELECT array_pop_front(l), array_pop_back(l), list_slice(l, 2, 3), l[2:3], list_first(l) "
    "FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[([1, None, 3, 10], [3, 1, None, 3], [1, None], [1, None], 3)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• array_pop_back: remove the last element.
• list_prepend: add an element at the start.
• List, struct and map functions: all the list functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.