ClickHouse 的 Map 类型以及相关操作

楔子

之前在介绍数据类型的时候，有一种没有说，就是 Map。Map 是什么想必无需多言，简单来说的话就是维护键值对之间的映射关系，可以通过键迅速定位到值。

下面就先来创建一张表：

-- 在定义 Map 的时候，必须要指定键值对的类型
CREATE TABLE table_map(a Map(String, UInt64)) ENGINE = Memory();

但是不出意外我们创建表的时候应该会报错，原因就是在表中支持定义 Map 类型的字段还只是试验性的，我们需要将 allow_experimental_map_type 设置为 1，这也是我们单独拿出来介绍的原因。然后我们插入数据：

set allow_experimental_map_type = 1;
CREATE TABLE table_map(a Map(String, UInt64)) ENGINE = Memory();

INSERT INTO table_map 
VALUES ({'key1': 1, 'key2': 10}), ({'key1':2,'key2':20}), ({'key1':3,'key2':30});

 

下面来对表进行查询：

SELECT * FROM table_map;
/*
┌─a────────────────────┐
│ {'key1':1,'key2':10} │
│ {'key1':2,'key2':20} │
│ {'key1':3,'key2':30} │
└──────────────────────┘
*/

如果想选择某个具体的键对应的 value，那么直接通过方括号即可，举个栗子：

SELECT a['key1'], a['key2'], a FROM table_map;
/*
┌─arrayElement(a, 'key1')─┬─arrayElement(a, 'key2')─┬─a────────────────────┐
│                       1 │                      10 │ {'key1':1,'key2':10} │
│                       2 │                      20 │ {'key1':2,'key2':20} │
│                       3 │                      30 │ {'key1':3,'key2':30} │
└─────────────────────────┴─────────────────────────┴──────────────────────┘
*/

如果查询一个不在 Map 当中 key，那么会返回对应的零值。

SELECT a['key3'] FROM table_map;
/*
┌─arrayElement(a, 'key3')─┐
│                       0 │
│                       0 │
│                       0 │
└─────────────────────────┘
*/

当然我们也可以根据现有的数组结构创建 Map：

WITH [1, 2, 3] AS key, ['a', 'b', 'c'] AS value
SELECT cast((key, value) AS Map(UInt8, String));
/*
┌─CAST(tuple(key, value), 'Map(UInt8, String)')─┐
│ {1:'a',2:'b',3:'c'}                           │
└───────────────────────────────────────────────┘
*/
-- 从返回的结果集的字段名，我们可以看出，cast(val AS type) 等价于 cast(val, 'type')
-- 比如 cast(3 AS String) 和 cast(3, 'String') 是等价的，不过个人还是习惯前者

我们在选择的时候也可以只选择 key 或者 value。

SELECT a.keys, a.values FROM table_map;
/*
┌─a.keys──────────┬─a.values─┐
│ ['key1','key2'] │ [1,10]   │
│ ['key1','key2'] │ [2,20]   │
│ ['key1','key2'] │ [3,30]   │
└─────────────────┴──────────┘
*/

 

然后我们来看看字典都支持哪些函数操作

map：我们除了可以通过大括号创建 Map，也可以通过 map 函数创建

SELECT map('key1', number, 'key2', number * 2) FROM numbers(3);
/*
┌─map('key1', number, 'key2', multiply(number, 2))─┐
│ {'key1':0,'key2':0}                              │
│ {'key1':1,'key2':2}                              │
│ {'key1':2,'key2':4}                              │
└──────────────────────────────────────────────────┘
*/
-- 注意：SELECT {'key1': number, 'key2': number * 2} 是非法的，必须使用 map 函数创建

同理我们插入数据的时候也可以使用 map 函数：

INSERT INTO table_map VALUES (map('key1', 1, 'key2', 10));
SELECT a.keys, a.values FROM table_map;
/*
┌─a.keys──────────┬─a.values─┐
│ ['key1','key2'] │ [1,10]   │
│ ['key1','key2'] │ [2,20]   │
│ ['key1','key2'] │ [3,30]   │
└─────────────────┴──────────┘
┌─a.keys──────────┬─a.values─┐
│ ['key1','key2'] │ [1,10]   │
└─────────────────┴──────────┘
*/

 

mapContains：检测 Map 里面是否包含某个 key

WITH map(1, 3, 2, 5) AS m
SELECT mapContains(m, 1), mapContains(m, 3);
/*
┌─mapContains(m, 1)─┬─mapContains(m, 3)─┐
│                 1 │                 0 │
└───────────────────┴───────────────────┘
*/

 

mapKeys：等价于 Map.keys

SELECT a.keys, mapKeys(a) FROM table_map;
/*
┌─a.keys──────────┬─mapKeys(a)──────┐
│ ['key1','key2'] │ ['key1','key2'] │
│ ['key1','key2'] │ ['key1','key2'] │
│ ['key1','key2'] │ ['key1','key2'] │
└─────────────────┴─────────────────┘
┌─a.keys──────────┬─mapKeys(a)──────┐
│ ['key1','key2'] │ ['key1','key2'] │
└─────────────────┴─────────────────┘
*/

 

