JSON schema inference

## Working with semi-structured and dynamic data[​](#working-with-semi-structured-data "Direct link to Working with semi-structured and dynamic data")

Our previous example used JSON which was static with well known key names and types. This is often not the case - keys can be added or their types can change. This is common in use cases such as Observability data.

ClickHouse handles this through a dedicated [`JSON`](/docs/sql-reference/data-types/newjson) type.

If you know your JSON is highly dynamic with many unique keys and multiple types for the same keys, we recommend not using schema inference with `JSONEachRow` to try and infer a column for each key - even if the data is in newline-delimited JSON format.

Consider the following example from an extended version of the above [Python PyPI dataset](https://clickpy.clickhouse.com/) dataset. Here we have added an arbitrary `tags` column with random key value pairs.

```
{
  "date": "2022-09-22",
  "country_code": "IN",
  "project": "clickhouse-connect",
  "type": "bdist_wheel",
  "installer": "bandersnatch",
  "python_minor": "",
  "system": "",
  "version": "0.2.8",
  "tags": {
    "5gTux": "f3to*PMvaTYZsz!*rtzX1",
    "nD8CV": "value"
  }
}
```

A sample of this data is publicly available in newline-delimited JSON format. If we attempt schema inference on this file, you will find performance is poor with an extremely verbose response:

```
DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/pypi_with_tags/sample_rows.json.gz')

-- result omitted for brevity

9 rows in set. Elapsed: 127.066 sec.
```

The primary issue here is that the `JSONEachRow` format is used for inference. This attempts to infer **a column type per key in the JSON** - effectively trying to apply a static schema to the data without using the [`JSON`](/docs/sql-reference/data-types/newjson) type.

With thousands of unique columns this approach to inference is slow. As an alternative, you can use the `JSONAsObject` format.

`JSONAsObject` treats the entire input as a single JSON object and stores it in a single column of type [`JSON`](/docs/sql-reference/data-types/newjson), making it better suited for highly dynamic or nested JSON payloads.

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

┌─name─┬─type─┐
│ json │ JSON │
└──────┴──────┘

1 row in set. Elapsed: 0.005 sec.
```

This format is also essential in cases where columns have multiple types that can't be reconciled. For example, consider a `sample.json` file with the following newline-delimited JSON:

```
{"a":1}
{"a":"22"}
```

In this case, ClickHouse is able to coerce the type collision and resolve the column `a` as a `Nullable(String)`.

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

┌─name─┬─type─────────────┐
│ a    │ Nullable(String) │
└──────┴──────────────────┘

1 row in set. Elapsed: 0.081 sec.
```

Type coercion

This type coercion can be controlled through a number of settings. The above example is dependent on the setting [`input_format_json_read_numbers_as_strings`](/docs/operations/settings/formats#input_format_json_read_numbers_as_strings).

However, some types are incompatible. Consider the following example:

```
{"a":1}
{"a":{"b":2}}
```

In this case any form of type conversion here isn't possible. A `DESCRIBE` command thus fails:

```
DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/json/conflict_sample.json')

Elapsed: 0.755 sec.

Received exception from server (version 24.12.1):
Code: 636. DB::Exception: Received from sql-clickhouse.clickhouse.com:9440. DB::Exception: The table structure cannot be extracted from a JSON format file. Error:
Code: 53. DB::Exception: Automatically defined type Tuple(b Int64) for column 'a' in row 1 differs from type defined by previous rows: Int64. You can specify the type for this column using setting schema_inference_hints.
```

In this case, `JSONAsObject` considers each row as a single [`JSON`](/docs/sql-reference/data-types/newjson) type (which supports the same column having multiple types). This is essential:

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

┌─name─┬─type─┐
│ json │ JSON │
└──────┴──────┘

1 row in set. Elapsed: 0.010 sec.
```

## Further reading[​](#further-reading "Direct link to Further reading")

To learn more about the data type inference, you can refer to [this](/docs/interfaces/schema-inference) documentation page.

- [When to use type inference](#when-to-use-type-inference)- [Detecting types](#detecting-types)- [Querying JSON](#querying-json)- [Creating tables](#creating-tables)
        - [Creating tables from snippets](#creating-tables-from-snippets)- [Loading JSON data](#loading-json-data)- [Handling errors](#handling-errors)- [Working with semi-structured and dynamic data](#working-with-semi-structured-data)- [Further reading](#further-reading)

Was this page helpful?