This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Map Functions

1: MAP_CAT
2: MAP_CONTAINS_KEY
3: MAP_DELETE
4: MAP_FILTER
5: MAP_INSERT
6: MAP_KEYS
7: MAP_PICK
8: MAP_SIZE
9: MAP_TRANSFORM_KEYS
10: MAP_TRANSFORM_VALUES
11: MAP_VALUES

This section provides reference information for map functions in PlaidCloud Lakehouse.

1 - MAP_CAT

Returns the concatenatation of two MAPs.

SQL Syntax

MAP_CAT( <map1>, <map2> )

Arguments

Arguments	Description
`<map1>`	The source MAP.
`<map2>`	The MAP to be appended to map1.

:::note

If both map1 and map2 have a value with the same key, then the output map contains the value from map2.
If either argument is NULL, the function returns NULL without reporting any error. :::

Return Type

Map.

SQL Examples

SELECT MAP_CAT({'a':1,'b':2,'c':3}, {'c':5,'d':6});
┌─────────────────────────────────────────────┐
│ map_cat({'a':1,'b':2,'c':3}, {'c':5,'d':6}) │
├─────────────────────────────────────────────┤
│ {'a':1,'b':2,'c':5,'d':6}                   │
└─────────────────────────────────────────────┘

2 - MAP_CONTAINS_KEY

Determines whether the specified MAP contains the specified key.

SQL Syntax

MAP_CONTAINS_KEY( <map>, <key> )

Arguments

Arguments	Description
`<map>`	The map to be searched.
`<key>`	The key to find.

Return Type

Boolean.

SQL Examples

SELECT MAP_CONTAINS_KEY({'a':1,'b':2,'c':3}, 'c');
┌────────────────────────────────────────────┐
│ map_contains_key({'a':1,'b':2,'c':3}, 'c') │
├────────────────────────────────────────────┤
│ true                                       │
└────────────────────────────────────────────┘

SELECT MAP_CONTAINS_KEY({'a':1,'b':2,'c':3}, 'x');
┌────────────────────────────────────────────┐
│ map_contains_key({'a':1,'b':2,'c':3}, 'x') │
├────────────────────────────────────────────┤
│ false                                      │
└────────────────────────────────────────────┘

3 - MAP_DELETE

Returns an existing MAP with one or more keys removed.

SQL Syntax

MAP_DELETE( <map>, <key1> [, <key2>, ... ] )
MAP_DELETE( <map>, <array> )

Arguments

Arguments	Description
`<map>`	The MAP that contains the KEY to remove.
`<keyN>`	The KEYs to be omitted from the returned MAP.
`<array>`	The Array of KEYs to be omitted from the returned MAP.

:::note

The types of the key expressions and the keys in the map must be the same.
Key values not found in the map will be ignored. :::

Return Type

Map.

SQL Examples

SELECT MAP_DELETE({'a':1,'b':2,'c':3}, 'a', 'c');
┌───────────────────────────────────────────┐
│ map_delete({'a':1,'b':2,'c':3}, 'a', 'c') │
├───────────────────────────────────────────┤
│ {'b':2}                                   │
└───────────────────────────────────────────┘

SELECT MAP_DELETE({'a':1,'b':2,'c':3}, ['a', 'b']);
┌─────────────────────────────────────────────┐
│ map_delete({'a':1,'b':2,'c':3}, ['a', 'b']) │
├─────────────────────────────────────────────┤
│ {'c':3}                                     │
└─────────────────────────────────────────────┘

4 - MAP_FILTER

Filters key-value pairs from a map using a lambda expression to define the condition.

SQL Syntax

MAP_FILTER(<map>, (<key>, <value>) -> <condition>)

Return Type

Returns a map that includes only the key-value pairs meeting the condition specified by the lambda expression.

SQL Examples

This example returns a map containing only the products with stock quantities below 10:

SELECT MAP_FILTER({101:15, 102:8, 103:12, 104:5}, (product_id, stock) -> (stock < 10)) AS low_stock_products;

┌────────────────────┐
│ low_stock_products │
├────────────────────┤
│ {102:8,104:5}      │
└────────────────────┘

5 - MAP_INSERT

Returns a new MAP consisting of the input MAP with a new key-value pair inserted (an existing key updated with a new value).

SQL Syntax

MAP_INSERT( <map>, <key>, <value> [, <updateFlag> ] )

Arguments

Arguments	Description
`<map>`	The input MAP.
`<key>`	The new key to insert into the MAP.
`<value>`	The new value to insert into the MAP.
`<updateFlag>`	The boolean flag indicates whether an existing key can be overwritten. The default is FALSE.

Return Type

Map.

SQL Examples

