Clickhouse-复杂json数组解析

最新推荐文章于 2025-06-08 10:29:24 发布

文文鑫

最新推荐文章于 2025-06-08 10:29:24 发布

阅读量590

点赞数 3

CC 4.0 BY-SA版权

分类专栏： # ClickHouse 文章标签： clickhouse json

本文链接：https://blog.youkuaiyun.com/dgssd/article/details/142977133

ClickHouse 专栏收录该内容

3 篇文章

订阅专栏

json示例

[{"id":1,"name":"月测","priceTypeList":[{"priceTypeId":1,"priceTypeName":"手绘","unitId":1,"unitName":"张","unitPrice":2.1}]},{"id":2,"name":"美术","priceTypeList":[{"priceTypeId":2,"priceTypeName":"矢量插画","unitId":1,"unitName":"张","unitPrice":10},{"priceTypeId":1,"priceTypeName":"手绘","unitId":1,"unitName":"张","unitPrice":5.23}]}]

JSONExtractArrayRaw：将JsonString 转换为Json
arrayJoin: 类似于explode ，将array中的element裂变为行
JSONExtractRaw：抽取json中的key、value

select JSONExtract(row, 'id', 'Int32') AS id
      ,JSONExtract(row, 'name', 'String') AS name
      ,JSONExtract(arrayJoin(JSONExtractArrayRaw(row,'priceTypeList')),'priceTypeId','String') AS priceTypeId
      ,JSONExtract(arrayJoin(JSONExtractArrayRaw(row,'priceTypeList')),'priceTypeName','String') AS priceTypeName
      ,JSONExtract(arrayJoin(JSONExtractArrayRaw(row,'priceTypeList')),'unitName','String') AS unitName
      ,JSONExtract(arrayJoin(JSONExtractArrayRaw(row,'priceTypeList')),'unitId','String') AS unitId
      ,JSONExtract(arrayJoin(JSONExtractArrayRaw(row,'priceTypeList')),'unitPrice','String') AS unitPrice
from 
(
select arrayJoin(JSONExtractArrayRaw('[{"id":1,"name":"月测","priceTypeList":[{"priceTypeId":1,"priceTypeName":"手绘","unitId":1,"unitName":"张","unitPrice":2.1}]},{"id":2,"name":"美术","priceTypeList":[{"priceTypeId":2,"priceTypeName":"矢量插画","unitId":1,"unitName":"张","unitPrice":10},{"priceTypeId":1,"priceTypeName":"手绘","unitId":1,"unitName":"张","unitPrice":5.23}]}]')) as row
)