Access all the values inside the json in clickhouse

Question

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

Answer 1

{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   ?
???????????????????????????????????????