map_extract

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Look up a key. Looks up a key in a map and returns its value inside a list: a list of one element if the key is in the map, an empty list if it isn't. element_at is the same function.

Syntax

map_extract(m, key)
element_at(m, key)

Returns: a list of the map's value type, such as VARCHAR[].

Aliases: element_at.

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_maps_map_extract. 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: the languages each one is translated into, the word count of each translation, and the language a reader asked for. Article 2 has a translation with no word count yet and was asked for in a language it doesn't have, article 3 has no translations, and the last row has no values. A table can't return a MAP column, so the keys and values are stored as two lists:

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

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, langs VARCHAR[], words BIGINT[], lang VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['en', 'de'], [120, 135], 'de'), "
    "(2, ['fr', 'es'], [NULL, 140], 'it'), "
    "(3, [], [], ''), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

map_extract(m, key) finds key among the keys of m. The result is a list, so it tells apart a key that's missing from a key whose value is NULL:

Take the value out of the list with [1]: map_extract(m, key)[1].

• Keys are compared exactly. 'DE' doesn't find 'de', and é written as one code point doesn't find e followed by a combining accent (U+0301).
• The key is cast to the map's key type. In a map with INTEGER keys, 1, 1.0 and '1' all find the key 1; in a map with VARCHAR keys, 1 finds '1'. A DATE key can be looked up with a string such as '2026-10-16'. A key that doesn't match any key, such as 1 in a map whose keys are 'a', gives [].

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 map_extract(MAP {'a': 1}, NULL), map_extract(NULL::MAP(VARCHAR, BIGINT), 'a'), "
    "map_extract(NULL, 'a'), map_extract(MAP {'a': NULL}, 'a'), map_extract(MAP {}, 'a')")
run("SELECT map_extract(MAP {'en': 'English', 'de': 'German'}, 'DE'), map_extract(map(['\u00e9'], [1]), 'e\u0301')")
run("SELECT map_extract(MAP {1: 'x'}, '1'), map_extract(MAP {'1': 'x'}, 1), "
    "map_extract(map(['NaN'::DOUBLE], [1]), 'NaN'::DOUBLE)")
run("SELECT map_extract(MAP {'a': 1}, 'a', 'b')")
run("SELECT map_extract([1, 2], 1)")

[([], None, [], [None], [])]
[([], [])]
[(['x'], ['x'], [1])]
InternalError: Binder Error: MAP_EXTRACT must have exactly two arguments
InternalError: Binder Error: 'map_extract' can only operate on MAPs

Examples

A key that's there and one that isn't, with element_at giving the same results:

run("SELECT map_extract(MAP {'en': 'English', 'de': 'German'}, 'de'), "
    "map_extract(MAP {'en': 'English', 'de': 'German'}, 'fr'), "
    "element_at(MAP {'en': 'English', 'de': 'German'}, 'de')")

[(['German'], [], ['German'])]

Look up the language each reader asked for. Article 1 has German; article 2 has no Italian and article 3 no translations, so their lists are empty; the NULL lists of article 4 give a NULL map, and so NULL:

run(f"SELECT id, lang, map_extract(map(langs, words), lang) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'de', [135]), (2, 'it', []), (3, '', []), (4, None, None)]

Look up each article's first language. Article 2's French translation has no word count, so its list holds one NULL; [1] takes the value out of the list:

run(f"SELECT id, map_extract(map(langs, words), langs[1]), map_extract(map(langs, words), langs[1])[1] "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [120], 120), (2, [None], None), (3, [], None), (4, None, 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). map_extract() is always computed by the Query Engine:

• In the SELECT list, the scan sends the list columns, and the Query Engine builds the map and looks up the key for each row it returns.
• In WHERE, a FILTER step in the Query Engine does the same for every row the scan sends. No rows are skipped in storage.

The result is a list, and an empty list isn't NULL: WHERE map_extract(...) IS NOT NULL keeps every row with a map, even when the key isn't there. To keep the rows that have the key, test the list's length, or use map_contains:

run(f"SELECT id FROM {TABLE} WHERE map_extract(map(langs, words), lang) IS NOT NULL ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE len(map_extract(map(langs, words), lang)) > 0 ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE len(map_extract(map(langs, words), lang)) > 0")

[(1,), (2,), (3,)]
[(1,)]
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #3            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (len(map_extract("map"  │
│(langs, words), lang)) > 0)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           langs           │
│           words           │
│            lang           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Pair a map 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.
• Keep a value you filter on in its own column. If queries often test one key, store its value in a column when you write the rows; a comparison on that column is evaluated in VAST DataBase.

run(f"SELECT id, map_extract(map(langs, words), 'es') FROM {TABLE} WHERE id > 1 ORDER BY id LIMIT 10")

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

Limitations and errors

• Maps only. map_extract and element_at don't work on lists: element_at([1, 2], 1) fails with 'element_at' can only operate on MAPs. For a list element use list_extract or l[i].
• Two arguments. A third fails with MAP_EXTRACT must have exactly two arguments, also when you call it as element_at.
• A table can't return a MAP column, so the maps on this page are built from list columns (data types in SQL).

run("SELECT element_at([1, 2], 1)")
run("SELECT element_at(MAP {'a': 1}, 'a', 'b')")

InternalError: Binder Error: 'element_at' can only operate on MAPs
InternalError: Binder Error: MAP_EXTRACT must have exactly two arguments

Similar functions

All for MAP {'en': 'English', 'de': 'German'}:

run("SELECT map_extract(MAP {'en': 'English', 'de': 'German'}, 'de'), "
    "map_extract_value(MAP {'en': 'English', 'de': 'German'}, 'de'), "
    "(MAP {'en': 'English', 'de': 'German'})['de'], map_contains(MAP {'en': 'English', 'de': 'German'}, 'de')")
run("SELECT map_extract(MAP {'en': 'English', 'de': 'German'}, 'fr'), "
    "map_extract_value(MAP {'en': 'English', 'de': 'German'}, 'fr'), "
    "(MAP {'en': 'English', 'de': 'German'})['fr'], map_contains(MAP {'en': 'English', 'de': 'German'}, 'fr')")

[(['German'], 'German', 'German', True)]
[([], None, None, False)]

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