Map functions

GoogleSQL for Bigtable supports the following map functions.

Function list

Name	Summary
`MAP_CONTAINS_KEY`	Checks if a key is in a map.
`MAP_EMPTY`	Checks if a map is empty.
`MAP_ENTRIES`	Gets an array of key-value pairs from a map, sorted in ascending order by key.
`MAP_KEYS`	Gets an array of keys from a map, sorted in ascending order.
`MAP_VALUES`	Gets an array of values from a map, sorted in ascending order by key.

`MAP_CONTAINS_KEY`

MAP_CONTAINS_KEY(input_map, key_to_find)

Description

Checks if a key is in a map. Returns TRUE if the key is found. Otherwise, returns FALSE.

Definitions

input_map: A MAP<K,V> value that represents the map to search. If this value is NULL, the function returns NULL.
key_to_find: The key to find in the map.

Return type

BOOL

Examples

The following query checks if a column called cell_plan in a table called test_table has a key called data_plan_05gb:

SELECT MAP_CONTAINS_KEY(cell_plan, b'data_plan_05gb') AS results
FROM test_table

/*---------+
 | results |
 +---------+
 | TRUE    |
 | TRUE    |
 | FALSE   |
 | FALSE   |
 | FALSE   |
 +---------*/

`MAP_EMPTY`

MAP_EMPTY(input_map)

Description

Checks if a map is empty. Returns TRUE if the map is empty, otherwise FALSE.

Definitions

input_map: A MAP<K,V> value that represents the map to search. If this value is NULL, the function returns NULL.

Return type

BOOL

Example

The following query checks if a column called cell_plan in a table called test_table is empty:

SELECT MAP_EMPTY(cell_plan) AS results
FROM test_table

/*----------+
 | results  |
 +----------+
 | FALSE    |
 | FALSE    |
 | TRUE     |
 | TRUE     |
 | FALSE    |
 | FALSE    |
 +----------*/

`MAP_ENTRIES`

MAP_ENTRIES(input_map)

Description

Gets an array of key-value pairs from a map, sorted in ascending order by key.

Definitions

input_map: A MAP<K,V> value that represents the map to query. If this value is NULL, the function returns NULL.

Return type

ARRAY<STRUCT<K,V>>

Examples

The following query gets key-value pairs, sorted in ascending order by key, from a table called test_table:

SELECT MAP_ENTRIES(cell_plan) AS results
FROM test_table

/*-------------------------------------------------------------+
 | results                                                     |
 +-------------------------------------------------------------+
 | [ {"data_plan_01gb", "true"}, {"data_plan_05gb", "false"} ] |
 | [ {"data_plan_05gb", "false"} ]                             |
 | []                                                          |
 | [ {"data_plan_10gb", "false"} ]                             |
 | [ {"data_plan_10gb", "false"} ]                             |
 +-------------------------------------------------------------*/

`MAP_KEYS`

MAP_KEYS(input_map)

Description

Gets an array of keys from a map, sorted in ascending order.

Definitions

input_map: A MAP<K,V> value that represents the map to query. If this value is NULL, the function returns NULL.

Return type

ARRAY<K>

Examples

The following query gets a list of keys, sorted in ascending order, from a table called test_table:

SELECT MAP_KEYS(cell_plan) AS results
FROM test_table

/*----------------------------------------+
 | results                                |
 +----------------------------------------+
 | [ "data_plan_01gb", "data_plan_05gb" ] |
 | [ "data_plan_05gb" ]                   |
 | []                                     |
 | [ "data_plan_10gb" ]                   |
 | [ "data_plan_10gb" ]                   |
 +----------------------------------------*/

`MAP_VALUES`

MAP_VALUES(input_map)

Description

Gets an array of values from a map, sorted in ascending order by key.

Definitions

input_map: A MAP<K,V> value that represents the map to query. If this value is NULL, the function returns NULL.

Return type

ARRAY<V>

Examples

The following query gets the values, sorted in ascending order by key, from a table called test_table:

SELECT MAP_VALUES(cell_plan) AS results
FROM test_table

/*---------------------+
 | results             |
 +---------------------+
 | [ "true", "false" ] |
 | [ "false" ]         |
 | []                  |
 | [ "false" ]         |
 | [ "false" ]         |
 +---------------------*/