32.4. MAP Functions

32.4. MAP Functions
Prev	Up	Chapter 32. Functions	Home	Next

32.4. MAP Functions #

cardinality(map_col duckdb.map) returns numeric #

Returns the size of a map (the number of key-value pairs).

Example 32.7.

-- Get the number of entries in a map
SELECT cardinality(r['map_col']) as size
FROM duckdb.query($$ SELECT MAP(['a', 'b', 'c'], [1, 2, 3]) as map_col $$) r;
-- Returns: 3

-- Empty map
SELECT cardinality(r['map_col']) as size
FROM duckdb.query($$ SELECT MAP([], []) as map_col $$) r;
-- Returns: 0

Required parameters:

Name	Type	Description
`map_col`	`duckdb.map`	The map, the size of which to return

element_at(map_col duckdb.map, key duckdb.unresolved_type) returns duckdb.unresolved_type #

Returns the value for a given key as an array.

Example 32.8.

-- Get value for a specific key
SELECT element_at(r['map_col'], 'a') as value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: {1}

-- Non-existent key
SELECT element_at(r['map_col'], 'c') as value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: {}

Required parameters:

Name	Type	Description
`map_col`	`duckdb.map`	The map from which to extract a value
`key`	`duckdb.unresolved_type`	The key for which to extract a value

map_concat(map_col duckdb.map, map_col2 duckdb.map) returns duckdb.map #

Merges two maps. On key collision, the value is taken from the last map.

Example 32.9.

-- Merge two maps
SELECT map_concat(r1['map1'], r2['map2']) as merged
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map1 $$) r1,
     duckdb.query($$ SELECT MAP(['b', 'c'], [3, 4]) as map2 $$) r2;
-- Returns: {a=1, b=3, c=4}

-- Note: 'b' value from map2 (3) overwrites map1's value (2)

Required parameters:

Name	Type	Description
`map_col`	`duckdb.map`	The first map to merge
`map_col2`	`duckdb.map`	The second map to merge

map_contains(map_col duckdb.map, key duckdb.unresolved_type) returns boolean #

Checks whether a map contains a given key.

Example 32.10.

-- Check if key exists
SELECT map_contains(r['map_col'], 'a') as has_key
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: t (true)

-- Check for non-existent key
SELECT map_contains(r['map_col'], 'c') as has_key
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: f (false)

Required parameters:

Name	Type	Description
`map_col`	`duckdb.map`	The map where to check a key
`key`	`duckdb.unresolved_type`	The key to check

map_contains_entry(map_col duckdb.map, key duckdb.unresolved_type, value duckdb.unresolved_type) boolean #

Checks whether a map contains a given key-value pair.

Example 32.11.

-- Check if key-value pair exists
SELECT map_contains_entry(r['map_col'], 'a', 1) as has_entry
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: t (true)

-- Check with wrong value for existing key
SELECT map_contains_entry(r['map_col'], 'a', 2) as has_entry
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: f (false)

Required parameters:

Name	Type	Description
`map_col`	`duckdb.map`	The map where to check a key-value pair
`key`	`duckdb.unresolved_type`	The key to check
`value`	`duckdb.unresolved_type`	The value to check

map_contains_value(map_col duckdb.map, value duckdb.unresolved_type) returns boolean #

Checks whether a map contains the specified value.

Example 32.12.

-- Check if value exists
SELECT map_contains_value(r['map_col'], 1) as has_value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: t (true)

-- Check for non-existent value
SELECT map_contains_value(r['map_col'], 3) as has_value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: f (false)

Required parameters:

Name	Type	Description
`map_col`	`duckdb.map`	The map where to check a value
`value`	`duckdb.unresolved_type`	The value to check

map_entries(map_col duckdb.map) returns duckdb.struct[] #

Returns an array of structs (key, value) for each key-value pair in the map.

Example 32.13.

-- Get all key-value pairs as structs
SELECT map_entries(r['map_col']) as entries
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: {"(a,1)","(b,2)"}

-- Access individual struct fields
SELECT unnest(map_entries(r['map_col'])) as entry
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;

Required parameters:

Name	Type	Description
`map_col`	`duckdb.map`	The map from which to extract arrays of structs

map_extract(map_col duckdb.map, key duckdb.unresolved_type) returns duckdb.unresolved_type #

Extracts a value from a map using the specified key. If the key does not exist, returns an empty array.

Example 32.14.

-- Extract value from a map
SELECT map_extract(r['map_col'], 'a') as value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: {1}

-- Extract non-existent key
SELECT map_extract(r['map_col'], 'c') as value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: {}

Required parameters:

Name	Type	Description
`map_col`	`duckdb.map`	The map from which to extract a value
`key`	`duckdb.unresolved_type`	The key to use for extracting a value

map_extract_value(map_col duckdb.map, key duckdb.unresolved_type) returns duckdb.unresolved_type #

Returns a value for the specified key or NULL if the key is not contained in the map.

Example 32.15.

-- Extract single value (not as array)
SELECT map_extract_value(r['map_col'], 'a') as value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: 1

-- Non-existent key returns NULL
SELECT map_extract_value(r['map_col'], 'c') as value
FROM duckdb.query($$ SELECT MAP(['a', 'b'], [1, 2]) as map_col $$) r;
-- Returns: NULL

Required parameters:

Name	Type	Description
`map_col`	`duckdb.map`	The map from which to extract a value
`key`	`duckdb.unresolved_type`	The key for which to extract a value

map_from_entries(entries duckdb.struct[]) returns duckdb.map #

Creates a map from an array of structs (k, v).

Example 32.16.

-- Create map from array of structs
SELECT map_from_entries(r['entries']) as new_map
FROM duckdb.query($$
    SELECT [{'k': 'a', 'v': 1}, {'k': 'b', 'v': 2}] as entries
$$) r;
-- Returns: {a=1, b=2}

-- This is the inverse operation of map_entries
SELECT map_from_entries(map_entries(r['map_col'])) as reconstructed
FROM duckdb.query($$ SELECT MAP(['x', 'y'], [10, 20]) as map_col $$) r;
-- Returns: {x=10, y=20}

Required parameters:

Name	Type	Description
`entries`	`duckdb.struct[]`	Array of structs with 'k' (key) and 'v' (value) fields

map_keys(map_col duckdb.map) returns duckdb.unresolved_type #

Returns all keys from a map as an array.

Example 32.17.

-- Get all keys from a map
SELECT map_keys(r['map_col']) as keys
FROM duckdb.query($$ SELECT MAP(['a', 'b', 'c'], [1, 2, 3]) as map_col $$) r;
-- Returns: {a,b,c}

-- Empty map
SELECT map_keys(r['map_col']) as keys
FROM duckdb.query($$ SELECT MAP([], []) as map_col $$) r;
-- Returns: {}

Required parameters:

Name	Type	Description
`map_col`	`duckdb.map`	The map from which to extract keys

map_values(map_col duckdb.map) returns duckdb.unresolved_type #

Returns all values from a map as an array.

Example 32.18.

-- Get all values from a map
SELECT map_values(r['map_col']) as values
FROM duckdb.query($$ SELECT MAP(['a', 'b', 'c'], [1, 2, 3]) as map_col $$) r;
-- Returns: {1,2,3}

-- Empty map
SELECT map_values(r['map_col']) as values
FROM duckdb.query($$ SELECT MAP([], []) as map_col $$) r;
-- Returns: {}

Required parameters:

Name	Type	Description
`map_col`	`duckdb.map`	The map from which to extract values

Prev	Up	Next
32.3. Union Type Functions	Home	32.5. Aggregates Functions