Opensource internals

ClickHouse's JSON Type: A Column That Is a Tree of Subcolumns

17 min read Jay Hubbert

ClickHouse's JSON/Object type is not a blob: every path becomes a subcolumn, declared paths are typed, discovered paths are bounded, and the overflow spills into a shared map that is sorted per row.

TL;DR: A ClickHouse JSON column is not a string and not a Map. It is a container of subcolumns, one per document path. Paths you declare in the type are typed and get their own stream; paths discovered at insert time become bounded “dynamic” subcolumns; and once that bound is hit, the rest spill into a single shared Array(Tuple(String, String)) whose entries are sorted within each row so a path can still be found by binary search.

JSON in a columnar database is a trap with two edges. The first edge is performance: if you store a document as a String, every query that wants payload.amount has to parse the whole document, per row, at query time. The second edge is correctness of the schema: a String column cannot be filtered, cannot be indexed, cannot be a primary key, and cannot be joined against.

The usual fix is to declare the interesting paths as separate columns. The problem with that is that JSON documents have paths you did not know about, and the set of paths in a log table is a function of the traffic, not of the schema.

ClickHouse’s answer is a type that is honest about that: it has a place for the paths you know, a bounded place for the paths it discovers, and a spill valve for everything else. The interesting part is not the first of those — that is just a Map — it is the second and third, and in particular what happens at merge time, when the server gets to decide, globally and with statistics, which paths deserve to be first-class.

The type

DataTypeObject is parametric in more ways than most ClickHouse types:

cpp 
// src/DataTypes/DataTypeObject.h:20
    static constexpr size_t MAX_TYPED_PATHS = 1000;
    static constexpr size_t MAX_DYNAMIC_PATHS_LIMIT = 10000;
    /// Don't change this constant, it can break backward compatibility.
    static constexpr size_t DEFAULT_MAX_DYNAMIC_PATHS = 1024;
    static constexpr const char * SPECIAL_SUBCOLUMN_NAME_FOR_DISTINCT_PATHS_CALCULATION = "__special_subcolumn_name_for_distinct_paths_calculation";

    /// Prefix character for sub-object subcolumns, e.g. "^`some`.path.path".
    static constexpr char SUB_OBJECT_SUBCOLUMN_PREFIX = '^';
    /// Prefix character for combined literal+sub-object subcolumns, e.g. "@`some`.path.path".
    static constexpr char COMBINED_SUBCOLUMN_PREFIX = '@';

and its state:

cpp 
// src/DataTypes/DataTypeObject.h:114
    SchemaFormat schema_format;
    /// Set of paths with types that were specified in type declaration.
    std::unordered_map<String, DataTypePtr> typed_paths;
    /// The same typed paths in the canonical order `getChild` and `doCloneWithChildren` agree on.
    /// `typed_paths` is a hash map, so an order has to be imposed rather than read off it, and it is
    /// imposed once here so that enumerating the children does not sort or allocate.
    std::vector<std::pair<String, DataTypePtr>> sorted_typed_paths;
    /// Set of paths that should be skipped during data parsing.
    std::unordered_set<String> paths_to_skip;
    /// List of regular expressions that should be used to skip paths during data parsing.
    std::vector<String> path_regexps_to_skip;
    /// Limit on the number of paths that can be stored as subcolumn.
    size_t max_dynamic_paths;
    /// Limit of dynamic types that should be used for Dynamic columns.
    size_t max_dynamic_types;

SKIP and SKIP REGEXP are worth a moment: they let a type declaration refuse paths, which is how you keep a high-cardinality field (request_id, trace_id) from ever becoming a subcolumn.

The type name round-trips, and it is where the typed paths, the skips and the two limits all show up:

cpp 
// src/DataTypes/DataTypeObject.cpp:257
    if (max_dynamic_types != DataTypeDynamic::DEFAULT_MAX_DYNAMIC_TYPES)
    {
        write_separator();
        out << "max_dynamic_types=" << max_dynamic_types;
    }

    if (max_dynamic_paths != DEFAULT_MAX_DYNAMIC_PATHS)
    {
        write_separator();
        out << "max_dynamic_paths=" << max_dynamic_paths;
    }

    for (const auto & [path, type] : sorted_typed_paths)
    {
        write_separator();
        /// We must quote path "SKIP" to avoid its confusion with SKIP keyword.
        if (boost::to_upper_copy(path) == "SKIP")
            out << backQuote(path) << " " << type->getName();
        else
            out << backQuoteIfNeed(path) << " " << type->getName();
    }

    std::vector<String> sorted_skip_paths;
    sorted_skip_paths.reserve(paths_to_skip.size());
    for (const auto & skip_path : paths_to_skip)
        sorted_skip_paths.push_back(skip_path);
    std::sort(sorted_skip_paths.begin(), sorted_skip_paths.end());
    for (const auto & skip_path : sorted_skip_paths)
    {
        write_separator();
        out << "SKIP " << backQuoteIfNeed(skip_path);
    }

