DEV Community

Florian Polster
Florian Polster

Posted on

1

JSON in ClickHouse

I just watched this talk about storing JSON in ClickHouse. It's quite insightful. My takeaways:

  • There are performance-trimmed json-value-extraction functions that avoid parsing the entire blob in favor of performance. JSON in String columns compresses pretty well.
  • There're functions to convert simple JSON to maps and when you store the same data from the talk's example as a map instead of JSON, queries are 4-5x faster.
  • You can insert data into a table with engine=null. Such a table doesn't store any data but it can be the source of a Materialized View.
  • It's pretty common for users to store the JSON in one column and then extract specific values into columns next to that. That will already boost performance if you're smart about it. You can further enhance performance by defining Data-Skipping Indexes on those columns.
  • All the approaches explored in the talk are documented here.
  • There's a beta feature for the future of JSON data in ClickHouse with many under the hood optimizations (essentially, inserted data is schema-inferred and clickhouse will automatically create columns for all fields (even nested) and it will even create new columns as json object with new fields flow in).

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

đź‘‹ Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay