Finding a Practical Analytics Format for Structured JSON Logs

Structured JSON logs are easy to produce and hard to analyze at scale. They carry useful context, but...

Structured JSON logs are easy to produce and hard to analyze at scale. They carry useful context, but that context is nested, optional, inconsistent, and often wider than expected. Before the data can be queried comfortably, it usually has to become a table.

The question is not only "can we flatten it?" The more useful question is:

After flattening, what should the output be?

CSV is simple and fast. Parquet is compact and portable. DuckDB gives a ready-to-query local database. SQLite is widely available. Direct database APIs look convenient from Go, but may not behave like bulk loaders.

This note walks through a small discovery process: define realistic shapes, run the same flattening flow through several output types, and compare write/export cost plus resulting artifact size.

The Contestants

The tested output types were:

• CSV file: plain text table output, the simplest baseline.
• Parquet Snappy: portable columnar file with fast compression.
• Parquet Zstd: portable columnar file with stronger compression.
• DuckDB CLI stdin: stream CSV into the duckdb CLI and create a database table. This path relies on DuckDB's CSV reader with automatic type detection rather than a hand-declared schema.
• SQLite CLI import: stream CSV into the sqlite3 CLI and create a database table.
• SQLite direct inserts: write rows through the Go SQLite driver.
• DuckDB native appender experiment: write rows through a native DuckDB Go appender driver.

The CLI output types matter because they behave like bulk loaders. They let flatjsonl focus on flattening and streaming rows, while the database process handles ingestion in its optimized path.

The flatjsonl Flow

flatjsonl uses two passes:

1. Scan the input to discover keys and infer column types.
2. Read the input again, flatten each JSON object, and write rows to the chosen output.

The first pass is mostly independent of output format. For comparing CSV, Parquet, SQLite, and DuckDB, the second pass is more interesting: it includes flattening, value conversion, writing/importing, compression, and finalization.

So the main timing metric below is Export time:

Export time = total wall time - key scan time

This intentionally ignores the common discovery pass and focuses on the output-side cost.

The Data Shapes

Flattened JSON performance depends heavily on shape. Three patterns were used:

The exact field names are not important here. The important properties are row count, column count, type mix, string cardinality, sparse optional fields, and how much text has to move through the writer.

All numbers are approximate. These are production-shaped samples rather than controlled synthetic fixtures. The goal is to build intuition about tradeoffs.

Reading The Results

The tables use relative cost indexes.

100% is the best measured result in that scenario. Higher is worse.

For example:

• 250% write/export time means about 2.5x slower than the fastest path for that data shape.
• 250% artifact size means about 2.5x larger than the smallest artifact for that data shape.

Write/Export Time

This table excludes key scanning and focuses on flattening plus writing/importing.

The fastest absolute export times were:

The narrow case is almost flat: CSV, Parquet, DuckDB CLI, and SQLite CLI are all within a few percent. In the normal and wide cases, writer choice starts to matter.

Artifact Size

This table compares resulting file/database size.

The winners were:

CSV wins on write speed, but it is consistently large. Compressed Parquet and DuckDB are much more storage-efficient.

The native DuckDB appender path was an experiment, not the final export implementation. Its database schema was not identical to the CLI path: it included an extra sequence primary key and used less precise types for some fields. Treat its size and time as a cautionary data point for row-wise appender ingestion, not as a tuned DuckDB baseline.

The size gap is also a useful reminder that DuckDB is columnar. Physical size is sensitive to column types, compression opportunities, and how data is loaded. SQLite behaved differently in the same experiment: CLI import and direct inserts produced similarly sized files even when their declared schemas differed, because SQLite stores row records with dynamic typing rather than compressed column segments.

Sample Analytical Query

Export speed is only half of the story. The point of making a table is usually to query it.

As a small read-side check, I ran a grouped count over one string dimension:

SELECT count(1), os_name
FROM flatjsonl
GROUP BY os_name
ORDER BY count(1);

For CSV and Parquet, DuckDB queried the files directly. They were not imported into a database first. SQLite databases were queried with sqlite3.

These timings are single local runs and should be read as rough order-of-magnitude results.

No indexes were created for this query. That is intentional: the goal is to compare raw analytical scan behavior of the artifacts as produced by the export step. Adding indexes, especially to SQLite or DuckDB tables, could change query performance drastically for selective filters or repeated lookups. At that point the benchmark would be measuring index design and maintenance cost rather than the default export artifact.