So a column declared as

sql 
CREATE TABLE events
(
    ts   DateTime,
    json JSON(id UInt64, `user.name` String, `user.age` UInt8, SKIP `request_id`)
) ENGINE = MergeTree ORDER BY ts;

has a type whose name is JSON(id UInt64, \user.age` UInt8, `user.name` String, SKIP `request_id`)` — typed paths sorted, skips sorted, so that the name is canonical and two equivalent declarations produce the same string.

The column is three containers

ColumnObject holds three different things, and the distinction between them is the whole design:

cpp 
// src/Columns/ColumnObject.h:394
    /// Map path -> column for paths with explicitly specified types.
    /// This set of paths is constant and cannot be changed.
    PathToColumnMap typed_paths;
    /// Sorted list of typed paths. Used to avoid sorting paths every time in some methods.
    VectorWithMemoryTracking<std::string_view> sorted_typed_paths;
    /// Flat vector of typed path column pointers in the same order as sorted_typed_paths.
    /// Used for cache-friendly iteration in hot loops (e.g., default filling).
    VectorWithMemoryTracking<IColumn *> sorted_typed_path_columns;
    /// Map path -> column for dynamically added paths. All columns
    /// here are Dynamic columns. This set of paths can be extended
    /// during inserts into the column.
    PathToColumnMap dynamic_paths;
    /// Sorted list of dynamic paths. Used to avoid sorting paths every time in some methods.
    SetWithMemoryTracking<std::string_view> sorted_dynamic_paths;

    /// Store and use pointers to ColumnDynamic to avoid virtual calls.
    /// With hundreds of dynamic paths these virtual calls are noticeable.
    PathToDynamicColumnPtrMap dynamic_paths_ptrs;
    /// Shared storage for all other paths and values. It's filled
    /// when the number of dynamic paths reaches the limit.
    /// It has type Array(Tuple(String, String)) and stores
    /// an array of pairs (path, binary serialized dynamic value) for each row.
    WrappedPtr shared_data;

    /// Maximum number of dynamic paths. If this limit is reached, all new paths will be inserted into shared data.
    /// This limit can be different for different instances of Object column. For example, we can decrease it
    /// in `chooseDynamicStructureForMerge` or `takeExactDynamicStructureFrom` before merge.
    size_t max_dynamic_paths;

Three kinds of path, three kinds of storage:

text 
  JSON document
       │
       ├── typed path "id"          → a real UInt64 column        (never changes)
       ├── typed path "user.name"   → a real String column        (never changes)
       │
       ├── dynamic path "actor.id"  → a ColumnDynamic column      (bounded, grows at insert)
       ├── dynamic path "org.url"   → a ColumnDynamic column
       │
       └── everything else          → shared_data: Array(Tuple(String, String))
                                      (path, binary-encoded value) per row

The typed paths are real columns of the declared type. The dynamic paths are ColumnDynamic columns, so a path that is a number in one row and a string in another still works — the column is a variant of the types it has seen. And the shared data is a single column of (path, value) pairs, where the value is the binary-serialized dynamic value.

The spill valve

Adding a dynamic path is bounded, and the boundary is where the interesting behaviour lives:

cpp 
// src/Columns/ColumnObject.cpp:515
ColumnDynamic * ColumnObject::tryToAddNewDynamicPath(std::string_view path)
{
    if (dynamic_paths.size() == max_dynamic_paths)
        return nullptr;

    auto new_dynamic_column = ColumnDynamic::create(max_dynamic_types);
    new_dynamic_column->reserve(shared_data->capacity());
    new_dynamic_column->insertManyDefaults(size());
    auto it = dynamic_paths.emplace(path, std::move(new_dynamic_column)).first;
    auto it_ptr = dynamic_paths_ptrs.emplace(path, assert_cast<ColumnDynamic *>(it->second.get())).first;
    sorted_dynamic_paths.insert(it->first);
    /// Invalidate statistics because they refer to the old set of dynamic paths.
    statistics.reset();
    return it_ptr->second;
}

tryToAddNewDynamicPath returns nullptr when the limit is hit, and the caller falls back to writing the path into the shared data instead. So the limit is not an error — it is a routing decision.

The shared data column is Array(Tuple(String, String)):

cpp 
// src/DataTypes/DataTypeObject.cpp:896
const DataTypePtr & DataTypeObject::getTypeOfSharedData()
{
    /// Array(Tuple(String, String))
    thread_local static DataTypePtr type = std::make_shared<DataTypeArray>(std::make_shared<DataTypeTuple>(DataTypes{std::make_shared<DataTypeString>(), std::make_shared<DataTypeString>()}, Names{"paths", "values"}));
    return type;
}

and it is reached through a pair of ColumnStrings — one for the paths, one for the values — plus offsets:

cpp 
// src/Columns/ColumnObject.h:231
    const ColumnPtr & getSharedDataPtr() const { return shared_data; }
    ColumnPtr & getSharedDataPtr() { return shared_data; }
    IColumn & getSharedDataColumn() { return *shared_data; }

    const ColumnArray & getSharedDataNestedColumn() const { return assert_cast<const ColumnArray &>(*shared_data); }
    ColumnArray & getSharedDataNestedColumn() { return assert_cast<ColumnArray &>(*shared_data); }

    ColumnArray::Offsets & getSharedDataOffsets() { return assert_cast<ColumnArray &>(*shared_data).getOffsets(); }
    const ColumnArray::Offsets & getSharedDataOffsets() const { return assert_cast<const ColumnArray &>(*shared_data).getOffsets(); }

    std::pair<ColumnString *, ColumnString *> getSharedDataPathsAndValues()
    {
        auto & column_array = assert_cast<ColumnArray &>(*shared_data);
        auto & column_tuple = assert_cast<ColumnTuple &>(column_array.getData());
        return {assert_cast<ColumnString *>(&column_tuple.getColumn(0)), assert_cast<ColumnString *>(&column_tuple.getColumn(1))};
    }

The trick that keeps the spill from being useless is that the paths within one row are sorted, so a path can be found by binary search rather than by scanning:

cpp 
// src/Columns/ColumnObject.h:295
    /// Paths in shared data are sorted in each row. Use this method to find the lower bound for specific path in the row.
    static size_t findPathLowerBoundInSharedData(std::string_view path, const ColumnString & shared_data_paths, size_t start, size_t end);
    /// Insert all the data from shared data with specified path to dynamic column.
    static void fillPathColumnFromSharedData(IColumn & path_column, std::string_view path, const ColumnPtr & shared_data_column, size_t start, size_t end);

and the implementation is a real std::lower_bound over a range of the ColumnString:

cpp 
// src/Columns/ColumnObject.cpp:2317
    Iterator start_it(&shared_data_paths, start);
    Iterator end_it(&shared_data_paths, end);
    auto it = std::lower_bound(start_it, end_it, path);
    return it.index;
}

