https://fiddle.clickhouse.com/2fcd76f1-0389-4745-8cc1-ff7a87bfbef3
https://clickhouse.com/docs/sql-reference/data-types/newjson
-- max_dynamic_paths how many real columns to store, excess be will stored in a sigle Map column
CREATE TABLE mt (json JSON(max_dynamic_paths=2048))
ENGINE=MergeTree
order by tuple();
INSERT INTO mt(json) select
'{"aaaa":"aaaa", "meta":{"aa":4}, "bbb":555}';
INSERT INTO mt(json) select
'{"aaaa":"aaaa", "meta":{"aa":6}, "bbb":"bbb", "o": [{"a": 1}]}';
--SELECT * FROM mt;
select
json.^meta as meta, -- as JSON node
json.bbb.:Int64 as x, -- as attribute with specific type
json.bbb.:String as x1, -- as attribute with specific type
json.bbb::String as y -- cast to type
from mt;
+-meta-------+----x-+-x1---+-y---+
1. | {"aa":"6"} | NULL | bbb | bbb |
2. | {"aa":"4"} | 555 | NULL | 555 |
+------------+------+------+-----+
select sum(json.meta.aa.:Int64), json.aaaa.:String
from mt
where json.aaaa.:String like 'aaa%'
group by json.aaaa.:String;
+-sum(json.meta.aa.:`Int64`)-+-json.aaaa.:`String`-+
1. | 10 | aaaa |
+----------------------------+---------------------+
-- extract array of objects / all attributes are flattened to a single level
select json.o[].a[1] from mt
+-arrayElement~SON)`.a, 1)-+
1. | NULL |
2. | 1 |
+--------------------------+