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" ] |  *---------------------*/