Skip to main content
Skip to main content
Edit this page

JSON Data Type

Beta feature. Learn more.

Stores JavaScript Object Notation (JSON) documents in a single column.

Note

This feature is beta and is not production-ready. If you need to work with JSON documents, consider using this guide instead. If you want to use JSON type, set enable_json_type = 1.

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

Where:

  • max_dynamic_paths is an optional parameter indicating how many paths can be stored separately as subcolumns 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. Default value of max_dynamic_paths is 1024.
  • max_dynamic_types is an 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. Default value of max_dynamic_types is 32.
  • some.path TypeName is an optional type hint for particular path in the JSON. Such paths will be always stored as subcolumns with specified type.
  • SKIP path.to.skip is an 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' is 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​

Using JSON type in table column definition:

Using CAST from String:

Using CAST from Tuple:

Using CAST from Map:

Using CAST from deprecated Object('json'):

Note

CAST from Tuple/Map/Object('json') to JSON is implemented via serializing the column into String column containing JSON objects and deserializing it back to JSON type column.

Reading JSON paths as subcolumns​

JSON type supports reading every path as a separate subcolumn. If type of the requested path was not specified in the JSON type declaration, the subcolumn 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 returned subcolumns:

As we can see, for a.b the type is UInt32 as we specified in the JSON type declaration, and for all other subcolumns the type is Dynamic.

It is also possible to read subcolumns of a Dynamic type using special syntax json.some.path.:TypeName:

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

Reading JSON sub-objects as subcolumns​

JSON type supports reading nested objects as subcolumns with type JSON using special syntax json.^some.path:

Note

Reading sub-objects as subcolumns may be inefficient, as this may require almost full scan of the JSON data.

Types inference for paths​

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

Let's see some examples:

Handling arrays of JSON objects​

JSON paths that contains an array of objects are parsed as type Array(JSON) and inserted into Dynamic column for this path. To read an array of objects you can extract it from Dynamic column as a subcolumn:

As you can notice, the max_dynamic_types/max_dynamic_paths parameters of the nested JSON type were reduced compared to the default values. It's needed to avoid number of subcolumns to grow uncontrolled on nested arrays of JSON objects.

Let's try to read subcolumns from this nested JSON column:

We can avoid writing Array(JSON) subcolumn name using special syntax:

The number of [] after path indicates the array level. 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 subcolumns from Array(JSON) column:

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

Reading JSON type from the data​

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

Examples:

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

Reaching the limit of dynamic paths inside JSON​

JSON data type can store only limited number of paths as separate subcolumns inside. By default, this limit is 1024, but you can change it in type declaration using parameter max_dynamic_paths. When the limit is reached, all new paths inserted to JSON column will be stored in a single shared data structure. It's still possible to read such paths as subcolumns, but it will require reading the whole shared data structure to extract the values of this path. This limit is needed to avoid the enormous number of different subcolumns that can make the table unusable.

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

Reaching the limit during data parsing​

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

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

During merges of data parts in MergeTree table engines​

During merge of several data parts in 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 subcolumns. In this case ClickHouse chooses what paths will remain as subcolumns 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, but it depends on the implementation.

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

Each insert will create a separate data part with JSON column containing 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 e and d to shared data structure.

Introspection functions​

There are several functions that can help to inspect the content of the JSON column: JSONAllPaths, JSONAllPathsWithTypes, JSONDynamicPaths, JSONDynamicPathsWithTypes, JSONSharedDataPaths, JSONSharedDataPathsWithTypes, distinctDynamicTypes, distinctJSONPaths and distinctJSONPathsAndTypes

Examples

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

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 String type is supported.

Example

Comparison between values of the JSON type​

Values of the JSON column cannot be compared by less/greater functions, but can be compared using equal function. Two JSON objects considered equal when they have the same set of paths and value of each path have the same type and value in both objects.

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 the storage and the 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 if needed. It will improve the storage.
  • Don't set max_dynamic_paths parameter to very high values, it can make the storage and reading less efficient.