Utilizing JSON Feature in ClickHouse

ClickHouse offers an experimental JSON format. So we need to

SET allow_experimental_object_type = 1

Now, we may use the JSON type and insert JSON values while creating tables.

CREATE TABLE exp_test
(
    `id` UInt64,
    `value` JSON
)
ENGINE = MergeTree
ORDER BY id

INSERT INTO exp_test VALUES(1, '{"db_type":"ClickHouse","version":22.10}')
INSERT INTO exp_test VALUES(2, '{"db_type":"MongoDB","version":5}')
INSERT INTO exp_test VALUES(3, '{"db_type":"PostgreSQL","version":14}')
INSERT INTO exp_test VALUES(4, '{"db_type":"Oracle","version":19}')
INSERT INTO exp_test VALUES(5, '{"db_type":"MySQL","version":8}')

We can easily see the JSON values when we check the table. But there is no JSON columns with this way.

SELECT * FROM exp_test

┌─id─┬─value───────────────┐
│  1 │ ('ClickHouse',22.1) │
│  2 │ ('MongoDB',5)       │
│  3 │ ('PostgreSQL',14)   │
│  4 │ ('Oracle',19)       │
│  5 │ ('MySQL',8)         │
└────┴─────────────────────┘

Displaying JSON columns

SET output_format_json_named_tuples_as_objects = 1

When we look at the table, we can quickly notice the columns that contain the JSON values:

SELECT * FROM exp_test FORMAT JSONEachRow

{"id":"1","value":{"db_type":"ClickHouse","version":22.1}}
{"id":"2","value":{"db_type":"MongoDB","version":5}}
{"id":"3","value":{"db_type":"PostgreSQL","version":14}}
{"id":"4","value":{"db_type":"Oracle","version":19}}
{"id":"5","value":{"db_type":"MySQL","version":8}}