Handling other JSON formats

Earlier examples of loading JSON data assume the use of JSONEachRow (NDJSON). This format reads the keys in each JSON line as columns. For example:

While this is generally the most commonly used format for JSON, users will encounter other formats or need to read the JSON as a single object.

We provide examples of reading and loading JSON in other common formats below.

Reading JSON as an object

Our previous examples show how JSONEachRow reads newline-delimited JSON, with each line read as a separate object mapped to a table row and each key to a column. This is ideal for cases where the JSON is predictable with single types for each column.

In contrast, JSONAsObject treats each line as a single JSON object and stores it in a single column, of type JSON, making it better suited for nested JSON payloads and cases where the keys are dynamic and have potentially more than one type.

Use JSONEachRow for row-wise inserts, and JSONAsObject when storing flexible or dynamic JSON data.

Contrast the above example, with the following query which reads the same data as a JSON object per line:

JSONAsObject is useful for inserting rows into a table using a single JSON object column e.g.

The JSONAsObject format may also be useful for reading newline-delimited JSON in cases where the structure of the objects is inconsistent. For example, if a key varies in type across rows (it may sometimes be a string, but other times an object). In such cases, ClickHouse cannot infer a stable schema using JSONEachRow, and JSONAsObject allows the data to be ingested without strict type enforcement, storing each JSON row as a whole in a single column. For example, notice how JSONEachRow fails on the following example:

Conversely, JSONAsObject can be used in this case as the JSON type supports multiple types for the same subcolumn.

Array of JSON objects

One of the most popular forms of JSON data is having a list of JSON objects in a JSON array, like in this example:

Let's create a table for this kind of data:

To import a list of JSON objects, we can use a JSONEachRow format (inserting data from list.json file):

We have used a FROM INFILE clause to load data from the local file, and we can see the import was successful:

JSON object keys

In some cases, the list of JSON objects can be encoded as object properties instead of array elements (see objects.json for example):

ClickHouse can load data from this kind of data using the JSONObjectEachRow format:

Specifying parent object key values

Let's say we also want to save values in parent object keys to the table. In this case, we can use the following option to define the name of the column we want key values to be saved to:

Now, we can check which data is going to be loaded from the original JSON file using file() function:

Note how the id column has been populated by key values correctly.

JSON arrays

Sometimes, for the sake of saving space, JSON files are encoded in arrays instead of objects. In this case, we deal with a list of JSON arrays:

In this case, ClickHouse will load this data and attribute each value to the corresponding column based on its order in the array. We use JSONCompactEachRow format for this:

Importing individual columns from JSON arrays

In some cases, data can be encoded column-wise instead of row-wise. In this case, a parent JSON object contains columns with values. Take a look at the following file:

ClickHouse uses the JSONColumns format to parse data formatted like that:

A more compact format is also supported when dealing with an array of columns instead of an object using JSONCompactColumns format:

Saving JSON objects instead of parsing

There are cases you might want to save JSON objects to a single String (or JSON) column instead of parsing it. This can be useful when dealing with a list of JSON objects of different structures. Let's take this file for example, where we have multiple different JSON objects inside a parent list:

We want to save original JSON objects into the following table:

Now we can load data from the file into this table using JSONAsString format to keep JSON objects instead of parsing them:

And we can use JSON functions to query saved objects:

Note that JSONAsString works perfectly fine in cases we have JSON object-per-line formatted files (usually used with JSONEachRow format).

Schema for nested objects

In cases when we're dealing with nested JSON objects, we can additionally define an explicit schema and use complex types (Array, Object Data Type or Tuple) to load data:

Accessing nested JSON objects

We can refer to nested JSON keys by enabling the following settings option:

This allows us to refer to nested JSON object keys using dot notation (remember to wrap those with backtick symbols to work):

This way we can flatten nested JSON objects or use some nested values to save them as separate columns.

Skipping unknown columns

By default, ClickHouse will ignore unknown columns when importing JSON data. Let's try to import the original file into the table without the month column:

We can still insert the original JSON data with 3 columns into this table:

ClickHouse will ignore unknown columns while importing. This can be disabled with the input_format_skip_unknown_fields settings option:

ClickHouse will throw exceptions in cases of inconsistent JSON and table columns structure.

BSON

ClickHouse allows exporting to and importing data from BSON encoded files. This format is used by some DBMSs, e.g. MongoDB database.

To import BSON data, we use the BSONEachRow format. Let's import data from this BSON file:

We can also export to BSON files using the same format:

After that, we'll have our data exported to the out.bson file.