map_values

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Keys, values and entries. Returns the values of a map as a list, in the order the map holds them.

Syntax

map_values(m)

Returns: a list of the map's value type, such as 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_maps_map_values. 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 and the word count of each translation. Article 2 has a translation with no word count yet, 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_values"'

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

[]
[]
[(4,)]

What it does

map_values(m) lists the values of m in the map's own order, the same order as map_keys: the nth value belongs to the nth key. map_values(MAP {'b': 1, 'a': 2}) is [1, 2]. NULL values stay in the list, as None in Python, and values that are lists stay lists, so the result is a list of lists.

The result is an ordinary list, so the list aggregate functions work on it: list_sum(map_values(m)) adds the values up.

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_values(NULL::MAP(VARCHAR, BIGINT)), map_values(NULL), map_values(MAP {}), map_values(map())")
run("SELECT map_values(MAP {'a': NULL, 'b': 2}), map_values(MAP {'b': 1, 'a': 2}), "
    "map_values(MAP {'a': [1], 'b': [2, 3]})")
run("SELECT map_values([1, 2])")
run("SELECT map_values(MAP {'a': 1}, MAP {'b': 2})")
run("SELECT map_values()")

[(None, None, [], [])]
[([None, 2], [1, 2], [[1], [2, 3]])]
InternalError: Invalid Input Error: The provided argument is not a map
InternalError: Invalid Input Error: Too many arguments provided, only expecting a single map
InternalError: Invalid Input Error: Too many arguments provided, only expecting a single map

Examples

The values of a literal map, and their sum. list_sum of INTEGER values returns a DECIMAL, which Python shows as Decimal:

run("SELECT map_values(MAP {'en': 'English', 'de': 'German'}), list_sum(map_values(MAP {'a': 1, 'b': 2}))")

[(['English', 'German'], Decimal('3'))]

The values of each article's map. Article 2's French translation has no word count, so its first value is NULL:

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

[(1, [120, 135]), (2, [None, 140]), (3, []), (4, None)]

One row per pair, with the keys and values side by side:

run("SELECT unnest(map_keys(MAP {'a': 1, 'b': 2})) AS k, unnest(map_values(MAP {'a': 1, 'b': 2})) AS v")

[('a', 1), ('b', 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). map_values() 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 lists its values 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 plan of a condition on the values shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE map_values(map(langs, words)) IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (map_values("map"(langs, │
│    words)) IS NOT NULL)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           langs           │
│           words           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Use the value column itself. When the map is built from two lists, map_values(map(langs, words)) has the same elements as words, so a query that needs only the values can use words and skip building the map. Building the map also checks the keys: a row with duplicate or NULL keys fails the query, and words alone doesn't.
• Pair a list 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.

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

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

Limitations and errors

• Maps only. A list, a struct or any other value fails with The provided argument is not a map.
• One argument. Two arguments, or none, fail with Too many arguments provided, only expecting a single map.
• 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 map_values({'a': 1})")

InternalError: Invalid Input Error: The provided argument is not a map

Similar functions

run("SELECT map_values(MAP {'en': 'English', 'de': 'German'}), map_keys(MAP {'en': 'English', 'de': 'German'}), "
    "map_entries(MAP {'en': 'English', 'de': 'German'}), cardinality(MAP {'en': 'English', 'de': 'German'})")

[(['English', 'German'], ['en', 'de'], [{'key': 'en', 'value': 'English'}, {'key': 'de', 'value': 'German'}], 2)]

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.