JSON schema inference

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/data-formats/json/inference.md)

ClickHouse can automatically determine the structure of JSON data. This can be used to query JSON data directly e.g. on disk with `clickhouse-local` or S3 buckets, and/or automatically create schemas prior to loading the data into ClickHouse.

## When to use type inference[​](#when-to-use-type-inference "Direct link to When to use type inference")

- **Consistent structure** - The data from which you're going to infer types contains all the keys that you're interested in. Type inference is based on sampling the data up to a [maximum number of rows](/docs/operations/settings/formats#input_format_max_rows_to_read_for_schema_inference) or [bytes](/docs/operations/settings/formats#input_format_max_bytes_to_read_for_schema_inference). Data after the sample, with additional columns, will be ignored and can't be queried.
- **Consistent types** - Data types for specific keys need to be compatible i.e. it must be possible to coerce one type to the other automatically.

If you have more dynamic JSON, to which new keys are added and multiple types are possible for the same path, see ["Working with semi-structured and dynamic data"](/docs/integrations/data-formats/json/inference#working-with-semi-structured-data).

## Detecting types[​](#detecting-types "Direct link to Detecting types")

The following assumes the JSON is consistently structured and has a single type for each path.

Our previous examples used a simple version of the [Python PyPI dataset](https://clickpy.clickhouse.com/) in `NDJSON` format. In this section, we explore a more complex dataset with nested structures - the [arXiv dataset](https://www.kaggle.com/datasets/Cornell-University/arxiv?resource=download) containing 2.5m scholarly papers. Each row in this dataset, distributed as `NDJSON`, represents a published academic paper. An example row is shown below:

```
{
  "id": "2101.11408",
  "submitter": "Daniel Lemire",
  "authors": "Daniel Lemire",
  "title": "Number Parsing at a Gigabyte per Second",
  "comments": "Software at https://github.com/fastfloat/fast_float and\n https://github.com/lemire/simple_fastfloat_benchmark/",
  "journal-ref": "Software: Practice and Experience 51 (8), 2021",
  "doi": "10.1002/spe.2984",
  "report-no": null,
  "categories": "cs.DS cs.MS",
  "license": "http://creativecommons.org/licenses/by/4.0/",
  "abstract": "With disks and networks providing gigabytes per second ....\n",
  "versions": [
    {
      "created": "Mon, 11 Jan 2021 20:31:27 GMT",
      "version": "v1"
    },
    {
      "created": "Sat, 30 Jan 2021 23:57:29 GMT",
      "version": "v2"
    }
  ],
  "update_date": "2022-11-07",
  "authors_parsed": [
    [
      "Lemire",
      "Daniel",
      ""
    ]
  ]
}
```

This data requires a far more complex schema than previous examples. We outline the process of defining this schema below, introducing complex types such as `Tuple` and `Array`.

This dataset is stored in a public S3 bucket at `s3://datasets-documentation/arxiv/arxiv.json.gz`.

You can see that the dataset above contains nested JSON objects. While you should draft and version your schemas, inference allows types to be inferred from the data. This allows the schema DDL to be auto-generated, avoiding the need to build it manually and accelerating the development process.

Auto format detection

As well as detecting the schema, JSON schema inference will automatically infer the format of the data from the file extension and contents. The above file is detected as being NDJSON automatically as a result.

Using the [s3 function](/docs/sql-reference/table-functions/s3) with the `DESCRIBE` command shows the types that will be inferred.

```
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
SETTINGS describe_compact_output = 1
```

```
┌─name───────────┬─type────────────────────────────────────────────────────────────────────┐
│ id             │ Nullable(String)                                                        │
│ submitter      │ Nullable(String)                                                        │
│ authors        │ Nullable(String)                                                        │
│ title          │ Nullable(String)                                                        │
│ comments       │ Nullable(String)                                                        │
│ journal-ref    │ Nullable(String)                                                        │
│ doi            │ Nullable(String)                                                        │
│ report-no      │ Nullable(String)                                                        │
│ categories     │ Nullable(String)                                                        │
│ license        │ Nullable(String)                                                        │
│ abstract       │ Nullable(String)                                                        │
│ versions       │ Array(Tuple(created Nullable(String),version Nullable(String)))         │
│ update_date    │ Nullable(Date)                                                          │
│ authors_parsed │ Array(Array(Nullable(String)))                                          │
└────────────────┴─────────────────────────────────────────────────────────────────────────┘
```

Avoid nulls

You can see a lot of the columns are detected as Nullable. We [don't recommend using the Nullable](/docs/sql-reference/data-types/nullable#storage-features) type when not absolutely needed. You can use [schema\_inference\_make\_columns\_nullable](/docs/operations/settings/formats#schema_inference_make_columns_nullable) to control the behavior of when Nullable is applied.

We can see that most columns have automatically been detected as `String`, with `update_date` column correctly detected as a `Date`. The `versions` column has been created as an `Array(Tuple(created String, version String))` to store a list of objects, with `authors_parsed` being defined as `Array(Array(String))` for nested arrays.

Controlling type detection

The auto-detection of dates and datetimes can be controlled through the settings [`input_format_try_infer_dates`](/docs/operations/settings/formats#input_format_try_infer_dates) and [`input_format_try_infer_datetimes`](/docs/operations/settings/formats#input_format_try_infer_datetimes) respectively (both enabled by default). The inference of objects as tuples is controlled by the setting [`input_format_json_try_infer_named_tuples_from_objects`](/docs/operations/settings/formats#input_format_json_try_infer_named_tuples_from_objects). Other settings which control schema inference for JSON, such as the auto-detection of numbers, can be found [here](/docs/interfaces/schema-inference#text-formats).

## Querying JSON[​](#querying-json "Direct link to Querying JSON")

The following assumes the JSON is consistently structured and has a single type for each path.

We can rely on schema inference to query JSON data in place. Below, we find the top authors for each year, exploiting the fact the dates and arrays are automatically detected.

```
SELECT
 toYear(update_date) AS year,
 authors,
    count() AS c
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
GROUP BY
    year,
 authors
ORDER BY
    year ASC,
 c DESC
LIMIT 1 BY year