网站ftp根目录/今天重大新闻国内最新消息
在定义表的字段类型的时候，可以指定map类型，在clickhouse中json数据的存储是通过map类型来实现的。clickhouse还支持数组类型。
1. map类型字段用法
创建表时，指定字段类型为map类型，并同时指定map的key和value的类型。
1.1 创建map字段的表
-- 在定义 Map 的时候，必须要指定键值对的类型
CREATE TABLE bill_info(bankinfo Map(String, String)) ENGINE = Memory();1.2 添加数据
INSERT INTO bill_info
VALUES ({'bank_deptid': '123', 'bank_deptname': 'bank dept name'})1.3 查询所有数据
查询所有数据。
SELECT * FROM bill_info;┌─bankinfo───────────────────────────────────────────────┐
│ {'bank_deptid':'123','bank_deptname':'bank dept name'} │
└────────────────────────────────────────────────────────┘1.4 查询map特定key的值
可以看到，返回的数据中，字段名变成了表的名称。
SELECT bankinfo['bank_deptid'], bankinfo['bank_deptname'] FROM bill_info;SELECTbankinfo['bank_deptid'],bankinfo['bank_deptname']
FROM bill_infoQuery id: e7c895fa-b6b0-4a7d-bf0b-e6ff12361d81┌─arrayElement(bankinfo, 'bank_deptid')─┬─arrayElement(bankinfo, 'bank_deptname')─┐
│ 123 │ bank dept name │
└───────────────────────────────────────┴─────────────────────────────────────────┘1 rows in set. Elapsed: 0.001 sec. 1.5 为返回值指定别名
可以看到返回的信息列名变成了arrayElement类型，这样对于终端命令行查询是可以看懂的，对于程序处理，就不那么友好了。所以，这里需要根据需要，可以定义合适的别名进行返回，如下所示：
SELECT bankinfo['bank_deptid'] as bank_deptid, bankinfo['bank_deptname'] as bank_deptname FROM bill_info;┌─bank_deptid─┬─bank_deptname──┐
│ 123 │ bank dept name │
└─────────────┴────────────────┘1.6 查询map字段中所有的key和value
SELECT bankinfo.keys, bankinfo.values FROM bill_info;SELECTbankinfo.keys,bankinfo.values
FROM bill_infoQuery id: ad8e9648-bbb8-41a4-92ad-9371ac62f4bd┌─bankinfo.keys───────────────────┬─bankinfo.values──────────┐
│ ['bank_deptid','bank_deptname'] │ ['123','bank dept name'] │
└─────────────────────────────────┴──────────────────────────┘1 rows in set. Elapsed: 0.001 sec. 1.7 根据map字段中包含的key进行查询
SELECT bankinfo.keys, bankinfo.values FROM bill_info where mapContains(bankinfo.keys, 'bank_deptid');SELECTbankinfo.keys,bankinfo.values
FROM bill_info
WHERE mapContains(bankinfo.keys, 'bank_deptid')Query id: 27d54cb2-bd3a-4fb6-875f-a419ec97cfe0┌─bankinfo.keys───────────────────┬─bankinfo.values──────────┐
│ ['bank_deptid','bank_deptname'] │ ['123','bank dept name'] │
└─────────────────────────────────┴──────────────────────────┘1 rows in set. Elapsed: 0.001 sec. 1.8 根据map字段中包含的value进行查询
SELECT bankinfo.keys, bankinfo.values FROM bill_info where mapContains(bankinfo.values, '123');SELECTbankinfo.keys,bankinfo.values
FROM bill_info
WHERE mapContains(bankinfo.values, '123')Query id: 45ee0847-a5b7-4f6b-bbff-94c1dfcdd9b9┌─bankinfo.keys───────────────────┬─bankinfo.values──────────┐
│ ['bank_deptid','bank_deptname'] │ ['123','bank dept name'] │
└─────────────────────────────────┴──────────────────────────┘1 rows in set. Elapsed: 0.001 sec. 1.9 mapKeys：等价于 Map.keys
SELECT bankinfo.keys, mapKeys(bankinfo) FROM bill_info ;SELECTbankinfo.keys,mapKeys(bankinfo)
FROM bill_infoQuery id: 3a7412b8-f593-4511-a931-09d983786860┌─bankinfo.keys───────────────────┬─mapKeys(bankinfo)───────────────┐
│ ['bank_deptid','bank_deptname'] │ ['bank_deptid','bank_deptname'] │
└─────────────────────────────────┴─────────────────────────────────┘1 rows in set. Elapsed: 0.001 sec.
10. mapValues：等价于 Map.values
SELECT bankinfo.values, mapValues(bankinfo) FROM bill_info ;SELECTbankinfo.values,mapValues(bankinfo)
FROM bill_infoQuery id: f96a2ca2-89e0-44e5-aabc-7d926af0c2ff┌─bankinfo.values──────────┬─mapValues(bankinfo)──────┐
│ ['123','bank dept name'] │ ['123','bank dept name'] │
└──────────────────────────┴──────────────────────────┘1 rows in set. Elapsed: 0.001 sec. 2. json相关操作函数
在表中，json字符串实际上也是通过map进行存储的。
2.1 判断字符串是否是json字符串
SELECT isValidJSON('{"a": 1, "b": false}'), isValidJSON('{1, 2, 3}');SELECTisValidJSON('{"a": 1, "b": false}'),isValidJSON('{1, 2, 3}')Query id: 521950f3-3ba5-43e6-a0d9-99df964a1da9┌─isValidJSON('{"a": 1, "b": false}')─┬─isValidJSON('{1, 2, 3}')─┐
│ 1 │ 0 │
└─────────────────────────────────────┴──────────────────────────┘1 rows in set. Elapsed: 0.001 sec. 2.2 检测json是否包含key
SELECT JSONHas('{"a": 1, "b": false}', 'a'), JSONHas('{"a": 1, "b": false}', 'a1');SELECTJSONHas('{"a": 1, "b": false}', 'a'),JSONHas('{"a": 1, "b": false}', 'a1')Query id: 8cff23c7-8266-4b06-b608-bd797a516f37┌─JSONHas('{"a": 1, "b": false}', 'a')─┬─JSONHas('{"a": 1, "b": false}', 'a1')─┐
│ 1 │ 0 │
└──────────────────────────────────────┴───────────────────────────────────────┘1 rows in set. Elapsed: 0.059 sec. 2.3 获取 JSON 的长度
SELECT JSONLength('{"a": 1, "b": false}');SELECT JSONLength('{"a": 1, "b": false}')Query id: 852009a4-2f68-4513-a369-6f61fc6f9108┌─JSONLength('{"a": 1, "b": false}')─┐
│ 2 │
└────────────────────────────────────┘1 rows in set. Elapsed: 0.001 sec. 2.4 获取 JSON 中指定 value 的类型
WITH '{"a": 1, "b": true}' AS j
SELECT JSONType(j, 'a'), JSONType(j, 'b');WITH '{"a": 1, "b": true}' AS j
SELECTJSONType(j, 'a'),JSONType(j, 'b')Query id: 33e503b5-a7a1-4d38-8b89-bc095500a5ba┌─JSONType(j, 'a')─┬─JSONType(j, 'b')─┐
│ Int64 │ Bool │
└──────────────────┴──────────────────┘1 rows in set. Elapsed: 0.001 sec. 2.5 将其它数据类型转成 JSON
-- 不可以写成 {'a': 1, 'b': 2}
SELECT toJSONString(map('a', 1, 'b', 2));SELECT toJSONString(map('a', 1, 'b', 2))Query id: 54ad6d9e-9311-4822-bb05-36fec38f5d85┌─toJSONString(map('a', 1, 'b', 2))─┐
│ {"a":1,"b":2} │
└───────────────────────────────────┘1 rows in set. Elapsed: 0.008 sec. 2.6 根据 key，从 JSON 中解析出指定的 value
-- 在获取 value 的时候，必须要指定 value 是什么类型
-- ClickHouse 中的 Bool 是用整型表示的，所以转成 UInt8、16、32、64 也是可以的
WITH '{"a": 1, "b": true}' AS jSELECT JSONExtract(j, 'a', 'UInt8'), JSONExtract(j, 'b', 'Bool');WITH '{"a": 1, "b": true}' AS j
SELECTJSONExtract(j, 'a', 'UInt8'),JSONExtract(j, 'b', 'Bool')Query id: deea5f36-76c6-442c-8242-0eaabb2937e6┌─JSONExtract(j, 'a', 'UInt8')─┬─JSONExtract(j, 'b', 'Bool')─┐
│ 1 │ true │
└──────────────────────────────┴─────────────────────────────┘1 rows in set. Elapsed: 0.001 sec. 2.7 如果解析失败，那么会得到相应的零值
WITH '{"a": [null, 123], "b": {"a": 1}}' AS jSELECT JSONExtract(j, 'a', 'UInt64');WITH '{"a": [null, 123], "b": {"a": 1}}' AS j
SELECT JSONExtract(j, 'a', 'UInt64')Query id: 491d7e5e-4939-4b5d-8a28-e577f0dd586c┌─JSONExtract(j, 'a', 'UInt64')─┐
│ 0 │
└───────────────────────────────┘1 rows in set. Elapsed: 0.001 sec. 3. 创建包含数组和map字段的表
创建同时包含数组和map数据类型的表：
3.1 创建表
CREATE TABLE dev_meminfo(dev_ip String,location Array(Float64),meminfo Map(String,Float32))ENGINE = MergeTree()ORDER BY dev_ip;CREATE TABLE dev_meminfo
(`dev_ip` String,`location` Array(Float64),`meminfo` Map(String, Float32)
)
ENGINE = MergeTree
ORDER BY dev_ipQuery id: 091f2d7a-bd78-46e5-95a9-13ad2c379881Ok.0 rows in set. Elapsed: 0.151 sec. 3.2 添加数据
INSERT INTO dev_meminfo(dev_ip, location, meminfo) values('123.46.5.112', [39.916144,?116.392582], {'total': 64, 'used': 27.8});INSERT INTO dev_meminfo (dev_ip, location, meminfo) FORMAT ValuesQuery id: 31c57b02-018b-46c6-89dc-a041f30dd0c1Ok.1 rows in set. Elapsed: 0.006 sec.
这里的数组是使用中括号[]进行标识，map使用大括号{}进行标识，每个元素之间使用逗号分隔。
3.3 查看数据
SELECT * from dev_meminfo;SELECT *
FROM dev_meminfoQuery id: 7929ee88-3e5e-4620-9c0e-c2a061ce1e6e┌─dev_ip───────┬─location───────────────┬─meminfo──────────────────┐
│ 123.46.5.112 │ [39.916144,116.392582] │ {'total':64,'used':27.8} │
└──────────────┴────────────────────────┴──────────────────────────┘1 rows in set. Elapsed: 0.001 sec.