ClickHouse解析json

最新推荐文章于 2024-09-12 14:35:57 发布

你是光芒丶

最新推荐文章于 2024-09-12 14:35:57 发布

阅读量2.4k

点赞数 25

文章标签： clickhouse json java

本文链接：https://blog.csdn.net/weixin_45322869/article/details/137928452

版权

JSON解析语法

工作中，我们经常遇到ClickHouse

数据表字段中存储json格式的数据.

1. JSONExtractRaw(json, 'key')

JSONExtractRaw：将一部分 JSON 作为未分析的字符串返回。如果该部分不存在或类型错误，则将返回空字符串。

2. JSONExtractString(json, 'key')

JSONExtractString：JSON中返回值为String类型的值。如果该部分不存在或类型错误，则将返回空字符串。

3. JSONExtractFloat(json, 'key')

JSONExtractFloat：JSON中返回值为Float类型的值。如果该部分不存在或类型错误，则将返回0。

4. JSONExtractInt(json, 'key')

JSONExtractInt：JSON中返回值为Int类型的值。如果该部分不存在或类型错误，则将返回0。

5. JSONExtractUInt(json, 'key')

JSONExtractUInt：JSON中返回值为Int类型的值。如果该部分不存在或类型错误，则将返回0。

6. JSONExtractArrayRaw(json, 'key')

JSONExtractArrayRaw：JSON中返回值为数组类型类型的值。如果该部分不存在或类型错误，则将返回空数组。

7. JSONExtractArrayRaw(json) [n]

JSONExtractArrayRaw：将字符串转为数组类型，并按照下标获取数据。如果该部分不存在或类型错误，则将返回空字符串。

例如下面的数据：

{"province":"广东省","city":"揭阳市","district":"普宁市","detail":"108县道与107县道交叉口东南500

SELECT  address,
        JSONExtractRaw(address, 'province') AS `省`,
        JSONExtractString(address, 'province') AS `省1`,
        JSONExtractFloat(address, 'province') AS `省2`,
        JSONExtractInt(address, 'province') AS `省3`,
        JSONExtractUInt(address, 'province') AS `省4`,
        JSONExtractArrayRaw(address, 'lnglatXY') AS `经纬度`,
        JSONExtractArrayRaw(address, 'lnglatXY') [1] AS `经度`,
        JSONExtractArrayRaw(address, 'lnglatXY') [2] AS `纬度`
FROM    hngd.dm_pom_hngd_jdy_third_party_construction_process_management_platform_information_collection_a_d

[{"_id":"66179e4448138e83146a95b9","guar_reco_key":"","guar_reco_state":"","guar_reco_time":"2024-04-11T01:47:13.000Z","guar_reco_work":"管道10米外铺设DN200给水管，接驳原有水管，现场钩机2台（钩机手杨东林13727205556，钩机手黄都13435365955），吊车0台，钻机0台","guar_reco_risk":"中风险","guar_reco_high_risk":"","guar_reco_mid_risk":"在距离管道5-20m内开展动土挖掘作业","guar_reco_low_risk":"","guar_reco_sign":{}}]

SELECT  guar_reco,
        JSONExtractArrayRaw(guar_reco)[1],
        JSONExtractString(JSONExtractArrayRaw(guar_reco)[1],'guar_reco_low_risk')  AS `低风险判定标准`
FROM    hngd.dm_pom_hngd_jdy_third_parth_construction_process_management_platform_monitoring_record_constructios_status_a_d

JSON一行转多行数据

使用字符串行式保存在ClickHouse的json数据，需要我们解析提取相关字段，将json行转多列。

WITH
'[{"name":"天台","tall":100,"model":"M779011"},{"name":"楼顶","tall":90,"model":"M669011"}]' AS new,
'S123' AS num
SELECT
new,
num

使用 arrayJoin(arr)函数一行转多行

WITH '[{"name":"天台","tall":100,"model":"M779011"},{"name":"楼顶","tall":90,"model":"M669011"}]' AS new,
'S123' AS num
select  JSONExtractString(json, 'name') AS name,
        JSONExtractInt(json, 'tall') AS tall,
        JSONExtractString(json, 'model') AS model
from    (
            SELECT  new,
                    num,
                    JSONExtractArrayRaw(new) AS arr,
                    arrayJoin(arr) AS json
        )