Format Settings

Allow to use String type for JSON keys that contain only `Null`/`{}`/`[]` in data sample during schema inference.
In JSON formats any value can be read as String, and we can avoid errors like `Cannot determine type for column 'column_name' by first 25000 rows of data, most likely this column contains only Nulls or empty Arrays/Maps` during schema inference
by using String type for keys with unknown types.

Example:

```
SET input_format_json_infer_incomplete_types_as_strings = 1, input_format_json_try_infer_named_tuples_from_objects = 1;
DESCRIBE format(JSONEachRow, '{"obj" : {"a" : [1,2,3], "b" : "hello", "c" : null, "d" : {}, "e" : []}}');
SELECT * FROM format(JSONEachRow, '{"obj" : {"a" : [1,2,3], "b" : "hello", "c" : null, "d" : {}, "e" : []}}');
```

Result:

```
┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Tuple(a Array(Nullable(Int64)), b Nullable(String), c Nullable(String), d Nullable(String), e Array(Nullable(String))) │              │                    │         │                  │                │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

┌─obj────────────────────────────┐
│ ([1,2,3],'hello',NULL,'{}',[]) │
└────────────────────────────────┘
```

Enabled by default.

## input\_format\_json\_map\_as\_array\_of\_tuples[​](#input_format_json_map_as_array_of_tuples "Direct link to input_format_json_map_as_array_of_tuples")

Deserialize maps columns as JSON arrays of tuples.

Disabled by default.

## input\_format\_json\_max\_depth[​](#input_format_json_max_depth "Direct link to input_format_json_max_depth")

Maximum depth of a field in JSON. This is not a strict limit, it does not have to be applied precisely.

## input\_format\_json\_named\_tuples\_as\_objects[​](#input_format_json_named_tuples_as_objects "Direct link to input_format_json_named_tuples_as_objects")

Parse named tuple columns as JSON objects.

Enabled by default.

## input\_format\_json\_read\_arrays\_as\_strings[​](#input_format_json_read_arrays_as_strings "Direct link to input_format_json_read_arrays_as_strings")

Allow parsing JSON arrays as strings in JSON input formats.

Example:

```
SET input_format_json_read_arrays_as_strings = 1;
SELECT arr, toTypeName(arr), JSONExtractArrayRaw(arr)[3] from format(JSONEachRow, 'arr String', '{"arr" : [1, "Hello", [1,2,3]]}');
```

Result:

```
┌─arr───────────────────┬─toTypeName(arr)─┬─arrayElement(JSONExtractArrayRaw(arr), 3)─┐
│ [1, "Hello", [1,2,3]] │ String          │ [1,2,3]                                   │
└───────────────────────┴─────────────────┴───────────────────────────────────────────┘
```

Enabled by default.

## input\_format\_json\_read\_bools\_as\_numbers[​](#input_format_json_read_bools_as_numbers "Direct link to input_format_json_read_bools_as_numbers")

Allow parsing bools as numbers in JSON input formats.

Enabled by default.

## input\_format\_json\_read\_bools\_as\_strings[​](#input_format_json_read_bools_as_strings "Direct link to input_format_json_read_bools_as_strings")

Allow parsing bools as strings in JSON input formats.

Enabled by default.

## input\_format\_json\_read\_numbers\_as\_strings[​](#input_format_json_read_numbers_as_strings "Direct link to input_format_json_read_numbers_as_strings")

Allow parsing numbers as strings in JSON input formats.

Enabled by default.

## input\_format\_json\_read\_objects\_as\_strings[​](#input_format_json_read_objects_as_strings "Direct link to input_format_json_read_objects_as_strings")

Allow parsing JSON objects as strings in JSON input formats.

Example:

```
SET input_format_json_read_objects_as_strings = 1;
CREATE TABLE test (id UInt64, obj String, date Date) ENGINE=Memory();
INSERT INTO test FORMAT JSONEachRow {"id" : 1, "obj" : {"a" : 1, "b" : "Hello"}, "date" : "2020-01-01"};
SELECT * FROM test;
```

Result:

```
┌─id─┬─obj──────────────────────┬───────date─┐
│  1 │ {"a" : 1, "b" : "Hello"} │ 2020-01-01 │
└────┴──────────────────────────┴────────────┘
```

Enabled by default.

## input\_format\_json\_throw\_on\_bad\_escape\_sequence[​](#input_format_json_throw_on_bad_escape_sequence "Direct link to input_format_json_throw_on_bad_escape_sequence")

Throw an exception if JSON string contains bad escape sequence in JSON input formats. If disabled, bad escape sequences will remain as is in the data.

Enabled by default.

## input\_format\_json\_try\_infer\_named\_tuples\_from\_objects[​](#input_format_json_try_infer_named_tuples_from_objects "Direct link to input_format_json_try_infer_named_tuples_from_objects")

If enabled, during schema inference ClickHouse will try to infer named Tuple from JSON objects.
The resulting named Tuple will contain all elements from all corresponding JSON objects from sample data.

Example:

```
SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : {"a" : 42, "b" : "Hello"}}, {"obj" : {"a" : 43, "c" : [1, 2, 3]}}, {"obj" : {"d" : {"e" : 42}}}')
```

Result:

```
┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Tuple(e Nullable(Int64))) │              │                    │         │                  │                │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

Enabled by default.

## input\_format\_json\_try\_infer\_numbers\_from\_strings[​](#input_format_json_try_infer_numbers_from_strings "Direct link to input_format_json_try_infer_numbers_from_strings")

If enabled, during schema inference ClickHouse will try to infer numbers from string fields.
It can be useful if JSON data contains quoted UInt64 numbers.

Disabled by default.

## input\_format\_json\_use\_string\_type\_for\_ambiguous\_paths\_in\_named\_tuples\_inference\_from\_objects[​](#input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects "Direct link to input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects")

Use String type instead of an exception in case of ambiguous paths in JSON objects during named tuples inference

## input\_format\_json\_validate\_types\_from\_metadata[​](#input_format_json_validate_types_from_metadata "Direct link to input_format_json_validate_types_from_metadata")

For JSON/JSONCompact/JSONColumnsWithMetadata input formats, if this setting is set to 1,
the types from metadata in input data will be compared with the types of the corresponding columns from the table.

Enabled by default.

## input\_format\_max\_block\_size\_bytes[​](#input_format_max_block_size_bytes "Direct link to input_format_max_block_size_bytes")

Limits the size of the blocks formed during data parsing in input formats in bytes. Used in row based input formats when block is formed on ClickHouse side.
0 means no limit in bytes.

## input\_format\_max\_block\_wait\_ms[​](#input_format_max_block_wait_ms "Direct link to input_format_max_block_wait_ms")

Limits the maximum time in milliseconds to wait before emitting a block during parsing in row-based input formats. 0 means no limit.

Note

This option only works if `input_format_connection_handling` is enabled. Setting a value also disables parallel parsing and makes deduplication impossible.

Note

For streaming inserts, you must also set `min_insert_block_size_rows=0` and `min_insert_block_size_bytes=0`. Otherwise, parsed blocks may still be accumulated in memory by the block squashing stage until those thresholds are reached, preventing timely inserts.

**Example: streaming Wikipedia recent changes into ClickHouse**