mapValues：等价于 Map.values

SELECT a.values, mapValues(a) FROM table_map;
/*
┌─a.values─┬─mapValues(a)─┐
│ [1,10]   │ [1,10]       │
│ [2,20]   │ [2,20]       │
│ [3,30]   │ [3,30]       │
└──────────┴──────────────┘
┌─a.values─┬─mapValues(a)─┐
│ [1,10]   │ [1,10]       │
└──────────┴──────────────┘
*/

注意：mapKeys、mapValues 相当于数据全量读取，然后再选择所有的 key 或 value，所以建议还是使用 Map.keys、Map.values。但如果将 optimize_functions_to_subcolumns 设置为 1，那么会进行优化：

SELECT mapKeys(m), mapValues(m) FROM table 会转化成 SELECT m.keys, m.values FROM table

以上就是 Map 的内容，总的来说还是很简单的。

JSON 的相关操作

既然提到了 Map，那么就不能不提到 JSON，这两者在结构上有着非常高的相似之处，下面就来看看 JSON 支持哪些操作。

 

isValidJSON：检测 JSON 是否合法

JSON 本质上也是一个字符串，isValidJSON 则是检测该字符串是否符合 JSON 格式。

SELECT isValidJSON('{"a": 1, "b": false}'), isValidJSON('{1, 2, 3}');
/*
┌─isValidJSON('{"a": 1, "b": false}')─┬─isValidJSON('{1, 2, 3}')─┐
│                                   1 │                        0 │
└─────────────────────────────────────┴──────────────────────────┘
*/

 

JSONHas：检测 JSON 是否包含指定的 key

SELECT JSONHas('{"a": 1, "b": false}', 'a'), JSONHas('{"a": 1, "b": false}', 'a1');
/*
┌─JSONHas('{"a": 1, "b": false}', 'a')─┬─JSONHas('{"a": 1, "b": false}', 'a1')─┐
│                                    1 │                                     0 │
└──────────────────────────────────────┴───────────────────────────────────────┘
*/

 

JSONLength：获取 JSON 的长度

SELECT JSONLength('{"a": 1, "b": false}');
/*
┌─JSONLength('{"a": 1, "b": false}')─┐
│                                  2 │
└────────────────────────────────────┘
*/

 

JSONType：获取 JSON 中指定 value 的类型

WITH '{"a": 1, "b": true, "c": null, "d": "xx", "e": [1, 2, 3], "f": {"a": 1}}' AS j
SELECT JSONType(j, 'a'), JSONType(j, 'b'), JSONType(j, 'c'), 
       JSONType(j, 'd'), JSONType(j, 'e'), JSONType(j, 'f');
/*
┌─JSONType(j, 'a')─┬─JSONType(j, 'b')─┬─JSONType(j, 'c')─┬─JSONType(j, 'd')─┬─JSONType(j, 'e')─┬─JSONType(j, 'f')─┐
│ Int64            │ Bool             │ Null             │ String           │ Array            │ Object           │
└──────────────────┴──────────────────┴──────────────────┴──────────────────┴──────────────────┴──────────────────┘
*/

 

toJSONString：将其它数据类型转成 JSON

-- 不可以写成 {'a': 1, 'b': 2}
SELECT toJSONString(map('a', 1, 'b', 2));
/*
┌─toJSONString(map('a', 1, 'b', 2))─┐
│ {"a":1,"b":2}                     │
└───────────────────────────────────┘
*/

 

JSONExtract：根据 key，从 JSON 中解析出指定的 value，就类似于根据 key 获取 Map 中的 value 一样

-- 在获取 value 的时候，必须要指定 value 是什么类型
-- ClickHouse 中的 Bool 是用整型表示的，所以转成 UInt8、16、32、64 也是可以的
WITH '{"a": 1, "b": true}' AS j
SELECT JSONExtract(j, 'a', 'UInt8'), JSONExtract(j, 'b', 'Bool');
/*
┌─JSONExtract(j, 'a', 'UInt8')─┬─JSONExtract(j, 'b', 'Bool')─┐
│                            1 │                           1 │
└──────────────────────────────┴─────────────────────────────┘
*/

WITH '{"a": [null, 123], "b": {"a": 1}}' AS j
SELECT JSONExtract(j, 'a', 'Array(UInt8)'),
       JSONExtract(j, 'a', 'Array(Nullable(UInt8))');
/*
┌─JSONExtract(j, 'a', 'Array(UInt8)')─┬─JSONExtract(j, 'a', 'Array(Nullable(UInt8))')─┐
│ [0,123]                             │ [NULL,123]                                    │
└─────────────────────────────────────┴───────────────────────────────────────────────┘
*/

如果解析失败，那么会得到相应的零值，举个栗子：

WITH '{"a": [null, 123], "b": {"a": 1}}' AS j
SELECT JSONExtract(j, 'a', 'UInt64');
/*
┌─JSONExtract(j, 'a', 'UInt64')─┐
│                             0 │
└───────────────────────────────┘
*/