Skip to main content
Skip to main content
Edit this page

JSON Data Type

Beta feature. Learn more.

The JSON type stores JavaScript Object Notation (JSON) documents in a single column.

Note

This feature is in beta and is not yet production-ready. If you need to work with JSON documents, consider using this guide instead.

If you want to use the JSON type, and for the examples on this page, please use:

To declare a column of JSON type, you can use the following syntax:

Where the parameters in the syntax above are defined as:

ParameterDescriptionDefault Value
max_dynamic_pathsAn optional parameter indicating how many paths can be stored separately as sub-columns across single block of data that is stored separately (for example across single data part for MergeTree table).

If this limit is exceeded, all other paths will be stored together in a single structure.		1024
max_dynamic_typesAn optional parameter between 1 and 255 indicating how many different data types can be stored inside a single path column with type Dynamic across single block of data that is stored separately (for example across single data part for MergeTree table).

If this limit is exceeded, all new types will be converted to type String.		32
some.path TypeNameAn optional type hint for particular path in the JSON. Such paths will be always stored as sub-columns with specified type.
SKIP path.to.skipAn optional hint for particular path that should be skipped during JSON parsing. Such paths will never be stored in the JSON column. If specified path is a nested JSON object, the whole nested object will be skipped.
SKIP REGEXP 'path_regexp'An optional hint with a regular expression that is used to skip paths during JSON parsing. All paths that match this regular expression will never be stored in the JSON column.

Creating JSON​

In this section we'll take a look at the various ways that you can create JSON.

Using JSON in a table column definition​

Using CAST with ::JSON​

It is possible to cast various types using the special syntax ::JSON.

CAST from String to JSON​

CAST from Tuple to JSON​

CAST from Map to JSON​

CAST from deprecated Object('json') to JSON​

Note

JSON paths are stored flattened. This means that when a JSON object is formatted from a path like a.b.c it is not possible to know whether the object should be constructed as { "a.b.c" : ... } or { "a" " {"b" : {"c" : ... }}}. Our implementation will always assume the latter.

For example:

will return:

and not:

Reading JSON paths as sub-columns​

The JSON type supports reading every path as a separate sub-column. If the type of the requested path is not specified in the JSON type declaration, then the sub column of the path will always have type Dynamic.

For example:

If the requested path wasn't found in the data, it will be filled with NULL values:

Let's check the data types of the returned sub-columns:

As we can see, for a.b, the type is UInt32 as we specified it to be in the JSON type declaration, and for all other sub-columns the type is Dynamic.

It is also possible to read sub-columns of a Dynamic type using the special syntax json.some.path.:TypeName:

Dynamic sub-columns can be cast to any data type. In this case an exception will be thrown if the internal type inside Dynamic cannot be cast to the requested type:

Reading JSON sub-objects as sub-columns​

The JSON type supports reading nested objects as sub-columns with type JSON using the special syntax json.^some.path:

Note

Reading sub-objects as sub-columns may be inefficient, as this may require a near full scan of the JSON data.

Type inference for paths​

During parsing of JSON, ClickHouse tries to detect the most appropriate data type for each JSON path. It works similarly to automatic schema inference from input data, and is controlled by the same settings:

Let's take a look at some examples:

Handling arrays of JSON objects​

JSON paths that contain an array of objects are parsed as type Array(JSON) and inserted into a Dynamic column for the path. To read an array of objects, you can extract it from the Dynamic column as a sub-column:

As you may have noticed, the max_dynamic_types/max_dynamic_paths parameters of the nested JSON type got reduced compared to the default values. This is needed to avoid the number of sub-columns growing uncontrollably on nested arrays of JSON objects.

Let's try to read sub-columns from a nested JSON column:

We can avoid writing Array(JSON) sub-column names using a special syntax:

The number of [] after the path indicates the array level. For example, json.path[][] will be transformed to json.path.:Array(Array(JSON))

Let's check the paths and types inside our Array(JSON):

Let's read sub-columns from an Array(JSON) column:

We can also read sub-object sub-columns from a nested JSON column:

Reading JSON type from data​

All text formats (JSONEachRow, TSV, CSV, CustomSeparated, Values, etc.) support reading the JSON type.

Examples:

For text formats like CSV/TSV/etc, JSON is parsed from a string containing the JSON object:

Reaching the limit of dynamic paths inside JSON​

The JSON data type can store only a limited number of paths as separate sub-columns internally. By default, this limit is 1024, but you can change it in the type declaration using parameter max_dynamic_paths.

When the limit is reached, all new paths inserted to a JSON column will be stored in a single shared data structure. It's still possible to read such paths as sub-columns, but it will require reading the entire shared data structure to extract the values of this path. This limit is needed to avoid having an enormous number of different sub-columns that can make the table unusable.

Let's see what happens when the limit is reached in a few different scenarios.

Reaching the limit during data parsing​

During parsing of JSON objects from data, when the limit is reached for the current block of data, all new paths will be stored in a shared data structure. We can use the following two introspection functions JSONDynamicPaths, JSONSharedDataPaths:

As we can see, after inserting paths e and f.g the limit was reached, and they got inserted into a shared data structure.

During merges of data parts in MergeTree table engines​

During a merge of several data parts in a MergeTree table the JSON column in the resulting data part can reach the limit of dynamic paths and won't be able to store all paths from source parts as sub-columns. In this case, ClickHouse chooses what paths will remain as sub-columns after merge and what paths will be stored in the shared data structure. In most cases, ClickHouse tries to keep paths that contain the largest number of non-null values and move the rarest paths to the shared data structure. This does, however, depend on the implementation.

Let's see an example of such a merge. First, let's create a table with a JSON column, set the limit of dynamic paths to 3 and then insert values with 5 different paths:

Each insert will create a separate data part with the JSON column containing a single path:

Now, let's merge all parts into one and see what will happen:

As we can see, ClickHouse kept the most frequent paths a, b and c and moved paths d and e to a shared data structure.

Introspection functions​

There are several functions that can help to inspect the content of the JSON column:

Examples

Let's investigate the content of the GH Archive dataset for the date 2020-01-01:

ALTER MODIFY COLUMN to JSON type​

It's possible to alter an existing table and change the type of the column to the new JSON type. Right now only ALTER from a String type is supported.

Example

Comparison between values of the JSON type​

Values of a JSON column cannot be compared with the less/greater functions, but can be compared using the equal function.

Two JSON objects are considered equal when they have the same set of paths and each of these paths has the same type and value in both objects.

For example:

Tips for better usage of the JSON type​

Before creating JSON column and loading data into it, consider the following tips:

  • Investigate your data and specify as many path hints with types as you can. It will make storage and reading much more efficient.
  • Think about what paths you will need and what paths you will never need. Specify paths that you won't need in the SKIP section, and SKIP REGEXP section if needed. This will improve the storage.
  • Don't set the max_dynamic_paths parameter to very high values, as it can make storage and reading less efficient. While highly dependent on system parameters such as memory, CPU, etc., a general rule of thumb would be to not set max_dynamic_paths > 10 000.

Further Reading​