Clickhouse、Mysql、Presto数据库解析Json数据
一、Clickhouse解析Json
1、visitParamExtractBool(json,name) → 提取json中的name字段,返回UInt8,0或1
例:visitParamExtractBool('{"name":true}', 'name') AS bool
2、visitParamExtractInt(json,name) →提取json中的name字段,返回Int型的值
例:visitParamExtractInt('{"name":123}', 'name') AS int
3、visitParamExtractFloat (json,name)→ 提取json中的name字段,返回Float型的值
例:visitParamExtractFloat('{"name":0.1}', 'name') AS float
4、visitParamExtractString (json,name)→提取json中的name字段,返回String型的值
例:visitParamExtractString('{"name":"你好"}', 'name') AS str
5、visitParamExtractRaw (json,name)→ 提取json中的name字段,返回字段的值,包含空格符
例:visitParamExtractRaw('{"name":"你好"}', 'name') AS raw
6、解析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 │
└──────┴──────┴─────────┴──────┘
参考博客:https://blog.csdn.net/weixin_39025362/article/details/114291128