map

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Build a map. Builds a map from a list of keys and a list of values of the same length. The literal MAP {key: value, ...} builds one from pairs written in the query.

Syntax

map(keys, values)
map()
MAP {key: value [, ...]}
MAP {}

Returns: MAP(key type, value type), such as MAP(VARCHAR, INTEGER). An empty map is MAP("NULL", "NULL").

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

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(keys, values) pairs the first key with the first value, the second key with the second value, and so on. MAP {'a': 1, 'b': 2} and map(['a', 'b'], [1, 2]) are the same map. Python receives a map as a list of (key, value) tuples: [('a', 1), ('b', 2)].

• Order is kept. The map holds its pairs in the order you give them: map(['b', 'a'], [2, 1]) is [('b', 2), ('a', 1)]. Two maps with the same pairs in a different order aren't equal.
• Keys are compared exactly. 'A' and 'a' are two keys, and so are é written as one code point and e followed by a combining accent (U+0301). 0.0 and -0.0 are the same key, and so are two NaN values.
• Keys and values can be of any type. Numbers, dates, booleans, lists and structs work as keys; values can be lists, structs or maps.
• All keys have one type, and all values have one type. A value that can't be converted to the type of the first one fails, such as MAP {'a': 1, 'b': 'x'}. Cast a map to change its types: MAP {'a': 1}::MAP(VARCHAR, DOUBLE).
• As text, a map cast to VARCHAR is {a=1}; to_json turns it into a JSON object.

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(NULL, NULL), map(['a'], NULL), map(), map([], []), MAP {}")
run("SELECT map(['a'], [NULL]), MAP {'a': NULL}, map(['A', 'a'], [1, 2]), map(['\u00e9', 'e\u0301'], [1, 2])")
run("SELECT MAP {'a': 1, 'b': 2} = MAP {'b': 2, 'a': 1}, typeof(map()), typeof(MAP {'a': 1, 'b': 2})")
run("SELECT map(['a', 'a'], [1, 2])")
run("SELECT map([0.0, -0.0], [1, 2])")
run("SELECT map(['a'], [1, 2])")
run("SELECT MAP {NULL: 1}")
run("SELECT MAP {'a': 1, 'b': 'x'}")
run("SELECT map('a', 1)")

[(None, None, [], [], [])]
[([('a', None)], [('a', None)], [('A', 1), ('a', 2)], [('é', 1), ('é', 2)])]
[(False, 'MAP("NULL", "NULL")', 'MAP(VARCHAR, INTEGER)')]
OSError: Map keys must be unique.
OSError: Map keys must be unique.
OSError: The map key list does not align with the map value list.
OSError: Map keys can not be NULL.
OSError: Could not convert string 'x' to INT32
InternalError: Invalid Input Error: Invalid map argument(s). Valid map arguments are a list of key-value pairs (MAP {'key1': 'val1', ...}), two lists (MAP ([1, 2], [10, 11])), or no arguments.

Examples

Build a map per article from its two list columns. Article 2's French translation has a NULL word count, article 3's lists are empty, so its map is empty, and the NULL lists of article 4 give NULL:

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

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

Look up the language each reader asked for, with m[key] (map_extract_value). A key that isn't in the map gives NULL:

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

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

Keep the articles whose German translation has 135 words:

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

[(1,)]

Literals, keys and values of other types, and a map as text and as JSON:

run("SELECT MAP {'en': 'English', 'de': 'German'}, map([1, 2], ['x', 'y']), map(['k'], [[1, 2]])")
run("SELECT map([DATE '2026-10-16'], ['d']), map([true, false], [1, 0]), MAP {'a': 1}::MAP(VARCHAR, DOUBLE)")
run("SELECT CAST(MAP {'a': 1} AS VARCHAR), to_json(MAP {'a': 1, 'b': 2})")

[([('en', 'English'), ('de', 'German')], [(1, 'x'), (2, 'y')], [('k', [1, 2])])]
[([(datetime.date(2026, 10, 16), 'd')], [(True, 1), (False, 0)], [('a', 1.0)])]
[('{a=1}', '{"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() is computed by the Query Engine:

• In the SELECT list, the scan sends langs and words, and the Query Engine builds the map for each row it returns.
• In WHERE, a FILTER step in the Query Engine builds the map and tests it for every row the scan sends. No rows are skipped in storage.

The plan of the WHERE example 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, so it has no FILTER step; a test on 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.
• Filter on plain columns where you can. A condition on a plain column, such as lang = 'de' above, is evaluated in VAST DataBase. The second statement returns the articles with id > 1, each with its map.

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

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

Limitations and errors

• A table can't return a MAP column. A MAP column can be created and written, but a query that returns it fails with Unsupported column type, and CREATE TABLE ... AS SELECT with a map fails with Not implemented Error: not supported (data types in SQL). Store the keys and values as two list columns, as on this page, or the pairs as a list of structs (map_entries).
• One bad row fails the whole query. Duplicate keys, a NULL key or lists of different lengths in any row stop the query. Here lang is NULL in the last row; leave such rows out with WHERE.
• No aggregate builds a map. map(list(k), list(v)) over a table fails, because list() needs a grouping step that isn't available (aggregate functions), and there's no map_agg. Build the map in your code from the rows.
• Not available: map_from_arrays (use map(keys, values)) and str_to_map (split the string, then use map_from_entries).

run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.attrs (id BIGINT, m MAP(VARCHAR, BIGINT))")
run(f"INSERT INTO {SCHEMA}.attrs VALUES (1, MAP {{'a': 1}})")
run(f"SELECT * FROM {SCHEMA}.attrs")
run(f"SELECT id, map([lang], [id]) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, map([lang], [id]) FROM {TABLE} WHERE lang IS NOT NULL ORDER BY id LIMIT 10")
run(f"SELECT map(list(lang), list(id)) FROM {TABLE}")
run("SELECT map_from_arrays(['a'], [1])")

[]
[(1,)]
InternalError: Unsupported column type 'MAP(VARCHAR, BIGINT)' of column 'm' in 'VAST_SCAN '.
OSError: Map keys can not be NULL.
[(1, [('de', 1)]), (2, [('it', 2)]), (3, [('', 3)])]
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: Catalog Error: Scalar Function with name map_from_arrays does not exist!

Similar functions

The first four give the same map:

run("SELECT map(['a', 'b'], [1, 2]), MAP {'a': 1, 'b': 2}, "
    "map_from_entries([{'k': 'a', 'v': 1}, {'k': 'b', 'v': 2}]), map_concat(MAP {'a': 1}, MAP {'b': 2})")
run("SELECT list_histogram(['a', 'b', 'a']), json_transform('{\"a\": 1, \"b\": 2}', '\"MAP(VARCHAR, BIGINT)\"')")

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

Clean up:

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

[]
[]
[]

See also

• List, struct and map functions: all the map functions.
• Data types in SQL: which column types a table can return.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.