LOAP引擎：clickhouse06：简单介绍几个JSON函数

数据准备

create table tb_json(cont String) engine=Log;

insert into  tb_json values('{"movie":"1207","rate":"4","timeStamp":"978300719","uid":"1"}')
,('{"movie":"2028","rate":"5","timeStamp":"978301619","uid":"1"}')
,('{"movie":"531","rate":"4","timeStamp":"978302149","uid":"1"}')
,('{"movie":"3114","rate":"4","timeStamp":"978302174","uid":"1"}')
,('{"movie":"608","rate":"4","timeStamp":"978301398","uid":"1"}')
,('{"movie":"1246","rate":"4","timeStamp":"978302091","uid":"1"}')
,('{"movie":"1357","rate":"5","timeStamp":"978298709","uid":"2"}') ;

1.visitParamHas(params, name)函数

select 
visitParamHas(
cont,              
'movie'
)
from
tb_json;

---参数一：json row data
---参数二：名称

若参数中有此名称的字段，返回1，没有返回0

2.visitParamExtractString(params, name)函数

select 
visitParamExtractString(cont,'movie')
from
tb_json;

---参数一：json row data
---参数二：字段名称

若有此字段，就返回此字段所对应的数据
没有就返回空

3.JSONExtract函数

select 
JSONExtract(cont,'Tuple(movie String,rate String)')
from 
tb_json;


----参数一： json row data
----参数二：要查的元组


----元组中的字段名和数据类型必须和json中的数据对应，否则查不到

----元组中查询的数据可以不是json的所有字段，可以只查部分

select 
JSONExtract(cont,'Tuple(movie String,rate String)').1  ---只查第一个字段
from 
tb_json