48.5k
To read a JSON file in Python, use chDB as a drop-in replacement for pandas. Change one import line and the code you already write keeps working:
import chdb.datastore as pd
df = pd.read_json("data/events.ndjson", lines=True) # NDJSON: one object per linedf supports the pandas API you already use (indexing, filtering, groupby, merge), but the work runs on ClickHouse's engine instead of pandas, so it stays fast as files grow. There is no server to start and no separate load step.
pip install chdbPrefer the command line? See how to run SQL on a JSON file to do this with clickhouse local.
Read an NDJSON file into a DataFrame
Most "JSON" data you query in bulk is NDJSON (newline-delimited JSON, also called json-lines): one JSON object per line. Pass lines=True and chDB reads it directly, inferring column types from the data:
import chdb.datastore as pd
df = pd.read_json("data/events.ndjson", lines=True)
print(df)
print(df.dtypes)event_id country event_type revenue user items
0 1 GB purchase 49.99 [101, gold] [sku-a, sku-b]
1 2 AU view 0.00 [102, silver] [sku-c]
2 3 GB purchase 19.50 [103, gold] [sku-a]
3 4 IN purchase 5.00 [104, bronze] [sku-d, sku-e, sku-f]
4 5 AU purchase 99.00 [105, gold] []
event_id int64
country str
event_type str
revenue float64
user object
items object
dtype: objectchDB inferred the types automatically: event_id is an integer, revenue is a float. Nested objects (user) and arrays (items) survive as object columns. You did not declare a single dtype.
One thing is different from pandas under the hood: df is not the whole file loaded into memory. It is a lazy, ClickHouse-backed object that records what you asked for and runs it on ClickHouse's engine when you materialize a result: print it, take len(), or call .to_pandas(). That is why it stays fast on large files: it only computes the rows and columns you actually use.
A top-level JSON array (a .json file whose outer structure is [{...}, {...}]) reads without lines=True:
arr_df = pd.read_json("data/events_array.json")Filter and aggregate the way you already do
The pandas you write does not change. Use a boolean mask to filter, then groupby and aggregate:
import chdb.datastore as pd
df = pd.read_json("data/events.ndjson", lines=True)
purchases = (df[df["event_type"] == "purchase"]
.groupby("country")["revenue"].sum()
.sort_values(ascending=False))
print(purchases)country
AU 99.00
GB 69.49
IN 5.00
Name: revenue, dtype: float64Same syntax as pandas, same result. chDB compiles the whole chain into one optimized query and only scans the columns the aggregation touches, rather than materializing the full file first.
Nested objects: extract fields with .apply
When chDB reads an NDJSON file with nested objects, those objects come back as struct columns (arrays of values in field order). After calling .to_pandas(), use .apply to pull out the fields you need:
import pandas as real_pd
pdf = df.to_pandas()
pdf["user_id"] = pdf["user"].apply(lambda x: x[0])
pdf["user_tier"] = pdf["user"].apply(lambda x: x[1])
print(pdf[["event_id", "user_id", "user_tier", "revenue"]])event_id user_id user_tier revenue
0 1 101 gold 49.99
1 2 102 silver 0.00
2 3 103 gold 19.50
3 4 104 bronze 5.00
4 5 105 gold 99.00Nested arrays: flatten with .explode
Arrays come back as list columns. Call .explode() on a real pandas DataFrame to turn each element into its own row:
pdf = df.to_pandas()
exploded = (pdf[["event_id", "items"]]
.explode("items")
.dropna(subset=["items"])
.reset_index(drop=True))
print(exploded)event_id items
0 1 sku-a
1 1 sku-b
2 2 sku-c
3 3 sku-a
4 4 sku-d
5 4 sku-e
6 4 sku-fHand off to real pandas when you need it
When a library downstream needs an actual pandas object (scikit-learn, a plotting call, anything that mutates in place), call .to_pandas() to materialize one:
result = df[df["event_type"] == "purchase"].groupby("country")["revenue"].sum()
pdf_result = result.to_pandas() # a real pandas.Series, in memoryThe usual pattern is to do the heavy filtering and aggregation on the chDB object, where it is fast, then .to_pandas() the small result and continue in ordinary pandas.
Is it faster than pandas.read_json?
On a file big enough that scanning is the actual cost, yes. JSON is a text format, so most of the work is parsing bytes into typed values, and chDB does that in compiled, multi-threaded ClickHouse code rather than the Python layer. The same filter-and-aggregate above on a 2M-row (~146 MB) NDJSON file, best-of-3 with a warm page cache on an Apple M4 Pro (14 cores, 24 GB RAM, macOS); chDB 4.1.8, Python 3.14; best-of-3, warm:
import pandas as pd: 1.714s
import chdb.datastore as pd: 0.134s
speedup: 12.8xAbout 13x here, from the same code with one import changed. The back-to-back ratio is the robust claim; absolute times shift with cache state and concurrent load. The gap widens as files grow, because plain pandas parses and holds the whole file in memory before it can group anything, while chDB streams and aggregates in one pass.
The small case can cut the other way: on a tiny JSON file (a few thousand rows) plain pandas is as fast or faster, because chDB pays a fixed per-query startup cost that dominates when there is barely any data. Reach for chDB when the file is large, or when you want one import to cover both.
Works in Jupyter
Printing a chDB object renders it as a table in a notebook, and .to_pandas() feeds straight into .plot(), joins, or any pandas code. The companion folder ships a run.ipynb you can open and run cell by cell.
Run it yourself
The complete, runnable example is here, with generate.sh to create the sample JSON files plus run.ipynb and a run.py mirror containing the exact code above:
github.com/ClickHouse/examples/tree/main/local-analytics/chdb-json
Working with other formats? Read a CSV file in Python and read a Parquet file in Python use the same drop-in import chdb.datastore as pd pattern; only the reader changes. If you keep re-reading the same JSON, convert it to Parquet once and every later read gets faster.
The same code scales from a JSON file on your laptop to a server to ClickHouse Cloud with no rewrite, so there is nothing to re-learn when the data outgrows one machine.
