基础数据准备
WITH
'[{"name":"xiaoming","age":18,"sex":"male"},{"name":"xiaohong","age":20,"model":"female"}]' AS people
SELECT
people
函数
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
解析json数组
使用JSONExtractArrayRaw()函数,将字符串转化为json数组:
SELECT
visitParamExtractString(json, 'name') AS name,
visitParamExtractInt(json, 'age') AS age,
visitParamExtractString(json, 'sex') AS sex
FROM
(
WITH
'[{"name":"xiaoming","age":18,"sex":"male"},{"name":"xiaohong","age":20,"sex":"female"}]' AS people
SELECT
people,
JSONExtractArrayRaw(people) AS arr,
arrayJoin(arr) AS json
)