--- title: "map_concat" slug: "dev-database-reference-sql-functions-maps-map-concat" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-maps-map-concat" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # map_concat *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs) › Build a map.* Merges two or more maps into one. Where a key is in more than one map, the value from the last of them wins. ## Syntax ``` map_concat(map1, map2 [, ...]) ``` | Argument | Type | Description | | --- | --- | --- | | `map1`, `map2`, ... | maps with the same key type and the same value type | The maps to merge, at least two. | **Returns:** a map of the same types as the arguments. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_maps_map_concat`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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. `langs` and `words` hold the word count of each translation; `new_langs` and `new_words` hold updated counts. Article 1 has a new count for a language it has, article 2 a new language, article 3 nothing, and the last row has no values. A table can't return a `MAP` column, so the keys and values are stored as 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_concat"' 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, "") 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[], new_langs VARCHAR[], new_words BIGINT[])") run(f"INSERT INTO {TABLE} VALUES " "(1, ['en', 'de'], [120, 135], ['de'], [140]), " "(2, ['fr', 'es'], [NULL, 140], ['it'], [90]), " "(3, [], [], [], []), " "(4, NULL, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `map_concat` starts with the pairs of the first map and adds the pairs of each next map in turn: - **A new key** is added at the end: `map_concat(MAP {'b': 1, 'a': 2}, MAP {'c': 3})` is `[('b', 1), ('a', 2), ('c', 3)]`. - **A key that's already there** gets the later value and keeps its place: `map_concat(MAP {'a': 1}, MAP {'b': 2}, MAP {'a': 3})` is `[('a', 3), ('b', 2)]`, with one `'a'`. - **Keys are compared exactly,** so `'A'` and `'a'` are two keys. - **A NULL map is skipped.** Only when every map is NULL is the result NULL. Every map must have the same key type and the same value type. A map literal such as `MAP {'de': 99}` has `INTEGER` values, so merging it with a map of `BIGINT` values from a column fails; cast the literal, as in `MAP {'de': 99}::MAP(VARCHAR, BIGINT)`. ## NULLs and edge cases | Input | Result | | --- | --- | | `map_concat(MAP {'a': 1}, NULL)`, `map_concat(NULL, MAP {'a': 1})` | `[('a', 1)]` | | `map_concat(NULL::MAP(VARCHAR, BIGINT), NULL::MAP(VARCHAR, BIGINT))` | `NULL` | | `map_concat(MAP {'a': 1}, MAP {})` | `[('a', 1)]` | | `map_concat(MAP {'A': 1}, MAP {'a': 2})` | `[('A', 1), ('a', 2)]` | | `map_concat(MAP {'a': 1}, MAP {'b': 'x'})` | Error: `'value' type of map differs between arguments, expected 'MAP(VARCHAR, INTEGER)', found 'MAP(VARCHAR, VARCHAR)' instead` | | `map_concat(MAP {'a': 1}, MAP {'a': NULL})` | Error: the same, with `found 'MAP(VARCHAR, "NULL")'`: an untyped NULL value has its own type | | `map_concat(MAP {'a': 1})`, `map_concat()` | Error: `The provided amount of arguments is incorrect, please provide 2 or more maps` | | `map_concat('abc', MAP {'a': 1})` or a list, struct or date | Error: `MAP_CONCAT only takes map arguments` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-maps-map-concat#set-up-the-examples): ``` run("SELECT map_concat(MAP {'a': 1}, NULL), map_concat(NULL, MAP {'a': 1}), " "map_concat(NULL::MAP(VARCHAR, BIGINT), NULL::MAP(VARCHAR, BIGINT))") run("SELECT map_concat(MAP {'a': 1}, MAP {}), map_concat(MAP {'A': 1}, MAP {'a': 2}), " "map_concat(MAP {'a': 1}, MAP {'b': 2}, MAP {'a': 3})") run("SELECT map_concat(MAP {'a': 1}, MAP {'b': 'x'})") run("SELECT map_concat(MAP {'a': 1}, MAP {'a': NULL})") run("SELECT map_concat(MAP {'a': 1})") run("SELECT map_concat('abc', MAP {'a': 1})") ``` ``` [([('a', 1)], [('a', 1)], None)] [([('a', 1)], [('A', 1), ('a', 2)], [('a', 3), ('b', 2)])] InternalError: Invalid Input Error: 'value' type of map differs between arguments, expected 'MAP(VARCHAR, INTEGER)', found 'MAP(VARCHAR, VARCHAR)' instead InternalError: Invalid Input Error: 'value' type of map differs between arguments, expected 'MAP(VARCHAR, INTEGER)', found 'MAP(VARCHAR, "NULL")' instead InternalError: Invalid Input Error: The provided amount of arguments is incorrect, please provide 2 or more maps InternalError: Invalid Input Error: MAP_CONCAT only takes map arguments ``` ## Examples Merge two literal maps. `'de'` is in both, so the second value wins: ``` run("SELECT map_concat(MAP {'en': 'English', 'de': 'German'}, MAP {'de': 'Deutsch', 'fr': 'French'})") ``` ``` [([('en', 'English'), ('de', 'Deutsch'), ('fr', 'French')],)] ``` Apply each article's updated counts to its current ones. Article 1's German count becomes 140, article 2 gains Italian, and article 4's NULL maps give NULL: ``` run(f"SELECT id, map_concat(map(langs, words), map(new_langs, new_words)) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, [('en', 120), ('de', 140)]), (2, [('fr', None), ('es', 140), ('it', 90)]), (3, []), (4, None)] ``` Add a constant pair to every row. The literal has `INTEGER` values, so it needs a cast to match the `BIGINT` column: ``` run(f"SELECT id, map_concat(map(langs, words), MAP {{'la': 50}}) FROM {TABLE} ORDER BY id LIMIT 10") run(f"SELECT id, map_concat(map(langs, words), MAP {{'la': 50}}::MAP(VARCHAR, BIGINT)) " f"FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` InternalError: Invalid Input Error: 'value' type of map differs between arguments, expected 'MAP(VARCHAR, BIGINT)', found 'MAP(VARCHAR, INTEGER)' instead [(1, [('en', 120), ('de', 135), ('la', 50)]), (2, [('fr', None), ('es', 140), ('la', 50)]), (3, [('la', 50)]), (4, [('la', 50)])] ``` ## 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_concat()` is always computed by the Query Engine: - **In the `SELECT` list,** the scan sends the list columns, and the Query Engine builds and merges the maps for each row it returns. - **In `WHERE`,** a `FILTER` step in the Query Engine builds and merges the maps and tests the result for every row the scan sends. No rows are skipped in storage. The plan of a lookup in the merged map shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE map_concat(map(langs, words), map(new_langs, new_words))['de'] = 140") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #4 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (map_extract_value │ │ (map_concat("map"(langs, │ │ words), "map"(new_langs, │ │ new_words)), 'de') = 140)│ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ langs │ │ words │ │ new_langs │ │ new_words │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Pair a map 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. - **Merge the lists when you write the rows.** If queries always apply the same updates, store the merged keys and values, so each query builds one map instead of two. ``` run(f"SELECT id, map_concat(map(langs, words), map(new_langs, new_words)) FROM {TABLE} " "WHERE id > 1 ORDER BY id LIMIT 10") ``` ``` [(2, [('fr', None), ('es', 140), ('it', 90)]), (3, []), (4, None)] ``` ## Limitations and errors - **The types must match exactly.** `INTEGER` and `BIGINT` values, or `VARCHAR` and `INTEGER` keys, fail with `'value' type of map differs between arguments` (the message says `'value'` for key mismatches too). Cast the maps to one type. - **At least two maps.** One map or none fails with `The provided amount of arguments is incorrect, please provide 2 or more maps`. - **Only maps.** Any other argument fails with `MAP_CONCAT only takes map arguments`; build a map first with [map](/develop/docs/dev-database-reference-sql-functions-maps-map) or [map_from_entries](/develop/docs/dev-database-reference-sql-functions-maps-map-from-entries). - **A table can't return a `MAP` column**, so the maps are built from list columns ([data types in SQL](/develop/docs/dev-database-reference-sql-data-types)). ``` run("SELECT map_concat(MAP {'a': 1}, MAP {1: 2})") run("SELECT map_concat(MAP {'a': 1}, MAP {'b': 2.5})") run("SELECT map_concat(MAP {'a': 1}, [1])") ``` ``` InternalError: Invalid Input Error: 'value' type of map differs between arguments, expected 'MAP(VARCHAR, INTEGER)', found 'MAP(INTEGER, INTEGER)' instead InternalError: Invalid Input Error: 'value' type of map differs between arguments, expected 'MAP(VARCHAR, INTEGER)', found 'MAP(VARCHAR, DECIMAL(2,1))' instead InternalError: Invalid Input Error: MAP_CONCAT only takes map arguments ``` ## Similar functions | Function | What it does | Example | | --- | --- | --- | | `map_concat` | merges maps; the last value for a key wins | `map_concat(MAP {'a': 1}, MAP {'a': 3, 'b': 2})` | | [map](/develop/docs/dev-database-reference-sql-functions-maps-map) | builds a map from keys and values; duplicate keys fail | `map(['a', 'a'], [1, 3])` | | [list_concat](/develop/docs/dev-database-reference-sql-functions-lists-list-concat) on the entries | joins the pairs; duplicate keys fail in [map_from_entries](/develop/docs/dev-database-reference-sql-functions-maps-map-from-entries) | `map_from_entries(list_concat(map_entries(MAP {'a': 1}), map_entries(MAP {'a': 3})))` | ``` run("SELECT map_concat(MAP {'a': 1}, MAP {'a': 3, 'b': 2})") run("SELECT map(['a', 'a'], [1, 3])") run("SELECT map_from_entries(list_concat(map_entries(MAP {'a': 1}), map_entries(MAP {'a': 3})))") ``` ``` [([('a', 3), ('b', 2)],)] OSError: Map keys must be unique. OSError: Map keys must be unique. ``` 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](/develop/docs/dev-database-reference-sql-functions-lists-and-structs): all the map functions. - [map_keys](/develop/docs/dev-database-reference-sql-functions-maps-map-keys): the keys of a merged map, in order. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.