How to query a BSON file

To query a BSON file, use clickhouse local. It runs SQL directly on files from the command line, with no server to install. It's part of ClickHouse, so the same query scales to billions of rows when you outgrow your laptop.

Install it with clickhousectl:

Then point it at the file with the file() function:

BSON is MongoDB's binary JSON, the format mongoexport writes, and it carries its own field names and types. ClickHouse reads it as BSONEachRow and infers the schema straight from the file, so you query it immediately with no import step.

See the schema without declaring one #

A BSON document records the name and type of every field, so you never write CREATE TABLE. DESCRIBE prints what ClickHouse read out of the file:

Scalars map to the obvious ClickHouse types. The interesting one is geo: it was a nested sub-document in the BSON, and it comes back as a Tuple with its keys preserved as named fields. Columns are Nullable because a document collection can always omit a field.

Query into nested sub-documents #

MongoDB documents nest. That's the whole point of them, and it's where flattening a BSON export to CSV first loses you information. Here ClickHouse kept the structure, so you address a sub-document field by key with dot notation (geo.country), no unpacking step:

A nested field reads just like a top-level one. (If a sub-document holds values that are all the same type, BSON serialises it as an array and ClickHouse infers Array(...) instead of a named Tuple; you'd then index it positionally as geo[1]. Mixed-type sub-documents keep their keys.)

Filter, group and aggregate directly on the BSON #

The file is a SQL source, so the full ClickHouse dialect works on it (WHERE, GROUP BY, aggregates, joins) with no load step in between, and the nested key works inside the aggregation too:

Convert BSON to a columnar file once #

BSON is a row format built for document storage and transport, not for repeated analytical scans: every query walks each document field by field. If you'll query the same export more than once, convert it to Parquet once and read that instead. It's a single command — SELECT from the BSON, INTO OUTFILE as Parquet:

The nested geo tuple maps cleanly to a Parquet struct, so the structure survives the round-trip. Read it back with the same file() call. See how to query a Parquet file and the convert BSON to JSON guide for other targets.

How fast is it? #

The conversion you skipped was the slow part. On a 1,300,000-row, ~140 MB BSON file (events_large.bson), a filter-and-group-by over every document completes in:

~0.14 seconds of query execution (--time prints it), best of three with the file warm in the OS page cache, on an Apple M4 Pro laptop (14 cores, 24 GB RAM; clickhouse local 26.6.1.117). That decodes every BSON document from scratch on each run; there's no cached table. Measured end to end including process startup, run.sh reports wall-clock at about 0.28s. Convert the same data to Parquet and the scan gets faster again, because Parquet is columnar and reads only the fields a query touches.

The same SQL scales unchanged #

The query you just ran on a laptop file is the same SQL you would run on a ClickHouse server or in ClickHouse Cloud. Nothing about SELECT ... WHERE ... GROUP BY changes. You swap file('events.bson') for a table name and the rest stays put. Prototype against a mongoexport dump on your machine, ship the identical logic to production.

Run it yourself #

The complete, runnable example lives in the ClickHouse examples repo. It has generate.sh (builds the demo document and the ~140 MB perf file), run.sh (every command above), and expected_output.txt:

github.com/ClickHouse/examples → local-analytics/clickhouse-local-bson

Related #

• What is BSON?
• Convert BSON to JSON
• Read a BSON file in Python with chDB
• How to query nested JSON with SQL