Access all the values inside the json in clickhouse

user_17220802 0
Viewed 604

i have a column "device" which has rows json values like this

device
{"brand_name":'huawei,'brand_id':'1232',''country:'china'}
{"brand_name":'sony,'brand_id':'1ds232',''country:'japan'}

i want to create a column for every element inside the json like this

brand_name brand_id country
huawei 1232 china
sony 1ds232 japan

In a standard SQL i have done like this,

Select
device.brand_name
device.brand_id
device.country
From table

I want to do this in clickhouse and In this case JSON only have three values ( brand_name,brand_id, country) but what if the JSON have n number of values , so what i want to do is instead of accessing every value in JSON by device.brand_name,device.brand_id....etc , I want to loop all the values inside it and make it as a column

In standard SQL i have achieved with this

Select
device.*
From table

, is there a way to do it in clickhouse? , thank you

clickhouse sql
1 Answers

{brand_name:'huawei,'brand_id':'1232',''country:'china'}

Not a valid JSON.

New JSON (22.3) feature https://github.com/ClickHouse/ClickHouse/issues/23516

set allow_experimental_object_type=1;
create table testj( A Int64, device JSON ) Engine=MergeTree order by A;

insert into testj (device) format TSV {"brand_name":"huawei","brand_id":"1232","country":"china"}


select A, device.brand_name, device.brand_id, device.country from testj;
┌─A─┬─device.brand_name─┬─device.brand_id─┬─device.country─┐
│ 0 │ huawei            │ 1232            │ china          │
└───┴───────────────────┴─────────────────┴────────────────┘


SELECT * FROM testj;
┌─A─┬─device────────────────────┐
│ 0 │ ('1232','huawei','china') │
└───┴───────────────────────────┘

SELECT toJSONString(device) FROM testj
┌─toJSONString(device)────────────────────────────────────────┐
│ {"brand_id":"1232","brand_name":"huawei","country":"china"} │
└─────────────────────────────────────────────────────────────┘

https://kb.altinity.com/altinity-kb-queries-and-syntax/jsonextract-to-parse-many-attributes-at-a-time/

https://kb.altinity.com/altinity-kb-schema-design/altinity-kb-jsonasstring-and-mat.-view-as-json-parser/

https://clickhouse.com/docs/en/sql-reference/functions/json-functions/#jsonextractjson-indices-or-keys-return-type

create table testj( A Int64, device String, 
     brand_name String default JSONExtractString(device,'brand_name'), 
     brand_id String  default JSONExtractString(device,'brand_id'), 
     country String  default JSONExtractString(device,'country') ) 
Engine=MergeTree order by A;

insert into testj (device) format TSV {"brand_name":"huawei","brand_id":"1232","country":"china"}
                               ;

select A, brand_name, brand_id, country from testj;
┌─A─┬─brand_name─┬─brand_id─┬─country─┐
│ 0 │ huawei     │ 1232     │ china   │
└───┴────────────┴──────────┴─────────┘
user_116443080
Related