map_keys

Applies to VAST Cluster 5.5.

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

Syntax

map_keys(m)

Returns: a list of the map's key 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_keys. 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_keys"'

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_keys(m) lists the keys of m in the map's own order: the order the pairs were given in when the map was built. map_keys(MAP {'b': 1, 'a': 2}) is ['b', 'a']. When map_concat replaces a key's value, the key keeps its place. To get the keys in sorted order, wrap the result in list_sort.

The list has one element per pair, so len(map_keys(m)) is the number of pairs, the same as cardinality. The values come in the same order from map_values.

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

[(None, None, [], [])]
[(['b', 'a'], ['b', 'a'], [1, 2])]
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 keys of a literal map, and sorted:

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

[(['en', 'de'], ['de', 'en'])]

The keys of each article's map. Article 3's empty map gives [] and article 4's NULL map gives NULL:

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

[(1, ['en', 'de']), (2, ['fr', 'es']), (3, []), (4, None)]

The keys of a merged map, and one row per key with unnest:

run(f"SELECT id, map_keys(map_concat(map(langs, words), MAP {{'la': 50}}::MAP(VARCHAR, BIGINT))) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT unnest(map_keys(MAP {'a': 1, 'b': 2})) AS k")

[(1, ['en', 'de', 'la']), (2, ['fr', 'es', 'la']), (3, ['la']), (4, ['la'])]
[('a',), ('b',)]

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_keys() 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 keys 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 keys shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE list_contains(map_keys(map(langs, words)), 'de')")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  list_contains(map_keys(  │
│"map"(langs, words)), 'de')│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           langs           │
│           words           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Use the key column itself. When the map is built from a list of keys, map_keys(map(langs, words)) is langs: test langs directly, as in list_contains(langs, 'de'), and the Query Engine doesn't build a map. The condition on langs still runs in a FILTER step, as the plan below shows; the id > 1 condition doesn't, because it is evaluated in VAST DataBase.
• 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 FROM {TABLE} WHERE list_contains(langs, 'de') ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE id > 1 AND list_contains(langs, 'de')")

[(1,)]
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ list_contains(langs, 'de')│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│           langs           │
│                           │
└───────────────────────────┘

Limitations and errors

• Maps only. A list, a struct or any other value fails with The provided argument is not a map. For the field names of a struct, convert it to JSON and use json_keys.
• 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_keys({'a': 1})")
run("SELECT json_keys(to_json({'a': 1}))")

InternalError: Invalid Input Error: The provided argument is not a map
[(['a'],)]

Similar functions

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

[(['en', 'de'], ['English', 'German'], [{'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.