Skip to main content
ClickHouse now offers a native JSON column type designed for semi-structured and dynamic data. It’s important to clarify that this is a column type, not a data format—you can insert JSON into ClickHouse as a string or via supported formats like JSONEachRow, but that doesn’t imply using the JSON column type. You should only use the JSON type when the structure of your data is dynamic, not when you simply happen to store JSON.

​
When to use the JSON Type

The JSON type is designed for querying, filtering, and aggregating specific fields within JSON objects that have dynamic or unpredictable structures. It achieves this by splitting JSON objects into separate sub-columns, which dramatically reduces data read and speeds up queries on selected fields compared to alternatives like Map or parsing strings. However, this comes with important trade-offs:
  • Slower INSERTs - Splitting JSON into sub-columns, performing type inference, and managing flexible storage structures makes inserts slower compared to storing JSON as a simple String column.
  • Slower when reading entire objects - If you need to retrieve complete JSON documents (rather than specific fields), the JSON type is slower than reading from a String column. The overhead of reconstructing objects from separate sub-columns provides no benefit when you’re not doing field-level queries.
  • Storage overhead - Maintaining separate sub-columns adds structural overhead compared to storing JSON as a single string value.

​
Use the JSON type when:

  • Your data has a dynamic or unpredictable structure with varying keys across documents
  • Field types or schemas change over time or vary between records
  • You need to query, filter, or aggregate on specific paths within JSON objects whose structure you can’t predict upfront
  • Your use case involves semi-structured data like logs, events, or user-generated content with inconsistent schemas

​
Use a String column (or structured types) when:

  • Your data structure is known and consistent - in this case, use normal columns, Tuple, Array, Dynamic, or Variant types instead
  • JSON documents are treated as opaque blobs that are only stored and retrieved in their entirety without field-level analysis
  • You don’t need to query or filter on individual JSON fields within the database
  • The JSON is simply a transport/storage format, not analyzed within ClickHouse
If JSON is an opaque document that isn’t analyzed inside the database, and only stored and retrieved back, it should be stored as a String field. The JSON type’s benefits only materialize when you need to efficiently query, filter, or aggregate on specific fields within dynamic JSON structures.You can also mix approaches—use standard columns for predictable top-level fields and a JSON column for dynamic sections of the payload.

​
Considerations and tips for using JSON

The JSON type enables efficient columnar storage by flattening paths into subcolumns. But with flexibility comes responsibility. To use it effectively:
  • Specify path types using hints in the column definition to specify types for known subcolumns, avoiding unnecessary type inference.
  • Skip paths if you don’t need the values, with SKIP and SKIP REGEXP to reduce storage and improve performance.
  • Avoid setting max_dynamic_paths too high—large values increase resource consumption and reduce efficiency. As a rule of thumb, keep it below 10,000.
Type hintsType hints offer more than just a way to avoid unnecessary type inference—they eliminate storage and processing indirection entirely. JSON paths with type hints are always stored just like traditional columns, bypassing the need for discriminator columns or dynamic resolution during query time. This means that with well-defined type hints, nested JSON fields achieve the same performance and efficiency as if they were modeled as top-level fields from the outset. As a result, for datasets that are mostly consistent but still benefit from the flexibility of JSON, type hints provide a convenient way to preserve performance without needing to restructure your schema or ingest pipeline.

​
Advanced features

  • JSON columns can be used in primary keys like any other columns. Codecs can’t be specified for a subcolumn.
  • They support introspection via functions like JSONAllPathsWithTypes() and JSONDynamicPaths().
  • You can read nested sub-objects using the .^ syntax.
  • Query syntax may differ from standard SQL and may require special casting or operators for nested fields.
For additional guidance, see ClickHouse JSON documentation or explore our blog post A New Powerful JSON Data Type for ClickHouse.

​
Examples

Consider the following JSON sample, representing a row from the Python PyPI dataset:
Lets assume this schema is static and the types can be well defined. Even if the data is in NDJSON format (JSON row per line), there is no need to use the JSON type for such a schema. Simply define the schema with classic types.
and insert JSON rows:
Consider the arXiv dataset containing 2.5m scholarly papers. Each row in this dataset, distributed as NDJSON, represents a published academic paper. An example row is shown below:
While the JSON here is complex, with nested structures, it is predictable. The number and type of the fields won’t change. While we could use the JSON type for this example, we can also just define the structure explicitly using Tuples and Nested types:
Again we can insert the data as JSON:
Suppose another column called tags is added. If this was simply a list of strings we could model this as an Array(String), but let’s assume you can add arbitrary tag structures with mixed types (notice score is a string or integer). Our modified JSON document:
In this case, we could model the arXiv documents as either all JSON or simply add a JSON tags column. We provide both examples below:
We provide a type hint for the update_date column in the JSON definition, as we use it in the ordering/primary key. This helps ClickHouse to know that this column won’t be null and ensures it knows which update_date subcolumn to use (there may be multiple for each type, so this is ambiguous otherwise).
We can insert into this table and view the subsequently inferred schema using the JSONAllPathsWithTypes function and PrettyJSONEachRow output format:
Alternatively, we could model this using our earlier schema and a JSON tags column. This is generally preferred, minimizing the inference required by ClickHouse:
We can now infer the types of the subcolumn tags.
Last modified on July 2, 2026