Cells show relative query cost and absolute time. Percentages are normalized independently down each data-shape column, because the datasets differ in row count, width, and value distribution. 100% is the fastest result in that column, and higher percentages are slower.

This query is favorable to columnar formats: it touches one dimension column and computes a small aggregation. DuckDB database files and Parquet are very fast. CSV has to parse text again and gets slower as the file gets wider/larger. SQLite is functional, but slower for this analytical scan.

The wide result highlights an important distinction: Parquet is columnar, but it is still an external file format. DuckDB can query it efficiently, but it still has to read Parquet metadata, decode Parquet pages and adapt the data into DuckDB execution vectors. A DuckDB database is already in DuckDB's native storage layout, with its own column segments, compression, statistics and execution-friendly metadata. For repeated DuckDB queries, especially ones touching a tiny subset of a very wide table, the native database can be noticeably faster even when Parquet is also columnar.

I also repeated the query with a second dimension that has noticeably higher cardinality than a very low-cardinality first field (os_name):

SELECT count(1), os_name, country
FROM flatjsonl
GROUP BY os_name, country
ORDER BY count(1);

This makes every path slower than the earlier one-field query, but the overall ranking stays the same. Row-oriented databases pay much more once the grouping key widens from one dimension to two.

DuckDB stayed surprisingly fast even after widening the grouping key. The likely reason is that the query still touches only a tiny subset of the table: two projected dimension columns plus the row count. In a columnar, vectorized engine, adding one more grouped column is much cheaper than widening a row-store scan across the full record. The query does get slower, but not dramatically, because it still reads only a few columns and the resulting group count remains manageable.

What The Shapes Teach

Narrow

The narrow shape has only 23 columns. Most values are short strings with low or medium cardinality, plus one numeric measure and a few boolean flags.

For this shape, output choice barely affects write/export time. The data pass itself dominates. CSV, Parquet, DuckDB CLI, and SQLite CLI all finish in the same practical range.

Artifact size still changes dramatically. CSV and SQLite are roughly 8-9x larger than Zstd Parquet. DuckDB CLI and Zstd Parquet are both compact enough that the choice mainly depends on whether the desired result is a database file or a portable columnar file.

Normal

The normal shape has 118 columns. It is still string-heavy, but it includes enough numeric measures, duration/window fields, timestamps, request metadata, and boolean flags to look like a practical operational analytics table.

This is where writer cost becomes visible. CSV remains fastest, but produces a very large artifact. Parquet Snappy adds moderate overhead and cuts size substantially. Parquet Zstd costs a bit more time and gives the smallest file.

DuckDB CLI is slower than Parquet, but it creates a ready-to-query database in one step. SQLite CLI remains workable, but its size and write cost are less attractive. Direct row-wise database APIs are already a poor fit.

Wide

The wide shape has fewer rows but about 1680 columns. It is sparse and string-heavy: optional identifiers, free-form strings, long text fields, and nested dimensions dominate the schema. Numeric and boolean fields are mixed in, but they are not what makes the workload difficult.

This is where format and ingestion path dominate. Parquet costs more CPU than CSV, but cuts storage substantially. DuckDB CLI produces the smallest measured artifact and avoids a separate import step, but costs more write/export time than Parquet.

SQLite CLI is much faster than direct SQLite inserts, but still slow and large compared with Parquet or DuckDB. Direct row-wise database APIs spend too much time crossing driver boundaries, converting values, appending/binding thousands of cells per row, and managing memory.

Practical Conclusions

Use CSV when the main goal is fastest raw export and temporary text output is acceptable.

Use Parquet Snappy as the default portable analytics artifact. It is much smaller than CSV and has acceptable write overhead for narrow and normal shapes.

Use Parquet Zstd when storage or transfer size matters more than a bit of extra CPU.

Use DuckDB CLI when the desired output is directly a DuckDB database. It is competitive on import across narrow, normal, and wide shapes, and then gives excellent query performance once the data is loaded. In these experiments it was especially strong for analytical scans that touched only a small subset of columns.

Use SQLite CLI when SQLite compatibility is required. Prefer CLI bulk loading over direct inserts.

Avoid direct row-wise database insertion for uncertain production JSON shapes unless the dataset is small or the schema is known to stay narrow.

The broader lesson is that "structured logs" are not one benchmark. Row count, column count, string cardinality, sparse optional fields, and output format all matter. For exploratory analytics at scale, the safest default is to produce a portable columnar artifact first, and only create a database file directly when that is the actual target.