SELECT MAP_INSERT({'a':1,'b':2,'c':3}, 'd', 4);
┌─────────────────────────────────────────┐
│ map_insert({'a':1,'b':2,'c':3}, 'd', 4) │
├─────────────────────────────────────────┤
│ {'a':1,'b':2,'c':3,'d':4}               │
└─────────────────────────────────────────┘

SELECT MAP_INSERT({'a':1,'b':2,'c':3}, 'a', 5, true);
┌───────────────────────────────────────────────┐
│ map_insert({'a':1,'b':2,'c':3}, 'a', 5, TRUE) │
├───────────────────────────────────────────────┤
│ {'a':5,'b':2,'c':3}                           │
└───────────────────────────────────────────────┘

6 - MAP_KEYS

Returns the keys in a map.

SQL Syntax

MAP_KEYS( <map> )

Arguments

Arguments	Description
`<map>`	The input map.

Return Type

Array.

SQL Examples

SELECT MAP_KEYS({'a':1,'b':2,'c':3});

┌───────────────────────────────┐
│ map_keys({'a':1,'b':2,'c':3}) │
├───────────────────────────────┤
│ ['a','b','c']                 │
└───────────────────────────────┘

7 - MAP_PICK

Returns a new MAP containing the specified key-value pairs from an existing MAP.

SQL Syntax

MAP_PICK( <map>, <key1> [, <key2>, ... ] )
MAP_PICK( <map>, <array> )

Arguments

Arguments	Description
`<map>`	The input MAP.
`<keyN>`	The KEYs to be included from the returned MAP.
`<array>`	The Array of KEYs to be included from the returned MAP.

:::note

The types of the key expressions and the keys in the map must be the same.
Key values not found in the map will be ignored. :::

Return Type

Map.

SQL Examples

SELECT MAP_PICK({'a':1,'b':2,'c':3}, 'a', 'c');
┌─────────────────────────────────────────┐
│ map_pick({'a':1,'b':2,'c':3}, 'a', 'c') │
├─────────────────────────────────────────┤
│ {'a':1,'c':3}                           │
└─────────────────────────────────────────┘

SELECT MAP_PICK({'a':1,'b':2,'c':3}, ['a', 'b']);
┌───────────────────────────────────────────┐
│ map_pick({'a':1,'b':2,'c':3}, ['a', 'b']) │
├───────────────────────────────────────────┤
│ {'a':1,'b':2}                             │
└───────────────────────────────────────────┘

8 - MAP_SIZE

Returns the size of a MAP.

SQL Syntax

MAP_SIZE( <map> )

Arguments

Arguments	Description
`<map>`	The input map.

Return Type

UInt64.

SQL Examples

SELECT MAP_SIZE({'a':1,'b':2,'c':3});

┌───────────────────────────────┐
│ map_size({'a':1,'b':2,'c':3}) │
├───────────────────────────────┤
│ 3                             │
└───────────────────────────────┘

9 - MAP_TRANSFORM_KEYS

Applies a transformation to each key in a map using a lambda expression.

SQL Syntax

MAP_TRANSFORM_KEYS(<map>, (<key>, <value>) -> <key_transformation>)

Return Type

Returns a map with the same values as the input map but with keys modified according to the specified lambda transformation.

SQL Examples

This example adds 1,000 to each product ID, creating a new map with updated keys while keeping the associated prices the same:

SELECT MAP_TRANSFORM_KEYS({101: 29.99, 102: 45.50, 103: 15.00}, (product_id, price) -> product_id + 1000) AS updated_product_ids;

┌────────────────────────────────────┐
│         updated_product_ids        │
├────────────────────────────────────┤
│ {1101:29.99,1102:45.50,1103:15.00} │
└────────────────────────────────────┘

10 - MAP_TRANSFORM_VALUES

Applies a transformation to each value in a map using a lambda expression.

SQL Syntax

MAP_TRANSFORM_VALUES(<map>, (<key>, <value>) -> <value_transformation>)

Return Type

Returns a map with the same keys as the input map but with values modified according to the specified lambda transformation.

SQL Examples

This example reduces each product's price by 10%, while the product IDs (keys) remain unchanged:

SELECT MAP_TRANSFORM_VALUES({101: 100.0, 102: 150.0, 103: 200.0}, (product_id, price) -> price * 0.9) AS discounted_prices;

┌───────────────────────────────────┐
│         discounted_prices         │
├───────────────────────────────────┤
│ {101:90.00,102:135.00,103:180.00} │
└───────────────────────────────────┘

11 - MAP_VALUES

Returns the values in a map.

SQL Syntax

MAP_VALUES( <map> )

Arguments

Arguments	Description
`<map>`	The input map.

Return Type

Array.

SQL Examples

SELECT MAP_VALUES({'a':1,'b':2,'c':3});

┌─────────────────────────────────┐
│ map_values({'a':1,'b':2,'c':3}) │
├─────────────────────────────────┤
│ [1,2,3]                         │
└─────────────────────────────────┘