Skip to main content
Skip to main content
Edit this page

Other approaches to modeling JSON

The following are alternatives to modeling JSON in ClickHouse. These are documented for completeness and are generally not recommended or applicable in most use cases.

Using Nested​

The Nested type can be used to model static objects which are rarely subject to change, offering an alternative to Tuple and Array(Tuple). We generally recommend avoiding using this type for JSON as its behavior is often confusing. The primary benefit of Nested is that sub-columns can be used in ordering keys.

Below, we provide an example of using the Nested type to model a static object. Consider the following simple log entry in JSON:

We can declare the request key as Nested. Similar to Tuple, we are required to specify the sub columns.

flatten_nested​

The setting flatten_nested controls the behavior of nested.

flatten_nested=1​

A value of 1 (the default) does not support an arbitrary level of nesting. With this value, it is easiest to think of a nested data structure as multiple Array columns of the same length. The fields method, path, and version are all separate Array(Type) columns in effect with one critical constraint: the length of the method, path, and version fields must be the same. If we use SHOW CREATE TABLE, this is illustrated:

Below, we insert into this table:

A few important points to note here:

  • We need to use the setting input_format_import_nested_json to insert the JSON as a nested structure. Without this, we are required to flatten the JSON i.e.

  • The nested fields method, path, and version need to be passed as JSON arrays i.e.

Columns can be queried using a dot notation:

Note the use of Array for the sub-columns means the full breath Array functions can potentially be exploited, including the ARRAY JOIN clause - useful if your columns have multiple values.

flatten_nested=0​

This allows an arbitrary level of nesting and means nested columns stay as a single array of Tuples - effectively they become the same as Array(Tuple).

This represents the preferred way, and often the simplest way, to use JSON with Nested. As we show below, it only requires all objects to be a list.

Below, we re-create our table and re-insert a row:

A few important points to note here:

  • input_format_import_nested_json is not required to insert.

  • The Nested type is preserved in SHOW CREATE TABLE. Underneath this column is effectively a Array(Tuple(Nested(method LowCardinality(String), path String, version LowCardinality(String))))

  • As a result, we are required to insert request as an array i.e.

Columns can again be queried using a dot notation:

Example​

A larger example of the above data is available in a public bucket in s3 at: s3://datasets-documentation/http/.

Given the constraints and input format for the JSON, we insert this sample dataset using the following query. Here, we set flatten_nested=0.

The following statement inserts 10 million rows, so this may take a few minutes to execute. Apply a LIMIT if required:

Querying this data requires us to access the request fields as arrays. Below, we summarize the errors and http methods over a fixed time period.

Using Pairwise Arrays​

Pairwise arrays provide a balance between the flexibility of representing JSON as Strings and the performance of a more structured approach. The schema is flexible in that any new fields can be potentially added to the root. This, however, requires a significantly more complex query syntax and isn't compatible with nested structures.

As an example, consider the following table:

To insert into this table, we need to structure the JSON as a list of keys and values. The following query illustrates the use of the JSONExtractKeysAndValues to achieve this:

Note how the request column remains a nested structure represented as a string. We can insert any new keys to the root. We can also have arbitrary differences in the JSON itself. To insert into our local table, execute the following:

Querying this structure requires using the indexOf function to identify the index of the required key (which should be consistent with the order of the values). This can be used to access the values array column i.e. values[indexOf(keys, 'status')]. We still require a JSON parsing method for the request column - in this case, simpleJSONExtractString.