map_entries

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Keys, values and entries. Returns the pairs of a map as a list of structs with two fields, key and value, in the order the map holds them.

Syntax

map_entries(m)

Returns: STRUCT(key K, value V)[], where K and V are the map's key and value types.

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_entries. 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_entries"'

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_entries(m) turns each pair of m into a struct {'key': ..., 'value': ...}, in the map's own order. map_entries(MAP {'a': 1}) is [{'key': 'a', 'value': 1}], and Python receives each struct as a dictionary.

The result is an ordinary list of structs, so you can:

• read a pair: map_entries(m)[1].key, map_entries(m)[1].value or map_entries(m)[1]['key'];
• filter or change the pairs with list_filter and list_transform, and turn the result back into a map with map_from_entries;
• get one row per pair with unnest;
• store a map in a table, in a column of type STRUCT(key ..., value ...)[]: a table can't return a MAP column, but it can return this one.

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_entries(NULL::MAP(VARCHAR, BIGINT)), map_entries(NULL), map_entries(MAP {}), map_entries(map())")
run("SELECT map_entries(MAP {'b': 1, 'a': 2}), map_entries(MAP {'a': 1})[1].key, map_entries(MAP {'a': 1})[1]['key'], "
    "map_entries(MAP {'a': 1})[1].value")
run("SELECT map_entries([1, 2])")
run("SELECT map_entries(MAP {'a': 1}, MAP {'b': 2})")
run("SELECT map_entries()")

[(None, None, [], [])]
[([{'key': 'b', 'value': 1}, {'key': 'a', 'value': 2}], 'a', 'a', 1)]
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 pairs of each article's map:

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

[(1, [{'key': 'en', 'value': 120}, {'key': 'de', 'value': 135}]), (2, [{'key': 'fr', 'value': None}, {'key': 'es', 'value': 140}]), (3, []), (4, None)]

Format the pairs as text, keep the pairs whose value is above 1, and remove a key:

run("SELECT list_transform(map_entries(MAP {'a': 1, 'b': 2}), e -> e.key || '=' || e.value)")
run("SELECT map_from_entries(list_filter(map_entries(MAP {'a': 1, 'b': 2}), e -> e.value > 1))")
run("SELECT map_from_entries(list_filter(map_entries(MAP {'a': 1, 'b': 2}), e -> e.key <> 'a'))")

[(['a=1', 'b=2'],)]
[([('b', 2)],)]
[([('b', 2)],)]

One row per pair, and the pairs as JSON:

run("SELECT unnest(map_entries(MAP {'a': 1, 'b': 2})) AS e")
run("SELECT to_json(map_entries(MAP {'a': 1}))")

[({'key': 'a', 'value': 1},), ({'key': 'b', 'value': 2},)]
[('[{"key":"a","value":1}]',)]

Store a map in a table as its entries, and read it back as a map:

STORED = f"{SCHEMA}.stored"
run(f"CREATE TABLE IF NOT EXISTS {STORED} (id BIGINT, m STRUCT(key VARCHAR, value BIGINT)[])")
run(f"INSERT INTO {STORED} SELECT 1, map_entries(MAP {{'a': 1, 'b': 2}})")
run(f"SELECT id, map_from_entries(m), map_from_entries(m)['b'] FROM {STORED}")

[]
[(1,)]
[(1, [('a', 1), ('b', 2)], 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_entries() 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 pairs 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 entries shows the FILTER step above the scan:

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

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

Make it faster

• 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.
• Store a value you filter on in its own column. A condition on map_entries(...) of a map built in the query runs in a FILTER step in the Query Engine, as the plan above shows; a comparison on a plain column is evaluated in VAST DataBase.

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

[(2, [{'key': 'fr', 'value': None}, {'key': 'es', 'value': 140}]), (3, []), (4, 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.
• No map functions with lambdas. map_filter, transform_keys and transform_values aren't available; use map_entries with a list lambda and map_from_entries, as in the examples.

run("SELECT map_entries({'a': 1})")
run("SELECT transform_values(MAP {'a': 1}, (k, v) -> v * 10)")
run("SELECT map_from_entries(list_transform(map_entries(MAP {'a': 1}), e -> {'key': e.key, 'value': e.value * 10}))")

InternalError: Invalid Input Error: The provided argument is not a map
InternalError: Catalog Error: Scalar Function with name transform_values does not exist!
[([('a', 10)],)]

Similar functions

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

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

Clean up:

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

[]
[]
[]

See also

• List, struct and map functions: all the map functions.
• map_from_entries: build a map from a list of structs.
• Data types in SQL: which column types a table can return.
• EXPLAIN: how to read a plan.