The ClickHouse JSON data type

Mark Needham

This tutorial demonstrates how to use the production-ready JSON data type introduced in ClickHouse 25.3. Using the Blue Sky dataset with 100 million records, we show you how to:

Create tables that handle JSON data effectively Query nested JSON structures with proper syntax Use the caret (^) symbol to access subcolumns Apply type hints to improve query performance Save storage space with the skip_regex option

We compare performance across different table configurations, showing how properly configured JSON tables can significantly reduce query times, in some cases, from 1.9 seconds to 0.2 seconds.

The tutorial covers basic and advanced JSON handling techniques in ClickHouse, helping you make the most of this powerful feature.

• https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse
• https://clickhouse.com/blog/accelerating-clickhouse-json-queries-for-fast-bluesky-dashboards
• https://clickhouse.com/docs/sql-reference/data-types/newjson
• https://clickhouse.com/blog/json-bench-clickhouse-vs-mongodb-elasticsearch-duckdb-postgresql