ClickHouse （十五）解析json数据

背景

使用字符串行式保存在ClickHouse的json数据，需要我们解析提取相关字段，将json行转多列。
如提取json数据的各个字段的值，以方便查询。

WITH
'[{"name":"天台","tall":100,"model":"M779011"},{"name":"楼顶","tall":90,"model":"M669011"}]' AS new,
'S123' AS num
SELECT
new,
num
┌─new────────────────────────────────────────────────────────────────────────────────────────┬─num──┐
│ [{"name":"天台","tall":100,"model":"M779011"},{"name":"楼顶","tall":90,"model":"M669011"}] │ S123 │
└────────────────────────────────────────────────────────────────────────────────────────────┴──────┘

准备

ClickHouse提供了JSON函数，方便我们操作json数据：

• visitParamExtractBool（json，name） → 提取json中的name字段，返回UInt8，0或1。
• visitParamExtractInt（json，name） →提取json中的name字段，返回Int型的值。
• visitParamExtractFloat （json，name）→ 提取json中的name字段，返回Float型的值。
• visitParamExtractString （json，name）→提取json中的name字段，返回String型的值。
• visitParamExtractRaw （json，name）→ 提取json中的name字段，返回字段的值，包含空格符。

SELECT
visitParamExtractBool('{"name":true}', 'name') AS bool,
visitParamExtractInt('{"name":123}', 'name') AS int,
visitParamExtractFloat('{"name":0.1}', 'name') AS float,
visitParamExtractString('{"name":"你好"}', 'name') AS str,
visitParamExtractRaw('{"name":"你好"}', 'name') AS raw
┌─bool─┬─int─┬─float─┬─str──┬─raw────┐
│
1 │ 123 │
0.1 │ 你好 │ "你好" │
└──────┴─────┴───────┴──────┴────────┘

测试，解析json数组

使用JSONExtractArrayRaw()函数，将字符串转化为json数组：

SELECT
visitParamExtractString(json, 'name') AS name,
visitParamExtractInt(json, 'tall') AS tall,
visitParamExtractString(json, 'model') AS model,
num
FROM
(
WITH
'[{"name":"天台","tall":100,"model":"M779011"},
{"name":"楼顶","tall":90,"model":"M669011"},
{"name":"秀儿","tall":80,"model":"M559011"}]' AS new,
'S123' AS num
SELECT
new,
num,
JSONExtractArrayRaw(new) AS arr,
arrayJoin(arr) AS json
)
┌─name─┬─tall─┬─model───┬─num──┐
│ 天台 │
100 │ M779011 │ S123 │
│ 楼顶 │
90 │ M669011 │ S123 │
│ 秀儿 │
80 │ M559011 │ S123 │
└──────┴──────┴─────────┴──────┘

还可以使用字符截取：

WITH
'[{"name":"天台","tall":100,"model":"M779011"},
{"name":"楼顶","tall":90,"model":"M669011"},
{"name":"秀儿","tall":80,"model":"M559011"}]' AS new,
replaceAll(replaceAll(new, '[', ''), ']', '') AS out,
concat(arrayJoin(splitByString('},', out)), '}') AS json,
'S123' AS num
SELECT
visitParamExtractString(json, 'name') AS name,
visitParamExtractInt(json, 'tall') AS tall,
visitParamExtractString(json, 'model') AS model,
num
┌─name─┬─tall─┬─model───┬─num──┐
│ 天台 │
100 │ M779011 │ S123 │
│ 楼顶 │
90 │ M669011 │ S123 │
│ 秀儿 │
80 │ M559011 │ S123 │
└──────┴──────┴─────────┴──────┘

最后

以上就是活力纸鹤最近收集整理的关于ClickHouse （十五）解析json数据的全部内容，更多相关ClickHouse内容请搜索靠谱客的其他文章。