---
title: "How to convert Avro to JSON"
date: "2026-06-15"
author: "Al Brown"
category: "Engineering"
excerpt: "Convert an Avro file to JSON with one command using clickhouse-local. The schema is read from the Avro file itself, types are carried into JSON, and there is no upload and no server."
---

# How to convert Avro to JSON

To convert Avro to JSON, use [`clickhouse local`](/resources/engineering/what-is-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`](https://clickhouse.com/docs/install):


```bash
curl https://clickhouse.com/cli | sh   # install clickhousectl
clickhousectl local use latest         # download ClickHouse and put it on your PATH
```

Then point it at the file and write it out as JSON Lines:


```bash
clickhouse local -q "SELECT * FROM file('events.avro') INTO OUTFILE 'events.jsonl' FORMAT JSONEachRow"
```

```response
{"event_id":1,"event_type":"login","country":"GB","amount":0,"ts":1767225600}
{"event_id":2,"event_type":"purchase","country":"US","amount":0.01,"ts":1767225601}
{"event_id":3,"event_type":"logout","country":"DE","amount":0.02,"ts":1767225602}
{"event_id":4,"event_type":"signup","country":"FR","amount":0.03,"ts":1767225603}
{"event_id":5,"event_type":"login","country":"IN","amount":0.04,"ts":1767225604}
```

Avro embeds its own schema in the file header, so `clickhouse local` reads the column names and types from the file itself with no structure argument. The file is converted in place, with no upload and no import step first.

## The schema comes from the Avro file

Avro is self-describing: the writer's schema is stored in the file header. `clickhouse-local` reads it, so you never supply column names or types by hand. Confirm what was read with `DESCRIBE`:


```bash
clickhouse local -q "DESCRIBE file('events.avro')"
```

```response
event_id	Int64
event_type	String
country	String
amount	Float64
ts	Int32
```

Those types are then carried into the JSON output: `event_id` and `ts` are numbers, `event_type` and `country` are strings, `amount` is a float. No quoting guesswork the way a hand-rolled text converter would do.

## The one type gotcha: timestamps arrive as integers

Look again at `ts` above. It came back as `Int32`, and in the first JSON output it showed up as `1767225600`, not a readable timestamp. That is not a bug. Avro stores timestamps as a logical type over an integer (seconds or milliseconds since the Unix epoch), and the integer is what lands in JSON by default.

If you want a human-readable timestamp in the JSON, cast it during the `SELECT`:


```bash
clickhouse local -q "
SELECT event_id, event_type, country, amount, ts::DateTime AS ts
FROM file('events.avro')
INTO OUTFILE 'events_typed.jsonl' TRUNCATE FORMAT JSONEachRow"
```

```response
{"event_id":1,"event_type":"login","country":"GB","amount":0,"ts":"2026-01-01 00:00:00"}
{"event_id":2,"event_type":"purchase","country":"US","amount":0.01,"ts":"2026-01-01 00:00:01"}
{"event_id":3,"event_type":"logout","country":"DE","amount":0.02,"ts":"2026-01-01 00:00:02"}
{"event_id":4,"event_type":"signup","country":"FR","amount":0.03,"ts":"2026-01-01 00:00:03"}
{"event_id":5,"event_type":"login","country":"IN","amount":0.04,"ts":"2026-01-01 00:00:04"}
```

`ts::DateTime` is shorthand for `CAST(ts AS DateTime)`. Use `::DateTime64(3)` if your Avro timestamps are millisecond precision. This is the kind of control you do not get from a generic upload converter, which can only guess.

## Options worth knowing

You rarely want a blind, whole-file dump. Because you convert with SQL, you can shape the output as you go:

- **Single JSON array instead of JSONL.** Use `FORMAT JSON`. This wraps the rows in a `data` array and adds a `meta` block with the column types:


```bash
clickhouse local -q "SELECT event_id, event_type, amount FROM file('events.avro') LIMIT 3 FORMAT JSON"
```

```response
{
	"meta":
	[
		{ "name": "event_id", "type": "Int64" },
		{ "name": "event_type", "type": "String" },
		{ "name": "amount", "type": "Float64" }
	],
	"data":
	[
		{ "event_id": 1, "event_type": "login", "amount": 0 },
		{ "event_id": 2, "event_type": "purchase", "amount": 0.01 },
		{ "event_id": 3, "event_type": "logout", "amount": 0.02 }
	],
	"rows": 3
}
```

- **Pick and rename columns.** Select only the fields you need; the JSON keys follow your `SELECT` aliases.
- **Filter and aggregate before writing.** Convert and reduce in one pass:


```bash
clickhouse local -q "
SELECT country, count() AS events, round(sum(amount), 2) AS total
FROM file('events.avro')
WHERE event_type = 'purchase'
GROUP BY country
ORDER BY total DESC
FORMAT JSONEachRow"
```

```response
{"country":"AU","events":2,"total":0.22}
{"country":"US","events":2,"total":0.14}
{"country":"FR","events":1,"total":0.09}
```

- **Gzip the output in place.** A `.gz`, `.zst`, `.lz4`, or `.xz` suffix on the output file is detected from the name and the JSON is compressed as it is written, no extra step:


```bash
clickhouse local -q "SELECT event_id, event_type, ts::DateTime AS ts FROM file('events.avro') INTO OUTFILE 'events.jsonl.gz' TRUNCATE FORMAT JSONEachRow"
```

Upload-required converter sites can do none of this. The wins here are concrete: nothing leaves your machine, the types come straight from the Avro schema, the command is scriptable in a pipeline, and files larger than RAM stream through instead of failing to upload.

## How fast is it?

On a 3,000,000-row `events_large.avro`, converting every row to JSON Lines (with the `ts::DateTime` cast) completes in:


```bash
clickhouse local -q "
SELECT event_id, event_type, country, amount, ts::DateTime AS ts
FROM file('events_large.avro')
INTO OUTFILE 'events_large.jsonl' TRUNCATE FORMAT JSONEachRow"
```

**~0.55 seconds**, best of three with a warm OS page cache, on an Apple M4 Pro laptop (14 cores, 24 GB RAM). That decodes the Avro, casts the timestamps, and serializes 3,000,000 JSON objects. The measurement may move slightly under concurrent load, but the order of magnitude holds.

## The same conversion in Python with chDB

If you live in Python, [chDB](/resources/engineering/what-is-chdb) is the same ClickHouse engine in-process. The SQL is identical, including the `::DateTime` cast; you just write the bytes to a file:


```python
import chdb

sql = """
SELECT event_id, event_type, country, amount, ts::DateTime AS ts
FROM file('events.avro')
FORMAT JSONEachRow
"""
jsonl = chdb.query(sql).bytes()
with open("events_chdb.jsonl", "wb") as f:
    f.write(jsonl)
```

```response
{"event_id":1,"event_type":"login","country":"GB","amount":0,"ts":"2026-01-01 00:00:00"}
{"event_id":2,"event_type":"purchase","country":"US","amount":0.01,"ts":"2026-01-01 00:00:01"}
{"event_id":3,"event_type":"logout","country":"DE","amount":0.02,"ts":"2026-01-01 00:00:02"}
```

Same engine, same result, no subprocess.

## Other Avro conversions

JSON is text and convenient, but it is larger and slower to re-read than a columnar format. If the destination is analytics rather than a JSON consumer, convert Avro to Parquet instead with [convert Avro to Parquet](/resources/engineering/convert-avro-to-parquet), or to a flat table with [convert Avro to CSV](/resources/engineering/convert-avro-to-csv). To query the Avro file in place without converting it at all, see [how to read an Avro file](/resources/engineering/read-avro-file) and [what is an Avro file](/resources/engineering/what-is-an-avro-file).

## Run it yourself

The complete, runnable example lives in the ClickHouse examples repo: `generate.sh` to create the sample Avro files (including the 3,000,000-row file used for the timing above), `run.sh` with every command on this page, and `expected_output.txt`.

[github.com/ClickHouse/examples/tree/main/local-analytics/convert-avro-to-json](https://github.com/ClickHouse/examples/tree/main/local-analytics/convert-avro-to-json)

The same SQL that converts one file on your laptop runs unchanged against a directory of files, a remote object store, or a ClickHouse server and [ClickHouse Cloud](https://clickhouse.com/cloud) when the data outgrows your machine.