When the ClickHouse JSON type fits your event payloads
Use the ClickHouse JSON type when your payloads must be queried inside ClickHouse by path, and keep the payload in a String column when it is only stored and returned to callers. That single question decides most of the design, because the two options carry different storage, query and maintenance costs. Event and log payloads often arrive as JSON whose keys change over time: new fields appear after a client release, nested objects grow, and some keys exist only for a subset of events. The JSON type splits objects into subcolumns, which lets ClickHouse read only the paths a query touches. The ClickHouse JSON best practices also advise storing JSON as String when the data is only stored and retrieved, not queried inside the database. DeployOpen plans and builds this decision on self-managed ClickHouse, with the schema, ingestion path and test queries designed together.
Before you choose, list the queries that will run against the payloads. If analysts filter or aggregate on a handful of known paths, extract those paths into typed columns in the ingestion pipeline and keep the rest as raw JSON. If the paths are truly unpredictable, the JSON type handles the variation better than a fixed schema, but you should confirm how it behaves under your key cardinality and value types before you commit. Ask the team that owns the producers how often new keys appear, whether values change type between versions, and which fields carry personal data, since that affects retention and erasure work. Run both designs on a copy of representative data and compare storage size and the rows read by your slowest queries, using the query log rather than assumptions.
Comparing the JSON type, typed columns and String
The JSON type stores each object as a set of subcolumns, so a path such as user.id in the payload is kept in its own column with its own type. ClickHouse can read only the subcolumns a query names, which keeps filters and aggregations on known paths close to the cost of a typed table. It suits payloads whose keys vary between records but whose important paths are queried often and stay reasonably predictable across the dataset.
- Storage layout: each distinct path becomes a subcolumn, and paths that appear rarely can be stored differently than frequent ones, so the number of distinct keys shapes storage and metadata size.
- Query fit: filters, grouping and aggregation on paths you query regularly, including paths that are not known when the table is created.
- Typing: values are inferred and typed per path, so mixed types under one key need to be understood before you rely on them in queries.
- Cost to change: adding a new path is usually cheap, but changing how existing paths are typed or restructuring large volumes of history requires planning and a test on representative data.
- Operational check: confirm the JSON type behavior for your ClickHouse version in the documentation before you commit a production schema.
Before choosing this option, sample real payloads and count the distinct paths and their types, then test the query patterns on a copy of the data.
Typed columns extract the fields you need at ingest time into named columns with fixed types, such as a UInt64 user identifier or a DateTime event time. The pipeline or materialized view does the parsing once, and every later query reads ordinary columns. This gives the most predictable query behavior and fits the ordering key and skip index design you use elsewhere in the schema.
- Storage layout: each extracted field is a regular column with a declared type, and fields you do not extract are not kept unless you store them elsewhere.
- Query fit: dashboards and recurring reports that filter, sort or group on a stable set of fields, and workloads where ordering keys and skip indexes matter most.
- Typing: types are fixed in the schema, so malformed or unexpected values must be handled at ingest with defaults, casts or a rejection path.
- Cost to change: adding a field means altering the table and deciding whether to backfill history, which can be costly on large tables.
- Ingest responsibility: the parsing logic lives in your pipeline, so it needs its own tests and version control.
Before choosing this option, list the fields your queries actually use and confirm that the rest of the payload can be dropped or stored in a second column.
A String column keeps the raw JSON text as received and leaves parsing to the query. ClickHouse documentation advises this approach when data is only stored and retrieved, not queried inside the database. It is the simplest option to ingest because nothing is parsed at write time, and it preserves the original payload exactly.
- Storage layout: the full document is stored as text in one column, so the database does not index or break out individual fields.
- Query fit: archival, audit trails and payloads that a downstream application reads back whole, rather than filtered or aggregated by field.
- Query cost: each query that reads a field must parse the text, which becomes expensive as volumes grow and filters are applied across many rows.
- Cost to change: changing the payload format is easy at write time, but any analytical need added later usually means building a parsed table and backfilling it.
- Combined pattern: many teams keep the raw String for retention and add typed columns or a materialized view for the fields they query.
Before choosing this option, confirm with the team that will query the data that no analytical filter will run on payload fields.
Which JSON storage option fits each workload
This table compares the JSON type, typed columns and a String column across the factors that decide between them. Read each row against your own payloads, then confirm the choice with a test on representative data before committing. The main takeaway is that the JSON type fits unpredictable keys that you query, while String fits payloads you only store and return.
| Factor | JSON type | Typed columns | String column |
|---|---|---|---|
| Query pattern | Filters and aggregations on paths that vary by record | Filters and aggregations on a fixed, known set of fields | No querying inside the database; the payload is returned as stored |
| Key stability | Suits keys that appear, disappear or change often | Suits keys that are stable and documented | Any keys, since the database does not parse the payload |
| Schema change effort | Low for new paths, since subcolumns are created as keys appear | High, because each new field needs a column and a migration | None inside the database, but any query logic moves to the application |
| Ingestion work | ClickHouse parses each object on insert | Producers or pipelines must map fields to columns before insert | Minimal, the raw text is stored as received |
| Storage and performance | Frequently used paths are stored as subcolumns, which keeps scans narrow; confirm the result with EXPLAIN on your data | Usually the most predictable scan and compression behavior | Reading one field means parsing the whole string at query time |
| Fit for store-and-return payloads | Usually more parsing than the workload needs | Usually more modeling than the workload needs | Strong fit when the database only archives and returns the document |
| What to confirm in a test | Which paths are queried most, and whether path count stays manageable as data grows | Whether the field list is complete and who owns mapping changes | Whether any dashboard or report needs a field, which would move it to one of the other options |
How to set up and test JSON storage on a copy of your data
This sequence lets your team compare the JSON type, typed columns and a String column against a representative sample before any production table changes. The output is a documented design choice backed by subcolumn layout and query plan evidence.
Collect representative payloads
The data engineering owner exports a sample of real event payloads from the source, covering the full range of shapes, optional fields and nested objects that producers send. Include payloads from every producer version still in use, because key sets often drift over time. Record the total row count and the size distribution of the payloads. The step is done when the sample includes the rare keys and malformed records that the production feed contains, and when someone other than the author has reviewed it for coverage. Keep the sample in a restricted location, since payloads often contain personal or sensitive data that should be masked before it leaves the source system.
Define the queried paths
Analysts and application owners list the JSON paths that dashboards, alerts and ad hoc queries actually read, along with the filters, aggregations and joins applied to each. Separate paths that are only stored and returned from paths that appear in WHERE, GROUP BY or ORDER BY clauses. Mark which paths are stable across producer versions and which change or appear irregularly. The step is done when every queried path has an owner, a data type and a usage frequency, and when the list of paths that are never queried inside the database is written down. That second list decides whether a String column is enough for part of the payload.
Create the test tables
On a non-production ClickHouse cluster, the engineer creates three test tables with the same ordering key: one using the JSON type, one with typed columns for the stable queried paths, and one that stores the raw payload as String. Confirm the exact JSON type syntax, supported settings and the default behavior for dynamic paths against the ClickHouse version you run, because these details change between releases. Use the same engine and partition scheme as the target table so later results carry over. The step is done when all three tables exist, the DDL is saved in version control, and the team has written down the ClickHouse version used for the test.
Load the sample into each design
The engineer inserts the same sample into each test table through the same ingestion path that production will use, so batch sizes and parsing behavior match. For the typed design, the load also checks how fields missing from a payload are stored and how type conflicts are handled, since these decide whether rows are rejected or silently altered. Compare row counts against the source export after each load. The step is done when every table holds the expected number of rows, rejected or coerced records are logged with a reason, and the team can explain any difference between the sample and the stored data.
Compare subcolumn layout and storage
The engineer inspects how each design stores the data, using system tables to list the columns and subcolumns created for the JSON type and the on-disk size of each table's compressed and uncompressed columns. Check whether frequently queried paths became their own subcolumns and whether rare paths fell into a shared fallback storage. Record the compressed size of each design side by side. The step is done when the team has a table showing, for each design, the subcolumns or columns created, the compressed size and any paths handled by fallback storage, and when an engineer has confirmed each figure by querying the system tables directly rather than copying it from documentation.
Test query behavior with EXPLAIN
Run each query from the queried-path list against all three tables, capturing the EXPLAIN output and the rows and bytes read reported in the query log. Use the same filters and time ranges for every design so the results are comparable. Pay attention to whether filters on JSON paths push down to the stored subcolumns and whether the ordering key still narrows the scan. Run each query more than once so cache effects do not distort the comparison. The step is done when every queried path has a result for each design, any query that reads far more data than expected has been investigated, and the team has a short list of queries where the design choice changes performance.
Promote the chosen design
The team selects the design that meets the query and storage targets, then writes the final DDL, the ingestion mapping and the list of paths kept as String for later extraction. The design is reviewed with the producers and the consumers, and the change is applied first to a staging environment that receives a copy of live data. Confirm that backup and restore procedures cover the new table definition before it goes live. The step is done when the staging table passes the same query and row count checks, the production change has a rollback plan that does not depend on the test tables, and the decision record names the ClickHouse version and the sample used.
Schema design risks with unpredictable keys
Storing JSON in typed subcolumns gives fast queries, but each path becomes a schema decision. The cards below cover the choices a team should settle before ingestion starts, because they are hard to reverse once dashboards and retained data depend on them.
Unbounded key sets
Every distinct path in an object can become a subcolumn, so a payload that allows user-defined keys can grow the schema without limit. Decide which paths must be typed, which stay in a catch-all field, and who approves a new key. Ask the team that produces the events how often new keys appear and whether any producer can emit arbitrary names. Test with a sample of production payloads before you choose.
Mixed value types under one path
The same key can carry a number in one event and a string or object in another. Decide up front whether that path is coerced, stored with its variant types, or rejected at the producer. Queries against a mixed path need explicit type handling, and a wrong assumption returns plausible but incorrect results. Document the expected type for each path that matters to reporting.
Promoting keys later
A path that starts in the flexible part of the payload may need to become a typed column once queries slow down or dashboards depend on it. Plan how the change happens: whether existing rows are backfilled, how long old and new definitions coexist, and which queries move first. Agree the promotion criteria in advance so that teams do not promote every key that someone once queried.
Dashboards that depend on fragile paths
Dashboards written against nested paths break when a producer renames a key, nests a field differently, or changes its type. Put a stable contract between the producer and the analytics layer, such as views or a versioned schema, so that BI tools query names the team controls. Review dashboard queries whenever the payload contract changes, and test them on a copy before release.
Retention and backup size
Flexible payloads often hold large nested objects, and each new subcolumn adds storage and metadata. Decide how long raw JSON is kept, whether it is stored as String for retrieval only, and how the retention policy applies to the typed copies. Backup and restore time grows with the same data, so estimate the size of a full restore on a test cluster before you commit to a retention period.
Access control over sensitive fields
Unpredictable payloads can carry personal or secret values under keys nobody has reviewed. Identify which paths may contain sensitive data before the table is opened to analysts, then restrict access with row policies, column-level grants or separate tables where the platform supports it. Confirm the exact controls for your ClickHouse version, and make sure the default user does not expose the full payload.
Questions teams ask before storing JSON in ClickHouse
Should we use the ClickHouse JSON type or a String column?
Use the JSON type when your queries filter, aggregate or group on fields inside the payload. Use a String column when the payload is only stored and retrieved whole, because ClickHouse's JSON best practices advise String in that case. The JSON type splits objects into subcolumns, which helps when query paths are known or recur, but it adds schema management work. A useful test is to list the JSON paths your most frequent queries read. If that list is short and stable, typed columns or the JSON type fit. If nothing inside the payload is queried in the database, String is the simpler choice.
How much effort does a schema test take, and what drives it?
The effort depends on how many distinct paths the payloads contain, how much those paths vary between producers, and how many queries must be checked. Start by sampling real payloads from each source and counting the keys, types and nesting depth. Then load a representative sample into a test table on a non-production cluster and run your actual dashboard and investigation queries against it. Teams usually spend the most time on type conflicts, such as one producer sending a number as a string. Confirm the scope of this test with your data owners before quoting it, since the size of the sample and the number of sources set the duration.
Can our own team do this without an outside partner?
Yes, a team with ClickHouse operating experience can usually run the schema test itself, and the ClickHouse docs make the JSON decision a matter of query patterns rather than special tooling. An outside partner adds value when the team has no ClickHouse experience yet, when payloads come from many producers, or when the schema has to pass an internal review. DeployOpen plans and implements self-hosted platforms in your own infrastructure under a written, fixed scope, so you can compare an internal plan with a quote built from the same sample data. Ask any partner to show how they would test the schema on a copy of your data first.
What do we need from our data owners and producers?
You need four things from data owners: the list of fields that matter for reporting, examples of real payloads from each producer, the rules for which fields are required and which are optional, and a named owner who can approve type changes. Producers should be asked whether their keys are stable, how often they add fields, and whether a numeric value can ever arrive as text. Without these answers, the schema test will rest on guesses. Agree on who decides when a new key appears in production, because unplanned keys are the most common reason a JSON schema drifts over time.
How do we change the JSON schema later without losing data?
Changing a JSON layout is safer when you treat it as a versioned data model rather than an in-place edit. A common approach is to keep the raw payload in a String column, add typed columns or a new table for the fields you query, and backfill them from the stored payload. Materialized views can populate new structures as data arrives. Test each change on a copy first, check that old queries still return the same results, and record the change in your schema log. Confirm with your ClickHouse operators how mutations and backfills will affect your cluster load before running them in production.
How is JSON data backed up and restored?
JSON data is backed up the same way as any other ClickHouse table, using BACKUP and RESTORE for tables, partitions or databases to local disk, S3-compatible storage or Azure Blob Storage. Replication keeps copies of the data across replicas, but the ClickHouse backup documentation notes that replication alone does not protect against human error or corruption, so a separate backup is still needed. Keep the raw String payloads in the backup too, because they are the source you can rebuild typed columns from. A backup that has never been restored will probably not restore properly, so schedule restore tests on a spare cluster and record how long they take.
Does storing JSON change our security or compliance scope?
The JSON type does not change access control on its own, but payloads often contain personal or regulated data nested inside fields that are easy to overlook. Map which keys hold such data before you build typed columns, since typed columns make those values easier to query and export. ClickHouse access control covers users, roles, row policies and settings profiles, and the documentation recommends restricting the default user, which starts with all permissions. Confirm with your compliance team which keys need masking or retention rules. DeployOpen prepares systems and evidence for compliance assessments, but auditors and certification bodies issue the actual assessment outcomes.
Security and operations for JSON columns
A JSON column carries whole payloads and their subcolumns into the same tables, so access, logging and recovery decisions apply to both at once. The cards below cover where ClickHouse controls can and cannot separate those fields, and how to prove that backups restore.
Access control at the table level
ClickHouse grants privileges on tables and columns through users and roles, so a JSON column is readable or not as one object unless you restrict it. Row policies can filter which rows a role sees, but they do not hide individual keys inside the payload. Decide before launch whether analysts need the whole document or only specific extracted subcolumns, and test that a restricted role cannot read the raw column.
Row policies for sensitive records
Row policies restrict which rows a user or role can query, which suits multi-tenant payloads where each team should only see its own events. Apply them to every role that reads the table, not only the reporting role. Confirm the policy behaves as expected when queries touch the JSON subcolumns, and review it whenever a new role is created.
Restrict the default user
The default user starts with all permissions, so a JSON table is exposed to anyone who can reach that account on a fresh install. Disable or restrict it, create named users with roles, and use settings profiles to cap resource use per role. Check this during hardening, because a read path that bypasses your roles also bypasses your key-level decisions.
Masking sensitive keys in logs
Query logs and error messages can echo JSON values, including keys such as tokens, national identifiers or contact details that were never meant for log storage. Decide which paths are sensitive, then confirm your log pipeline masks or drops them before they leave the server. Test with a known test value and search the log store for it after a failed insert and after a slow query.
Full and incremental backups with BACKUP
BACKUP and RESTORE cover tables, partitions or whole databases, with full and incremental modes, to local disk, S3-compatible storage or Azure Blob Storage. The JSON column is backed up as part of its table, so the backup includes the raw payloads. Replication does not protect against deletion or corruption, so a backup schedule and off-cluster destination are required.
Test restores on a spare cluster
The backup documentation warns that a backup never test-restored will probably not restore properly when needed. Schedule restores of JSON tables onto a spare cluster, compare row counts and sample payloads, and confirm that subcolumn queries return the same results. Record how long the restore took and whose access is needed, so the procedure can run during an incident.
How DeployOpen works on this
DeployOpen treats JSON storage as a schema decision made against your actual queries, retention and ingestion volume, not a default. We scope the choice in writing, build and test it on representative data in your own ClickHouse environment, then hand over the schema and the operating notes your team needs to run it.
How we scope
- Inventory which JSON payloads are only stored and returned versus filtered, grouped or aggregated inside ClickHouse.
- Document the key paths, their stability and how often new keys appear in production events.
- Agree the written scope, deliverables and timeline before any build work starts, as most engagements are fixed-scope.
How we build
- Compare typed columns, the JSON type and a String column on a copy of representative data and queries.
- Check the chosen layout with EXPLAIN and query logs, and test ingestion behavior against realistic event volumes.
- Set up the agreed table definitions and any materialized views in a pre-production environment you control before production use.
How we hand over
- Deliver the schema, table definitions and the reasoning behind each subcolumn or typed column decision.
- Provide runbook notes on adding new keys, changing types and handling payloads that do not match the schema.
- Walk your engineers through the test results so the next schema change can be reviewed against the same criteria.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer after handover.
- Confirm with your team which ClickHouse release line you run, and review the changelog before each upgrade of the JSON handling.
- Re-test the schema when query patterns or payload shapes change, so storage choices keep matching how the data is used.
