v.22.3Experimental Feature

New Object Data Type for Storing and Querying Semi-Structured Data

Official CH changelog text
New data type Object(<schema_format>), which supports storing of semi-structured data (for now JSON only). Data is written to such types as string. Then all paths are extracted according to format of semi-structured data and written as separate columns in most optimal types, that can store all their values. Those columns can be queried by names that match paths in source data. E.g data.key1.key2 or with cast operator data.key1.key2::Int64.
Introduces a new data type Object(<schema_format>) for storing semi-structured data (currently JSON). It extracts paths from the data and stores them as separate columns with optimal data types, allowing direct querying of nested fields.

Why it matters

This feature enables efficient storage and querying of semi-structured data within ClickHouse by automatically extracting nested fields into dedicated columns. It solves the problem of handling JSON-like data structures without sacrificing query performance or requiring complex parsing at query time.

How to use it

Define a column with the Object('JSON') data type when creating a table. Insert JSON data as strings into this column. Access nested fields using dot notation, e.g., data.key1.key2, or cast them to specific types using data.key1.key2::Int64 in queries.

Related resources

PreviousNext