map_contains

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Search a map. Returns true if a map has a key, false if it doesn't. contains with a map as its first argument is the same function.

Syntax

map_contains(map, key)
contains(map, key)

Returns: BOOLEAN.

Aliases: contains, when its first argument is a map.

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_contains. 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.

A table can't return a MAP column, so the examples keep each map as two list columns, codes (the keys) and counts (the values), and build the map in the query with map(codes, counts). This block connects, defines the run() and plan() helpers, and creates a table of five rows, each with a code to look up. Row 3 has empty lists (an empty map) and row 4 has no values:

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

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}.langs"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, codes VARCHAR[], counts BIGINT[], code VARCHAR, n BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['en', 'de'], [1, 2], 'de', 10), "
    "(2, ['\u00e9', '\u00fc'], [NULL, 3], 'fr', -5), "
    "(3, [], [], '', 0), "
    "(4, NULL, NULL, NULL, NULL), "
    "(5, ['k1', 'k2', 'k3'], [10, 20, 30], 'k3', 2147483647)")

[]
[]
[(5,)]

What it does

map_contains is true if key is one of the map's keys:

• Keys only. The values aren't searched. To search the values, use map_contains_value; for a key with a given value, map_contains_entry.
• Exact comparison. String keys must match exactly, so the search is case-sensitive: 'EN' doesn't find 'en'.
• A key with a NULL value is still a key. map_contains(MAP {'a': NULL}, 'a') is true.
• The key must have the map's key type. 1.5 finds the DECIMAL key 1.5, but a string in a map with INTEGER keys, or a number in a map with VARCHAR keys, fails (see Limitations and errors).

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_contains(NULL::MAP(VARCHAR, BIGINT), 'a'), map_contains(MAP {'a': 1}, NULL), "
    "map_contains(NULL, NULL), map_contains(MAP {'a': NULL}, 'a'), map_contains(MAP {}, 'a')")
run("SELECT map_contains(MAP {'en': 'English', 'de': 'German'}, 'EN'), map_contains(MAP {1: 'x'}, 1), "
    "map_contains(MAP {1.5: 'x'}, 1.5), contains(MAP {'en': 'English', 'de': 'German'}, 'en')")
run("SELECT map_contains(MAP {1: 'x'}, '1')")

[(None, None, None, True, False)]
[(False, True, True, True)]
InternalError: Binder Error: map_contains: Cannot match element of type 'VARCHAR' in a map of type 'MAP(INTEGER, VARCHAR)' - an explicit cast is required

Examples

Whether each row's map has the row's code as a key. Row 3's map is empty, so the answer is false; row 4's lists are NULL, so its map and the answer are NULL:

run(f"SELECT id, map_contains(map(codes, counts), code), contains(map(codes, counts), code) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, True, True), (2, False, False), (3, False, False), (4, None, None), (5, True, True)]

Keep only the rows whose map has the key 'de':

run(f"SELECT id FROM {TABLE} WHERE map_contains(map(codes, counts), 'de') ORDER BY id LIMIT 10")

[(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). A map exists only inside the query: the scan sends the list columns, and the Query Engine builds the map and searches it, row by row:

• In the SELECT list, the Query Engine builds the map and computes map_contains() for each row it returns.
• In WHERE, the scan sends the lists for every row, and a FILTER step in the Query Engine builds the map, searches it and keeps the rows that match. 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_contains(map(codes, counts), 'de')")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ map_contains("map"(codes, │
│       counts), 'de')      │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           codes           │
│           counts          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Search the key list instead. When the keys are in a list column, list_contains(codes, 'de') returns the same rows without building the map. It still runs in a FILTER step:

run(f"SELECT id FROM {TABLE} WHERE list_contains(codes, 'de') ORDER BY id LIMIT 10")

[(1,)]

• Pair it with a pushed-down condition. 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:

plan(f"SELECT id FROM {TABLE} WHERE id > 1 AND map_contains(map(codes, counts), 'de')")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ map_contains("map"(codes, │
│       counts), 'de')      │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│           codes           │
│           counts          │
│                           │
└───────────────────────────┘

• Keep a key you filter on often in its own column. A comparison such as code = 'de' is pushed down to VAST DataBase, and its plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE code = 'de'")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Limitations and errors

• The first argument must be a map. A list, string, number, date or struct fails with No function matches the given name and argument types, for example 'map_contains(INTEGER[], INTEGER_LITERAL)'. The error lists the one form: map_contains(MAP(ANY, ANY), ANY) -> BOOLEAN.
• The key must have the map's key type. A string in a map with INTEGER keys, or a number in a map with VARCHAR keys, fails with Cannot match element of type ... - an explicit cast is required. Cast the key to the map's key type.
• contains has other forms. contains also takes a list (is a value an element) and a string (is one string inside another), so contains(NULL, NULL) fails with Could not choose a best candidate function for the function call. Use map_contains.
• A table can't return a MAP column. A query that reads one fails with Unsupported column type. Store the keys and values as two list columns, or as a list of structs that map_from_entries turns into a map (data types in SQL).
• Other SQL dialects' names aren't available. map_contains_key fails with Scalar Function with name map_contains_key does not exist!. Use map_contains.

run("SELECT map_contains([1, 2], 1)")
run("SELECT contains([1, 2], 1), contains('abc', 'b')")
run("SELECT contains(NULL, NULL)")
run("SELECT map_contains(MAP {'a': 1}, 1)")
run("SELECT map_contains(MAP {1: 'x'}, CAST('1' AS INTEGER))")
run("SELECT map_contains_key(MAP {'a': 1}, 'a')")

InternalError: Binder Error: No function matches the given name and argument types 'map_contains(INTEGER[], INTEGER_LITERAL)'. You might need to add explicit type casts.
[(True, True)]
InternalError: Binder Error: Could not choose a best candidate function for the function call "contains("NULL", "NULL")". In order to select one, please add explicit type casts.
InternalError: Binder Error: map_contains: Cannot match element of type 'INTEGER' in a map of type 'MAP(VARCHAR, INTEGER)' - an explicit cast is required
[(True,)]
InternalError: Catalog Error: Scalar Function with name map_contains_key does not exist!

Similar functions

run("SELECT map_contains(MAP {'en': 'English', 'de': 'German'}, 'de'), "
    "map_contains_value(MAP {'en': 'English', 'de': 'German'}, 'German'), "
    "map_contains_entry(MAP {'en': 'English', 'de': 'German'}, 'de', 'German'), "
    "list_contains(map_keys(MAP {'en': 'English', 'de': 'German'}), 'de'), "
    "map_extract_value(MAP {'en': 'English', 'de': 'German'}, 'de') IS NOT NULL")

[(True, True, True, True, True)]

The last two differ for a key whose value is NULL: map_contains is true, while map_extract_value(...) IS NOT NULL is false, and map_extract returns a list holding one NULL:

run("SELECT map_contains(MAP {'a': NULL::BIGINT}, 'a'), map_extract_value(MAP {'a': NULL::BIGINT}, 'a') IS NOT NULL, "
    "map_extract(MAP {'a': NULL::BIGINT}, 'a')")

[(True, False, [None])]

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 from two lists.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.