So SELECT json.some_rare_path FROM t still works, and it costs a binary search per row over that row’s spilled paths, not a scan of the whole document.

Reading a path

The payoff of all this is that json.path is a subcolumn. ClickHouse’s subcolumn machinery lets a query name a path and read only the storage that holds it. DataTypeObject::getDynamicSubcolumnInfo is where the resolution happens, and it is a large function because a subcolumn name can be a lot of things: a typed path, a dynamic path, a sub-object (^ prefix), a combined literal+sub-object (@ prefix), or a type-hinted path (:Type).

There is even a synthetic subcolumn whose only job is to expose the list of paths:

cpp 
// src/DataTypes/DataTypeObject.cpp:614
std::unique_ptr<IDataType::SubcolumnInfo> DataTypeObject::getDynamicSubcolumnInfo(std::string_view subcolumn_name, const SubstreamData & data, size_t initial_array_level, bool throw_if_null) const
{
    /// Check if it's a special subcolumn used for distinct paths calculation.
    if (subcolumn_name == SPECIAL_SUBCOLUMN_NAME_FOR_DISTINCT_PATHS_CALCULATION)
    {
        std::vector<String> typed_path_names;
        typed_path_names.reserve(typed_paths.size());
        for (const auto & [path, _] : typed_paths)
            typed_path_names.push_back(path);

        auto res = std::make_unique<SubcolumnInfo>();
        res->data = SubstreamData(SerializationObjectDistinctPaths::create(typed_path_names));
        res->data.type = std::make_shared<DataTypeArray>(std::make_shared<DataTypeString>());
        /// If column was provided, we should create a column for the requested subcolumn.
        if (data.column)
        {
            const auto & object_column = assert_cast<const ColumnObject &>(*data.column);
            auto result_column = res->data.type->createColumn();
            if (!object_column.empty())
            {
                auto & result_array_column = assert_cast<ColumnArray &>(*result_column);
                auto & result_paths_column = assert_cast<ColumnString &>(result_array_column.getData());
                for (const auto & path : typed_path_names)
                    result_paths_column.insertData(path.data(), path.size());
                for (const auto & [path, _] : object_column.getDynamicPaths())
                    result_paths_column.insertData(path.data(), path.size());
                const auto [shared_data_paths, _] = object_column.getSharedDataPathsAndValues();
                result_paths_column.insertRangeFrom(*shared_data_paths, 0, shared_data_paths->size());
                result_array_column.getOffsets().push_back(result_paths_column.size());
                result_array_column.insertManyDefaults(object_column.size() - 1);
            }
            res->data.column = std::move(result_column);
        }

Notice how the three containers are stitched back together here: the typed path names, then the dynamic path names, then the shared data’s path column. That is the full path list of the column, reconstructed from three different storage layouts.

The visible behaviour is what you would hope:

sql 
CREATE TABLE test (json String) ENGINE=MergeTree ORDER BY tuple();
INSERT INTO test VALUES ('{"a" : 42}'), ('{"a" : 43, "b" : "Hello"}'), ('{"a" : 44, "b" : [1, 2, 3]}'), ('{"c" : "2020-01-01"}');
ALTER TABLE test MODIFY COLUMN json JSON;
SELECT json, json.a, json.b, json.c FROM test;
text 
┌─json─────────────────────────┬─json.a─┬─json.b──┬─json.c─────┐
│ {"a":"42"}                   │ 42     │ ᴺᵁᴸᴸ    │ ᴺᵁᴸᴸ       │
│ {"a":"43","b":"Hello"}       │ 43     │ Hello   │ ᴺᵁᴸᴸ       │
│ {"a":"44","b":["1","2","3"]} │ 44     │ [1,2,3] │ ᴺᵁᴸᴸ       │
│ {"c":"2020-01-01"}           │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ    │ 2020-01-01 │
└──────────────────────────────┴────────┴─────────┴────────────┘

json.b is Dynamic (it is a string in one row and an array in another), and json.c is a DateTime because the JSON parser inferred it from the string.

The streams

On disk, an Object column is not one stream. The serialization splits it into a structure stream and a data stream, and the data stream is further split per path:

cpp 
// src/DataTypes/Serializations/SerializationObject.h:23
    struct SerializationVersion
    {
        enum Value
        {
            /// V1 serialization:
            /// - ObjectStructure stream:
            ///     <max_dynamic_paths parameter>
            ///     <actual number of dynamic paths>
            ///     <sorted list of dynamic paths>
            ///     <statistics with number of non-null values for dynamic paths> (only in MergeTree serialization)
            ///     <statistics with number of non-null values for some paths in shared data> (only in MergeTree serialization)
            /// - ObjectData stream:
            ///   - ObjectTypedPath stream for each column in typed paths
            ///   - ObjectDynamicPath stream for each column in dynamic paths
            ///   - ObjectSharedData stream shared data column.
            V1 = 0,
            /// V2 serialization: the same as V1 but without max_dynamic_paths parameter in ObjectStructure stream.
            V2 = 2,
            /// V3 serialization: the same as V2 but with 2 additions:
            ///   - additional information about shared data serialization version that goes after list of dynamic paths.
            ///   - additional bool flag before statistics that indicates if there are any statistics serialized.
            V3 = 4,

So a part containing an Object column with 3 typed paths and 40 dynamic paths has, at the file level: one structure stream, one stream per typed path, one stream per dynamic path, and one shared-data stream. The structure stream records the set of dynamic paths, sorted, because that set is not known from the type:

cpp 
// src/DataTypes/Serializations/SerializationObject.cpp:393
    /// Write all dynamic paths in sorted order.
    object_state->sorted_dynamic_paths.reserve(dynamic_paths.size());
    for (const auto & [path, _] : dynamic_paths)
        object_state->sorted_dynamic_paths.push_back(path);
    std::sort(object_state->sorted_dynamic_paths.begin(), object_state->sorted_dynamic_paths.end());

    /// In V1 version we had max_dynamic_paths parameter written, but now we need only actual number of dynamic paths.
    /// For compatibility we need to write V1 version sometimes, but we should write number of dynamic paths instead of
    /// max_dynamic_paths (because now max_dynamic_paths can be different in different serialized columns).
    if (serialization_version.value == SerializationVersion::V1)
        writeVarUInt(object_state->sorted_dynamic_paths.size(), *stream);

    writeVarUInt(object_state->sorted_dynamic_paths.size(), *stream);
    for (const auto & path : object_state->sorted_dynamic_paths)
        writeStringBinary(path, *stream);

The per-path streams are what makes a skip index on json.a possible and what makes reading json.a not read json.b.

Statistics, and the merge-time decision

This is where the type stops being a storage format and starts being a planner. Every Object column in a MergeTree part carries statistics: for each dynamic path, how many non-null values it had; and for some of the paths in shared data, the same.

cpp 
// src/Columns/ColumnObject.h:21
    struct Statistics
    {
        /// Statistics for dynamic paths: (path) -> (total number of not-null values).
        UnorderedMapWithMemoryTracking<String, size_t> dynamic_paths_statistics;
        /// Statistics for paths in shared data: (path) -> (total number of not-null values).
        /// We don't store statistics for all paths in shared data but only for some subset of them
        /// (is 10000 a good limit? It should not be expensive to store 10000 paths per part)
        static const size_t MAX_SHARED_DATA_STATISTICS_SIZE = 10000;
        UnorderedMapWithMemoryTracking<String, size_t, StringHashForHeterogeneousLookup, StringHashForHeterogeneousLookup::transparent_key_equal> shared_data_paths_statistics;
    };

The statistics are written into the part’s structure stream (see the V1 description above), so they travel with the data.

Now the payoff. When MergeTree merges several parts, each of which may have a different set of dynamic paths, the merged part must have one consistent set — but which paths should win? The answer is: the ones that actually carry data, measured across the whole merge:

cpp 
// src/Columns/ColumnObject.cpp:2021
    /// We want to construct resulting set of dynamic paths with paths that have least number of null values in source columns
    /// and insert the rest paths into shared data if we exceed the limit of dynamic paths.
    /// First, collect all dynamic paths from all source columns and calculate total number of non-null values.
    /// We read source statistics to make path selection decisions, but do not update statistics in the result.
    UnorderedMapWithMemoryTracking<String, size_t> path_to_total_number_of_non_null_values;
    for (const auto & source_column : source_columns)
    {
        const auto & source_object = assert_cast<const ColumnObject &>(*source_column);
        /// During deserialization from MergeTree we will have statistics from the whole
        /// data part with number of non null values for each dynamic path.
        const auto & source_statistics = source_object.getOrCalculateStatistics();
        for (const auto & [path, column_ptr] : source_object.dynamic_paths_ptrs)
        {
            auto it = path_to_total_number_of_non_null_values.find(path);
            if (it == path_to_total_number_of_non_null_values.end())
                it = path_to_total_number_of_non_null_values.emplace(path, 0).first;
            size_t size = column_ptr->size() - column_ptr->getNumberOfDefaultRows();
            auto statistics_it = source_statistics->dynamic_paths_statistics.find(path);
            if (statistics_it != source_statistics->dynamic_paths_statistics.end())
                size = statistics_it->second;
            it->second += size;
        }

        /// Add paths from shared data statistics. It can help extracting frequent paths
        /// from shared data to dynamic paths.
        for (const auto & [path, size] : source_statistics->shared_data_paths_statistics)
        {
            auto it = path_to_total_number_of_non_null_values.find(path);
            if (it == path_to_total_number_of_non_null_values.end())
                it = path_to_total_number_of_non_null_values.emplace(path, 0).first;
            it->second += size;
        }
    }

Note the last loop: a path that was spilled into shared data in some earlier part can be promoted back to a first-class dynamic path if the merged statistics say it is common. The classification is not permanent; it is a function of the data, re-decided at every merge.

Then the selection, with a deterministic tie-break:

cpp 
// src/Columns/ColumnObject.cpp:2063
    /// Check if the number of all dynamic paths exceeds the limit.
    if (path_to_total_number_of_non_null_values.size() > max_dynamic_paths)
    {
        /// Sort paths by total number of non null values.
        VectorWithMemoryTracking<std::pair<size_t, std::string_view>> paths_with_sizes;
        paths_with_sizes.reserve(path_to_total_number_of_non_null_values.size());
        for (const auto & [path, size] : path_to_total_number_of_non_null_values)
            paths_with_sizes.emplace_back(size, path);

        /// If sizes are equal, sort by path names in ascending order (for easier testing purposes).
        const auto compare_paths = [](const auto & left, const auto & right)
        {
            return std::tuple(right.first, left.second) < std::tuple(left.first, right.second);
        };
        sortAndKeepTop(paths_with_sizes, max_dynamic_paths, compare_paths);

        /// Fill dynamic_paths with first max_dynamic_paths paths in sorted list.
        for (const auto & [_, path] : paths_with_sizes)
        {
            auto it = dynamic_paths.emplace(path, ColumnDynamic::create(max_dynamic_types)).first;
            dynamic_paths_ptrs.emplace(path, assert_cast<ColumnDynamic *>(it->second.get()));
            sorted_dynamic_paths.insert(it->first);
        }
    }

sortAndKeepTop is a partial sort (std::nth_element then std::sort) — it does not need a full ordering of the losers, only of the winners. The tie-break by path name is there “for easier testing purposes”, which is a small honesty that every deterministic system needs.

The result is visible in the documentation’s own example: after merging parts with paths a, b, c, d, e and a limit of 3, the merged part keeps a, b, c and pushes d, e into shared data.

text 
┌─count()─┬─dynamic_paths─┬─shared_data_paths─┬─_part─────┐
│      15 │ ['a','b','c'] │ ['d','e']         │ all_1_5_2 │
└─────────┴───────────────┴───────────────────┴───────────┘

The shared data on disk

In memory, shared data is a Map(String, String). On disk it has four serializations, and the choice is a trade-off between write speed and subcolumn read speed:

  • map — a single Map(String, String) column. Efficient to write, efficient to read the whole Object column, inefficient to read one path (you read the whole map and extract in memory).
  • map_with_buckets — N map columns, each holding a subset of the paths (8 buckets for Compact parts, 32 for Wide by default, max 256). Reading one path reads one bucket.
  • advanced — a purpose-built structure with per-path marks and substreams, so reading one path reads only that path’s data. It is inefficient to write.
  • advanced_chunked — advanced with the rows split into chunks (8192 rows by default) so that a merge does not have to materialise everything at once.

That is a four-way knob on a single design decision: how much do you care about reading one path versus writing many?

Trade-offs

What the design buys:

  • Paths are columns. json.user.id is a real subcolumn with its own stream, so it can be filtered, indexed, sorted, and used in a primary key.
  • Unbounded input, bounded structure. A million distinct paths do not become a million columns; the excess lands in one shared column, and the paths that matter are promoted back at merge time.
  • The schema stays honest. Typed paths are typed; dynamic paths are Dynamic and report the types they actually contain; the shared data reports nothing and is clearly a fallback.
  • The decision is data-driven and revisable. Which paths are first-class is decided by merge-time statistics, not by a human’s guess at insert time.
  • Reads degrade gracefully. A spilled path is found by binary search within its row, not by a scan.

What it costs:

  • Complexity is severe. SerializationObjectSharedData.cpp is 1900 lines and SerializationObject.cpp is 1472, and there are four on-disk formats for the shared data alone. The type has ObjectTypedPath, ObjectDynamicPath, ObjectSharedData, ObjectDistinctPaths, ObjectCombinedPath, SubObject, SubObjectSharedData serializations.
  • The structure is not stable. A query that reads json.x as a dynamic subcolumn and a query that reads it from shared data can hit different code paths with different performance, depending on which part it reads and what the merge did.
  • Statistics cost space and time. Every dynamic path’s non-null count is written into the part, and up to 10 000 shared-data paths are tracked.
  • The merge has to make global decisions. Choosing the top-N paths means a merge can demote a path that a user is used to reading as a subcolumn, changing the performance of somebody’s query without changing its result.
  • The dynamic path limit is a real limit. With max_dynamic_paths=1024 by default, a document with more than 1024 distinct paths will have its overflow go to shared data, and reading those paths is a per-row binary search rather than a column read.

The bargain, in one sentence: ClickHouse’s JSON type gives you the shape of a schema without requiring you to know it, and pays for that with a merge-time planner that re-decides, from statistics, which parts of the shape deserve to be fast.

ClickhouseInternalsJSONTypesStorage