title_wip

### JSONAsObject[​](#json-as-object "Direct link to JSONAsObject")

In this format, ClickHouse reads the whole JSON object from the data into a single column with `JSON` data type. Inferred type for this format is always `JSON` and the column name is `json`.

**Example**

```
DESC format(JSONAsObject, '{"x" : 42, "y" : "Hello, World!"}');
```

```
┌─name─┬─type─┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ JSON │              │                    │         │                  │                │
└──────┴──────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

## Schema inference modes[​](#schema-inference-modes "Direct link to Schema inference modes")

Schema inference from the set of data files can work in 2 different modes: `default` and `union`.
The mode is controlled by the setting `schema_inference_mode`.

### Default mode[​](#default-schema-inference-mode "Direct link to Default mode")

In default mode, ClickHouse assumes that all files have the same schema and tries to infer the schema by reading files one by one until it succeeds.

Example:

Let's say we have 3 files `data1.jsonl`, `data2.jsonl` and `data3.jsonl` with the next content:

`data1.jsonl`:

```
{"field1" :  1, "field2" :  null}
{"field1" :  2, "field2" :  null}
{"field1" :  3, "field2" :  null}
```

`data2.jsonl`:

```
{"field1" :  4, "field2" :  "Data4"}
{"field1" :  5, "field2" :  "Data5"}
{"field1" :  6, "field2" :  "Data5"}
```

`data3.jsonl`:

```
{"field1" :  7, "field2" :  "Data7", "field3" :  [1, 2, 3]}
{"field1" :  8, "field2" :  "Data8", "field3" :  [4, 5, 6]}
{"field1" :  9, "field2" :  "Data9", "field3" :  [7, 8, 9]}
```

Let's try to use schema inference on these 3 files:

```
:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='default'
```

Result:

```
┌─name───┬─type─────────────┐
│ field1 │ Nullable(Int64)  │
│ field2 │ Nullable(String) │
└────────┴──────────────────┘
```

As we can see, we don't have `field3` from file `data3.jsonl`.
It happens because ClickHouse first tried to infer schema from file `data1.jsonl`, failed because of only nulls for field `field2`,
and then tried to infer schema from `data2.jsonl` and succeeded, so data from file `data3.jsonl` wasn't read.

### Union mode[​](#default-schema-inference-mode-1 "Direct link to Union mode")

In union mode, ClickHouse assumes that files can have different schemas, so it infer schemas of all files and then union them to the common schema.

Let's say we have 3 files `data1.jsonl`, `data2.jsonl` and `data3.jsonl` with the next content:

`data1.jsonl`:

```
{"field1" :  1}
{"field1" :  2}
{"field1" :  3}
```

`data2.jsonl`:

```
{"field2" :  "Data4"}
{"field2" :  "Data5"}
{"field2" :  "Data5"}
```

`data3.jsonl`:

```
{"field3" :  [1, 2, 3]}
{"field3" :  [4, 5, 6]}
{"field3" :  [7, 8, 9]}
```

Let's try to use schema inference on these 3 files:

```
:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='union'
```

Result:

```
┌─name───┬─type───────────────────┐
│ field1 │ Nullable(Int64)        │
│ field2 │ Nullable(String)       │
│ field3 │ Array(Nullable(Int64)) │
└────────┴────────────────────────┘
```

As we can see, we have all fields from all files.

Note:

- As some of the files may not contain some columns from the resulting schema, union mode is supported only for formats that support reading subset of columns (like JSONEachRow, Parquet, TSVWithNames, etc) and won't work for other formats (like CSV, TSV, JSONCompactEachRow, etc).
- If ClickHouse cannot infer the schema from one of the files, the exception will be thrown.
- If you have a lot of files, reading schema from all of them can take a lot of time.

## Automatic format detection[​](#automatic-format-detection "Direct link to Automatic format detection")

If data format is not specified and cannot be determined by the file extension, ClickHouse will try to detect the file format by its content.

**Examples:**

Let's say we have `data` with the following content:

```
"a","b"
1,"Data1"
2,"Data2"
3,"Data3"
```

We can inspect and query this file without specifying format or structure:

```
:) desc file(data);
```

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

```
:) select * from file(data);
```

```
┌─a─┬─b─────┐
│ 1 │ Data1 │
│ 2 │ Data2 │
│ 3 │ Data3 │
└───┴───────┘
```

Note

ClickHouse can detect only some subset of formats and this detection takes some time, it's always better to specify the format explicitly.

- [Usage](#usage)- [Table functions file, s3, url, hdfs, azureBlobStorage.](#table-functions-file-s3-url-hdfs-azureblobstorage)- [Table engines File, S3, URL, HDFS, azureBlobStorage](#table-engines-file-s3-url-hdfs-azureblobstorage)- [clickhouse-local](#clickhouse-local)- [Using structure from insertion table](#using-structure-from-insertion-table)- [Schema inference cache](#schema-inference-cache)- [Text formats](#text-formats)
              - [JSON formats](#json-formats)- [CSV](#csv)- [TSV/TSKV](#tsv-tskv)- [Values](#values)- [CustomSeparated](#custom-separated)- [Template](#template)- [Regexp](#regexp)- [Settings for text formats](#settings-for-text-formats)- [Self describing formats](#self-describing-formats)
                - [Formats with -WithNamesAndTypes suffix](#formats-with-names-and-types)- [JSON formats with metadata](#json-with-metadata)- [Avro](#avro)- [Parquet](#parquet)- [Arrow](#arrow)- [ORC](#orc)- [Native](#native)- [Formats with external schema](#formats-with-external-schema)
                  - [Protobuf](#protobuf)- [CapnProto](#capnproto)- [Strong-typed binary formats](#strong-typed-binary-formats)
                    - [MsgPack](#msgpack)- [BSONEachRow](#bsoneachrow)- [Formats with constant schema](#formats-with-constant-schema)
                      - [LineAsString](#line-as-string)- [JSONAsString](#json-as-string)- [JSONAsObject](#json-as-object)- [Schema inference modes](#schema-inference-modes)
                        - [Default mode](#default-schema-inference-mode)- [Union mode](#default-schema-inference-mode-1)- [Automatic format detection](#automatic-format-detection)

Was this page helpful?