map_extract_value

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, or NULL if the key isn't in the map. The subscript m[key] is the same.

Syntax

map_extract_value(m, key)
m[key]

Returns: the map's value type, such as VARCHAR.

Aliases: the subscript m[key].

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

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_value(m, key) and m[key] return the value stored under key. The result is NULL in three cases: the key isn't in the map, its value is NULL, or the map is NULL. To tell these apart, use map_extract, which returns [] for a missing key and [None] for a NULL value, or map_contains.

• Keys are compared exactly. 'DE' doesn't find 'de'.
• The key is cast to the map's key type. In a map with INTEGER keys, '1' finds the key 1. A key that doesn't match, such as 1 in a map whose keys are strings like 'a', gives NULL.
• The result can be used directly: in arithmetic (MAP {'a': 1}['a'] + 1 is 2), with another subscript for a nested map or a list (MAP {'a': [5, 6]}['a'][2] is 6), or with .field for a struct value.

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_value(MAP {'a': 1}, NULL), (MAP {'a': 1})[NULL], "
    "map_extract_value(NULL::MAP(VARCHAR, BIGINT), 'a'), map_extract_value(MAP {'a': NULL::BIGINT}, 'a'), "
    "map_extract_value(MAP {}, 'a')")
run("SELECT (MAP {'en': 'English', 'de': 'German'})['DE'], MAP {'a': 1}[1], MAP {1: 'x'}['1'], "
    "MAP {'a': MAP {'b': 2}}['a']['b']")
run("SELECT MAP {'a': 1}['a':'b']")
run("SELECT map_extract_value([1, 2], 1)")
run("SELECT map_extract_value(MAP {'a': 1}, 'a', 'b')")

[(None, None, None, None, None)]
[(None, None, 'x', 2)]
InternalError: Binder Error: ARRAY_SLICE can only operate on LISTs and VARCHARs
InternalError: Binder Error: 'map_extract_value' can only operate on MAPs
InternalError: Binder Error: MAP_EXTRACT must have exactly two arguments

Examples

The function and the subscript give the same value; a missing key gives NULL:

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

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

Look up the language each reader asked for. Only article 1 has it; the others give NULL:

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

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

Keep the articles whose German translation has more than 100 words, and use the value in arithmetic:

run(f"SELECT id, map(langs, words)['de'] - map(langs, words)['en'] FROM {TABLE} "
    "WHERE map(langs, words)['de'] > 100 ORDER BY id LIMIT 10")

[(1, 15)]

Values that are lists or structs:

run("SELECT map_extract_value(MAP {'a': [1, 2]}, 'a'), MAP {'a': [5, 6]}['a'][2], "
    "map_extract_value(MAP {'a': {'x': 1}}, 'a').x")

[([1, 2], 6, 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 lookup 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 plan of a lookup in WHERE shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE map(langs, words)['de'] = 135")

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

Make it faster

• Keep a value you filter on in its own column. A comparison of a plain column with a constant, such as lang = 'de', is evaluated in VAST DataBase and has no FILTER step; a lookup in a map built in the query always has one. If queries often filter on one key, store its value in a column when you write the rows.
• Pair a map condition with a pushed-down one, such as id > 1, so the rows it rules out never reach the FILTER step.

plan(f"SELECT id FROM {TABLE} WHERE lang = 'de'")
run(f"SELECT id, map(langs, words)['es'] FROM {TABLE} WHERE id > 1 ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(2, 140), (3, None), (4, None)]

Limitations and errors

• NULL doesn't say why. A missing key, a NULL value and a NULL map all give NULL; see What it does.
• One key at a time. m['a', 'b'] is a syntax error, and a slice m['a':'b'] fails with ARRAY_SLICE can only operate on LISTs and VARCHARs.
• The subscript works on other types too. On a list, a string or a struct, x[...] is a list, string or struct lookup, with that type's errors: a string key on a list or a date fails with No function matches the given name and argument types 'array_extract(...)', and a key from a column on a struct fails with Key name for struct_extract needs to be a constant string.
• Not available: map_get; use map_extract_value or m[key].
• A table can't return a MAP column, so the maps on this page are built from list columns (data types in SQL).

run(f"SELECT ([1, 2])[lang] FROM {TABLE} LIMIT 1")
run(f"SELECT ({{'a': 1}})[lang] FROM {TABLE} LIMIT 1")
run("SELECT map_get(MAP {'a': 1}, 'a')")

InternalError: Binder Error: No function matches the given name and argument types 'array_extract(INTEGER[], VARCHAR)'. You might need to add explicit type casts.
InternalError: Binder Error: Key name for struct_extract needs to be a constant string
InternalError: Catalog Error: Scalar Function with name map_get does not exist!

Similar functions

On a struct, the same subscript reads a field (struct_extract), and the field name must be a constant. The first two lines are for MAP {'en': 'English', 'de': 'German'}; the last compares a map and a struct:

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

[('German', ['German'], True)]
[(None, [], False)]
[(1, 1, 'MAP(VARCHAR, INTEGER)', 'STRUCT(a INTEGER, b INTEGER)')]

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.