JSON Data Type

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/data-types/newjson.md)

[### Looking for a guide?

Check out our JSON best practice guide for examples, advanced features and considerations for using the JSON type.](/docs/best-practices/use-json-where-appropriate)[Read more](/docs/best-practices/use-json-where-appropriate)

  

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

Note

In ClickHouse Open-Source JSON data type is marked as production ready in version 25.3. It's not recommended to use this type in production in previous versions.

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

```
<column_name> JSON
(
    max_dynamic_paths=N,
    max_dynamic_types=M,
    some.path TypeName,
    SKIP path.to.skip,
    SKIP REGEXP 'paths_regexp'
)
```

Where the parameters in the syntax above are defined as:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Description Default Value|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `max_dynamic_paths` An 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 called [shared data](#shared-data-structure).  There are also [ways](#controlling-the-number-of-dynamic-paths) how to change the limit on dynamic paths without changing this parameter. `1024`| `max_dynamic_types` An optional parameter between `1` and `255` indicating how many different data types can be stored separately 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 stored together in a single structure called `shared variant`. `32`| `some.path TypeName` An optional type hint for particular path in the JSON. Such paths will be always stored as sub-columns with specified type. |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `SKIP path.to.skip` 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'` 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.  | | | | | | | | | | | | | | | | | |

## When to use the `JSON` Type[​](#when-to-use-json-type "Direct link to when-to-use-json-type")

The `JSON` type is designed for querying, filtering, and aggregating specific fields within JSON objects that have dynamic or unpredictable structures. It achieves this by splitting JSON objects into separate sub-columns, which dramatically reduces data read and speeds up queries on selected fields compared to alternatives like `Map` or parsing strings.

**However, this comes with important trade-offs:**

- Slower `INSERT`s - Splitting JSON into sub-columns, performing type inference, and managing flexible storage structures makes inserts slower compared to storing JSON as a simple `String` column.
- Slower when reading entire objects - If you need to retrieve complete JSON documents (rather than specific fields), the `JSON` type is slower than reading from a `String` column. The overhead of reconstructing objects from separate sub-columns provides no benefit when you're not doing field-level queries.
- Storage overhead - Maintaining separate sub-columns adds structural overhead compared to storing JSON as a single string value.

### Use the `JSON` type when:[​](#use-json-type "Direct link to use-json-type")

- Your data has a dynamic or unpredictable structure with varying keys across documents
- Field types or schemas change over time or vary between records
- You need to query, filter, or aggregate on specific paths within JSON objects whose structure you can't predict upfront
- Your use case involves semi-structured data like logs, events, or user-generated content with inconsistent schemas

### Use a `String` column (or structured types) when:[​](#use-string-type "Direct link to use-string-type")

- Your data structure is known and consistent - in this case, use normal columns, `Tuple`, `Array`, `Dynamic`, or `Variant` types instead
- `JSON` documents are treated as opaque blobs that are only stored and retrieved in their entirety without field-level analysis
- You don't need to query or filter on individual JSON fields within the database
- The `JSON` is simply a transport/storage format, not analyzed within ClickHouse

Tip

If `JSON` is an opaque document that isn't analyzed inside the database, and only stored and retrieved back, it should be stored as a `String` field. The `JSON` type's benefits only materialize when you need to efficiently query, filter, or aggregate on specific fields within dynamic `JSON` structures.

You can also mix approaches—use standard columns for predictable top-level fields and a `JSON` column for dynamic sections of the payload.

## Creating `JSON`[​](#creating-json "Direct link to 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-json-in-a-table-column-definition "Direct link to using-json-in-a-table-column-definition")

```
CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : 42}, "c" : [1, 2, 3]}'), ('{"f" : "Hello, World!"}'), ('{"a" : {"b" : 43, "e" : 10}, "c" : [4, 5, 6]}');
SELECT json FROM test;
```

```
┌─json────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"]}          │
│ {"f":"Hello, World!"}                       │
│ {"a":{"b":"43","e":"10"},"c":["4","5","6"]} │
└─────────────────────────────────────────────┘
```

```
CREATE TABLE test (json JSON(a.b UInt32, SKIP a.e)) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : 42}, "c" : [1, 2, 3]}'), ('{"f" : "Hello, World!"}'), ('{"a" : {"b" : 43, "e" : 10}, "c" : [4, 5, 6]}');
SELECT json FROM test;
```

```
┌─json──────────────────────────────┐
│ {"a":{"b":42},"c":["1","2","3"]}  │
│ {"a":{"b":0},"f":"Hello, World!"} │
│ {"a":{"b":43},"c":["4","5","6"]}  │
└───────────────────────────────────┘
```

### Using CAST with `::JSON`[​](#using-cast-with-json "Direct link to using-cast-with-json")

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

#### CAST from `String` to `JSON`[​](#cast-from-string-to-json "Direct link to cast-from-string-to-json")

```
SELECT '{"a" : {"b" : 42},"c" : [1, 2, 3], "d" : "Hello, World!"}'::JSON AS json;
```

```
┌─json───────────────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"],"d":"Hello, World!"} │
└────────────────────────────────────────────────────────┘
```

#### CAST from `Tuple` to `JSON`[​](#cast-from-tuple-to-json "Direct link to cast-from-tuple-to-json")

```
SET enable_named_columns_in_function_tuple = 1;
SELECT (tuple(42 AS b) AS a, [1, 2, 3] AS c, 'Hello, World!' AS d)::JSON AS json;
```

```
┌─json───────────────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"],"d":"Hello, World!"} │
└────────────────────────────────────────────────────────┘
```

#### CAST from `Map` to `JSON`[​](#cast-from-map-to-json "Direct link to cast-from-map-to-json")

```
SET use_variant_as_common_type=1;
SELECT map('a', map('b', 42), 'c', [1,2,3], 'd', 'Hello, World!')::JSON AS json;
```

```
┌─json───────────────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"],"d":"Hello, World!"} │
└────────────────────────────────────────────────────────┘
```

Note