clickhouse数据库中使用map、json和array的用法

最新推荐文章于 2024-08-02 15:39:08 发布

程序猿20

最新推荐文章于 2024-08-02 15:39:08 发布

阅读量1w

点赞数 5

分类专栏：数据库文章标签： clickhouse

本文链接：https://blog.csdn.net/liaomingwu/article/details/126342893

版权

数据库专栏收录该内容

32 篇文章 2 订阅

订阅专栏

在定义表的字段类型的时候，可以指定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;

SELECT
    bankinfo['bank_deptid'],
    bankinfo['bank_deptname']
FROM bill_info

Query 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;

SELECT
    bankinfo.keys,
    bankinfo.values
FROM bill_info

Query 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');

SELECT
    bankinfo.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');

SELECT
    bankinfo.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 ;

SELECT
    bankinfo.keys,
    mapKeys(bankinfo)
FROM bill_info

Query 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 ;

SELECT
    bankinfo.values,
    mapValues(bankinfo)
FROM bill_info

Query 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}');

SELECT
    isValidJSON('{"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');

SELECT
    JSONHas('{"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
SELECT
    JSONType(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 j
              SELECT JSONExtract(j, 'a', 'UInt8'), JSONExtract(j, 'b', 'Bool');

WITH '{"a": 1, "b": true}' AS j
SELECT
    JSONExtract(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 j
              SELECT 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_ip

Query id: 091f2d7a-bd78-46e5-95a9-13ad2c379881

Ok.

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 Values

Query id: 31c57b02-018b-46c6-89dc-a041f30dd0c1

Ok.

1 rows in set. Elapsed: 0.006 sec.

这里的数组是使用中括号[]进行标识，map使用大括号{}进行标识，每个元素之间使用逗号分隔。

3.3 查看数据

SELECT * from dev_meminfo;

SELECT *
FROM dev_meminfo

Query 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.