Exporting JSON

Almost any JSON format used for import can be used for export as well. The most popular is JSONEachRow:

Or we can use JSONCompactEachRow to save disk space by skipping column names:

Overriding data types as strings

ClickHouse respects data types and will export JSON accordingly to standards. But in cases where we need to have all values encoded as strings, we can use the JSONStringsEachRow format:

Now, the hits numeric column is encoded as a string. Exporting as strings is supported for all JSON* formats, just explore JSONStrings\* and JSONCompactStrings\* formats:

Exporting metadata together with data

General JSON format, which is popular in apps, will export not only resulting data but column types and query stats:

The JSONCompact format will print the same metadata but use a compacted form for the data itself:

Consider JSONStrings or JSONCompactStrings variants to encode all values as strings.

Compact way to export JSON data and structure

A more efficient way to have data, as well as it's structure, is to use JSONCompactEachRowWithNamesAndTypes format:

This will use a compact JSON format prepended by two header rows with column names and types. This format can then be used to ingest data into another ClickHouse instance (or other apps).

Exporting JSON to a file

To save exported JSON data to a file, we can use an INTO OUTFILE clause:

It took ClickHouse only 2 seconds to export almost 37 million records to a JSON file. We can also export using a COMPRESSION clause to enable compression on the fly:

It takes more time to accomplish, but generates a much smaller compressed file: