1、字典结构

select visitParamExtractRaw('{"abc":"\\n\\u0000"}', 'abc');  --"\n\u0000"
select JSONExtract('{"a":"hello","b":"tom","c":12}', 'Tuple(String,String,UInt8)') as kn;  --('hello','tom',12)
select visitParamExtractRaw('{"type":"polygon","radius":null,"body":[(119.60, 30.15), (119.61, 30.12)]}','type') ;-- "polygon"
select visitParamExtractRaw('{"type":"polygon","radius":null,"body":[(119.60, 30.15), (119.61, 30.12)]}','radius') ;-- null
select visitParamExtractRaw('{"type":"polygon","radius":null,"body":[(119.60, 30.15), (119.61, 30.12)]}','body');--[(119.60, 30.15), (119.61, 30.12)]

2、地理函数

函数 - GEO函数 - 《列式数据库 ClickHouse v20.8 使用教程》 - 书栈网 · BookStack

drop table if exists dw_src_grid;
create table if not exists dw_src_grid
    engine = MergeTree
    order by object_id
as
select object_id,
       id grid_id,
       dictGet('dict_code_grid', 'sub_bank1', cityHash64(grid_id)) as sub_bank,
       dictGet('dict_code_grid', 'sub_banks', cityHash64(grid_id)) as sub_banks,
       dictGet('dict_code_grid', 'manager_id1', cityHash64(grid_id)) as manager_id,
       dictGet('dict_code_grid', 'type', cityHash64(grid_id)) as grid_type
from dw_src_grid01
where (is_polygon and pointInPolygon(pt1, polygon)) or
      (is_circle and radius is not null and geoDistance(pt1.1, pt1.2, (polygon.1)[1], (polygon.2)[1]) < radius)

3、字符串函数

where startsWith(cuidcsid, '101'))