ClickHouse （十五）解析json数据

古城微笑少年丶 2021-06-24 13:58 4715阅读 1赞

## 背景 ##

使用字符串行式保存在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 │
    └──────┴──────┴─────────┴──────┘