map_contains_entry

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Search a map. Returns true if a map has a key and that key has a given value, false otherwise.

Syntax

map_contains_entry(map, key, value)

Returns: BOOLEAN.

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_entry. 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 and a number n to look up. Row 2 has a NULL value, 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_entry"'

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_entry is true if the map has key and the value of key equals value:

• Key and value together. In MAP {'en': 'English', 'de': 'German'}, the entry 'de', 'German' is found, but 'de', 'English' isn't, although 'de' is a key and 'English' is a value.
• Typed comparison. Numbers of different types compare by value: map_contains_entry(MAP {'a': 1}, 'a', 1.0) is true. A key or value of a type that doesn't convert fails (see Limitations and errors).
• NULL matches NULL. Unlike map_contains and map_contains_value, a NULL key or value doesn't make the result NULL: a NULL value finds a key whose value is NULL, and is false otherwise.

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_entry(NULL::MAP(VARCHAR, BIGINT), 'a', 1), map_contains_entry(NULL, NULL, NULL), "
    "map_contains_entry(MAP {'a': 1}, NULL, 1), map_contains_entry(MAP {'a': 1}, 'a', NULL), "
    "map_contains_entry(MAP {'a': NULL::BIGINT}, 'a', NULL)")
run("SELECT map_contains_entry(MAP {}, 'a', 1), map_contains_entry(MAP {'en': 'English', 'de': 'German'}, 'fr', 'French'), "
    "map_contains_entry(MAP {'a': 1}, 'a', 1.0)")
run("SELECT map_contains_entry(MAP {'a': 1}, 'a', '1')")

[(None, None, False, False, True)]
[(False, False, True)]
InternalError: Binder Error: contains: Cannot match element of type 'STRUCT("key" VARCHAR, "value" VARCHAR)' in a list of type 'STRUCT("key" VARCHAR, "value" INTEGER)[]' - an explicit cast is required

Examples

Whether each row's map has the key 'de' with the value 2, and whether it has the row's own code with the value n. Only row 1 has 'de' with 2; no row's map has its code with its n; row 4's lists are NULL, so its map and the answers are NULL:

run(f"SELECT id, map_contains_entry(map(codes, counts), 'de', 2), map_contains_entry(map(codes, counts), code, n) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

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

Keep only the rows whose map has the key 'de' with the value 2:

run(f"SELECT id FROM {TABLE} WHERE map_contains_entry(map(codes, counts), 'de', 2) 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 its entries, row by row:

• In the SELECT list, the Query Engine builds the map and computes map_contains_entry() 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_entry(map(codes, counts), 'de', 2)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ contains(map_entries("map"│
│ (codes, counts)), {'key': │
│     'de', 'value': 2})    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           codes           │
│           counts          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• 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_entry(map(codes, counts), 'de', 2)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ contains(map_entries("map"│
│ (codes, counts)), {'key': │
│     'de', 'value': 2})    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│           codes           │
│           counts          │
│                           │
└───────────────────────────┘

• Keep a pair you filter on often in its own columns. A condition such as code = 'de' AND n = 10 compares plain columns with constants, which VAST DataBase evaluates; a search of a map built in the query runs in the Query Engine.

Limitations and errors

• Three arguments. With two, it fails with Macro function map_contains_entry(map, key, value) requires 3 positional arguments, but 2 positional arguments were provided. To search only the keys, use map_contains.
• The first argument must be a map. A list, string, number, date or struct fails with The provided argument is not a map.
• The key and value must have the map's types. A key or value of a type that doesn't convert fails with contains: Cannot match element of type 'STRUCT(...)' in a list of type 'STRUCT(...)[]' - an explicit cast is required; the error names the entry you passed and the map's entries as structs of key and value. Cast the key or value to the map's types.

run("SELECT map_contains_entry(MAP {'a': 1}, 'a')")
run("SELECT map_contains_entry([1], 1, 1)")
run("SELECT map_contains_entry(MAP {'a': 1}, 1, 1)")

InternalError: Binder Error: Macro function map_contains_entry(map, key, value) requires 3 positional arguments, but 2 positional arguments were provided.
InternalError: Invalid Input Error: The provided argument is not a map
InternalError: Binder Error: contains: Cannot match element of type 'STRUCT("key" INTEGER, "value" INTEGER)' in a list of type 'STRUCT("key" VARCHAR, "value" INTEGER)[]' - an explicit cast is required

Similar functions

run("SELECT map_contains_entry(MAP {'en': 'English', 'de': 'German'}, 'de', 'English'), "
    "map_contains(MAP {'en': 'English', 'de': 'German'}, 'de') "
    "AND map_contains_value(MAP {'en': 'English', 'de': 'German'}, 'English'), "
    "map_extract_value(MAP {'en': 'English', 'de': 'German'}, 'de') = 'German', "
    "map_contains_entry(MAP {'en': 'English', 'de': 'German'}, 'de', 'German')")

[(False, True, True, True)]

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_entries: a map's entries as a list of structs.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.