JSON Data Type

Note: because of storing some additional information inside the data structure, the disk storage size is higher with this serialization compared to
`map` and `map_with_buckets` serializations.

For more detailed overview of the new shared data serializations and implementation details read the [blog post](https://clickhouse.com/blog/json-data-type-gets-even-better).

## Controlling the number of dynamic paths inside JSON in MergeTree parts[​](#controlling-the-number-of-dynamic-paths "Direct link to Controlling the number of dynamic paths inside JSON in MergeTree parts")

The main way to set a limit on dynamic paths in JSON is to use `max_dynamic_paths` parameter inside the JSON type declaration.
But changing `max_dynamic_paths` for existing columns requires running `ALTER TABLE <table> MODIFY COLUMN <column> JSON(max_dynamic_paths=K)` that will start a background mutation that will rewrite all existing parts.
Such mutation can be really heavy and can affect the server performance until the mutation is finished. To avoid this, you can use these 3 settings that can help you to change the limit on dynamic paths in MergeTree tables for new data parts:

- `merge_max_dynamic_subcolumns_in_wide_part` - a MergeTree setting that limits the number of dynamic subcolumns for each JSON column during merge into a Wide data part.
- `merge_max_dynamic_subcolumns_in_compact_part` - a MergeTree setting that limits the number of dynamic subcolumns for each JSON column during merge into a Compact data part.
- `max_dynamic_subcolumns_in_json_type_parsing` - a session setting that limits the number of dynamic subcolumns for each JSON column during parsing of JSON data into a JSON column.

Note: limit on dynamic paths cannot exceed the value specified in `max_dynamic_paths` parameter, even if values of described settings are higher.

## Introspection functions[​](#introspection-functions "Direct link to Introspection functions")

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

- [`JSONAllPaths`](/docs/sql-reference/functions/json-functions#JSONAllPaths)
- [`JSONAllPathsWithTypes`](/docs/sql-reference/functions/json-functions#JSONAllPathsWithTypes)
- [`JSONAllValues`](/docs/sql-reference/functions/json-functions#JSONAllValues)
- [`JSONDynamicPaths`](/docs/sql-reference/functions/json-functions#JSONDynamicPaths)
- [`JSONDynamicPathsWithTypes`](/docs/sql-reference/functions/json-functions#JSONDynamicPathsWithTypes)
- [`JSONSharedDataPaths`](/docs/sql-reference/functions/json-functions#JSONSharedDataPaths)
- [`JSONSharedDataPathsWithTypes`](/docs/sql-reference/functions/json-functions#JSONSharedDataPathsWithTypes)
- [`distinctDynamicTypes`](/docs/sql-reference/aggregate-functions/reference/distinctdynamictypes)
- [`distinctJSONPaths and distinctJSONPathsAndTypes`](/docs/sql-reference/aggregate-functions/reference/distinctjsonpaths)

**Examples**

Let's investigate the content of the [GH Archive](https://www.gharchive.org/) dataset for the date `2020-01-01`:

```
SELECT arrayJoin(distinctJSONPaths(json))
FROM s3('s3://clickhouse-public-datasets/gharchive/original/2020-01-01-*.json.gz', JSONAsObject)
```

```
┌─arrayJoin(distinctJSONPaths(json))─────────────────────────┐
│ actor.avatar_url                                           │
│ actor.display_login                                        │
│ actor.gravatar_id                                          │
│ actor.id                                                   │
│ actor.login                                                │
│ actor.url                                                  │
│ created_at                                                 │
│ id                                                         │
│ org.avatar_url                                             │
│ org.gravatar_id                                            │
│ org.id                                                     │
│ org.login                                                  │
│ org.url                                                    │
│ payload.action                                             │
│ payload.before                                             │
│ payload.comment._links.html.href                           │
│ payload.comment._links.pull_request.href                   │
│ payload.comment._links.self.href                           │
│ payload.comment.author_association                         │
│ payload.comment.body                                       │
│ payload.comment.commit_id                                  │
│ payload.comment.created_at                                 │
│ payload.comment.diff_hunk                                  │
│ payload.comment.html_url                                   │
│ payload.comment.id                                         │
│ payload.comment.in_reply_to_id                             │
│ payload.comment.issue_url                                  │
│ payload.comment.line                                       │
│ payload.comment.node_id                                    │
│ payload.comment.original_commit_id                         │
│ payload.comment.original_position                          │
│ payload.comment.path                                       │
│ payload.comment.position                                   │
│ payload.comment.pull_request_review_id                     │
...
│ payload.release.node_id                                    │
│ payload.release.prerelease                                 │
│ payload.release.published_at                               │
│ payload.release.tag_name                                   │
│ payload.release.tarball_url                                │
│ payload.release.target_commitish                           │
│ payload.release.upload_url                                 │
│ payload.release.url                                        │
│ payload.release.zipball_url                                │
│ payload.size                                               │
│ public                                                     │
│ repo.id                                                    │
│ repo.name                                                  │
│ repo.url                                                   │
│ type                                                       │
└─arrayJoin(distinctJSONPaths(json))─────────────────────────┘
```

```
SELECT arrayJoin(distinctJSONPathsAndTypes(json))
FROM s3('s3://clickhouse-public-datasets/gharchive/original/2020-01-01-*.json.gz', JSONAsObject)
SETTINGS date_time_input_format = 'best_effort'
```