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: AMAP<K,V>value that represents the map to search. If this value isNULL, the function returnsNULL. -
key_to_find: The key to find in the map.
Return type
BOOL
Examples
WITH
t
AS
(
SELECT
MAP_FROM_ARRAY
(
[
(
'color'
,
'red'
)
]
)
AS
input_map
)
SELECT
MAP_CONTAINS_KEY
(
input_map
,
'color'
)
AS
color_key
,
MAP_CONTAINS_KEY
(
input_map
,
'shape'
)
AS
shape_key
FROM
t
/*-----------+-----------+
| color_key | shape_key |
+-----------+-----------+
| true | 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: AMAP<K,V>value that represents the map to search. If this value isNULL, the function returnsNULL.
Return type
BOOL
Example
WITH
t
AS
(
SELECT
MAP_FROM_ARRAY
(
CAST
(
[]
AS
ARRAY<STRUCT<INT64
,
INT64
>> ))
AS
input_map
)
SELECT
MAP_EMPTY
(
input_map
)
AS
empty
FROM
t
/*-------+
| empty |
+-------+
| true |
+-------*/
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: AMAP<K,V>value that represents the map to query. If this value isNULL, the function returnsNULL.
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: AMAP<K,V>value that represents the map to query. If this value isNULL, the function returnsNULL.
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: AMAP<K,V>value that represents the map to query. If this value isNULL, the function returnsNULL.
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" ] |
+---------------------*/
