JSON Data Type

Note

When paths are stored in basic (`map`) [shared data](#shared-data-structure), reading sub-object sub-columns may be inefficient as it requires scanning the entire shared data structure. With `map_with_buckets` or `advanced` shared data serialization, reading sub-columns from shared data is highly optimized.

## Reading JSON combined sub-columns[​](#reading-json-combined-sub-columns "Direct link to Reading JSON combined sub-columns")

The `JSON` type supports reading a path as a **combined sub-column** using the special syntax `[email protected]`.
A combined sub-column for a given path returns:

- The literal value stored at that path as `Dynamic`, if the path has a literal value.
- A JSON sub-object at that path as `Dynamic`, if the path has no literal value but has nested sub-paths.
- `NULL`, if neither a literal value nor any sub-paths exist for that path.

This is useful when a path may hold either a scalar value or a nested object across different rows, and is more convenient than separately querying the literal sub-column (`json.a`) and the sub-object sub-column (`json.^a`).

The following example compares all three sub-column types for path `a`:

```
CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : 42, "b" : {"c" : 1, "d" : "Hello"}}'), ('{"a" : {"x": 1, "y": 2}, "b" : {"c" : 1}}'), ('{"c" : "World"}');
SELECT json FROM test;
```

```
┌─json────────────────────────────┐
│ {"a":42,"b":{"c":1,"d":"Hello"}}│
│ {"a":{"x":1,"y":2},"b":{"c":1}}│
│ {"c":"World"}                   │
└─────────────────────────────────┘
```

```
SELECT
    json.a,
    dynamicType(json.a),
    json.^a,
    toTypeName(json.^a),
    json.@a,
    dynamicType(json.@a)
FROM test;
```

```
┌─json.a─┬─dynamicType(json.a)─┬─json.^a───────┬─toTypeName(json.^a)─┬─json.@a───────┬─dynamicType(json.@a)─┐
│ 42     │ Int64               │ {}            │ JSON                │ 42            │ Int64                │
│ NULL   │ None                │ {"x":1,"y":2} │ JSON                │ {"x":1,"y":2} │ JSON                 │
│ NULL   │ None                │ {}            │ JSON                │ NULL          │ None                 │
└────────┴─────────────────────┴───────────────┴─────────────────────┴───────────────┴──────────────────────┘
```

- Row 1: `a` holds a literal `42`. `json.a` returns it as `Dynamic(Int64)`, `json.^a` returns an empty sub-object `{}` (no nested keys under `a`), and `json.@a` returns the literal `42`.
- Row 2: `a` holds a nested object. `json.a` returns `NULL` (no literal at that path), `json.^a` returns the sub-object as `JSON`, and `json.@a` also returns the sub-object as `Dynamic(JSON)`.
- Row 3: `a` is absent entirely. Both `json.a` and `json.@a` return `NULL`, while `json.^a` returns an empty `{}`.

Note

When paths are stored in basic (`map`) [shared data](#shared-data-structure), reading combined sub-columns may be inefficient as it requires scanning the entire shared data structure. With `map_with_buckets` or `advanced` shared data serialization, reading sub-columns from shared data is highly optimized.

## Type inference for paths[​](#type-inference-for-paths "Direct link to Type inference for paths")

During parsing of `JSON`, ClickHouse tries to detect the most appropriate data type for each JSON path.
It works similarly to [automatic schema inference from input data](/docs/interfaces/schema-inference),
and is controlled by the same settings:

- [input\_format\_try\_infer\_dates](/docs/operations/settings/formats#input_format_try_infer_dates)
- [input\_format\_try\_infer\_datetimes](/docs/operations/settings/formats#input_format_try_infer_datetimes)
- [schema\_inference\_make\_columns\_nullable](/docs/operations/settings/formats#schema_inference_make_columns_nullable)
- [input\_format\_json\_try\_infer\_numbers\_from\_strings](/docs/operations/settings/formats#input_format_json_try_infer_numbers_from_strings)
- [input\_format\_json\_infer\_incomplete\_types\_as\_strings](/docs/operations/settings/formats#input_format_json_infer_incomplete_types_as_strings)
- [input\_format\_json\_read\_numbers\_as\_strings](/docs/operations/settings/formats#input_format_json_read_numbers_as_strings)
- [input\_format\_json\_read\_bools\_as\_strings](/docs/operations/settings/formats#input_format_json_read_bools_as_strings)
- [input\_format\_json\_read\_bools\_as\_numbers](/docs/operations/settings/formats#input_format_json_read_bools_as_numbers)
- [input\_format\_json\_read\_arrays\_as\_strings](/docs/operations/settings/formats#input_format_json_read_arrays_as_strings)
- [input\_format\_json\_infer\_array\_of\_dynamic\_from\_array\_of\_different\_types](/docs/operations/settings/formats#input_format_json_infer_array_of_dynamic_from_array_of_different_types)

Let's take a look at some examples:

```
SELECT JSONAllPathsWithTypes('{"a" : "2020-01-01", "b" : "2020-01-01 10:00:00"}'::JSON) AS paths_with_types settings input_format_try_infer_dates=1, input_format_try_infer_datetimes=1;
```

```
┌─paths_with_types─────────────────┐
│ {'a':'Date','b':'DateTime64(9)'} │
└──────────────────────────────────┘
```

```
SELECT JSONAllPathsWithTypes('{"a" : "2020-01-01", "b" : "2020-01-01 10:00:00"}'::JSON) AS paths_with_types settings input_format_try_infer_dates=0, input_format_try_infer_datetimes=0;
```

```
┌─paths_with_types────────────┐
│ {'a':'String','b':'String'} │
└─────────────────────────────┘
```

```
SELECT JSONAllPathsWithTypes('{"a" : [1, 2, 3]}'::JSON) AS paths_with_types settings schema_inference_make_columns_nullable=1;
```

```
┌─paths_with_types───────────────┐
│ {'a':'Array(Nullable(Int64))'} │
└────────────────────────────────┘
```

```
SELECT JSONAllPathsWithTypes('{"a" : [1, 2, 3]}'::JSON) AS paths_with_types settings schema_inference_make_columns_nullable=0;
```

```
┌─paths_with_types─────┐
│ {'a':'Array(Int64)'} │
└──────────────────────┘
```

## Handling arrays of JSON objects[​](#handling-arrays-of-json-objects "Direct link to Handling arrays of JSON objects")

JSON paths that contain an array of objects are parsed as type `Array(JSON)` and inserted into a `Dynamic` column for the path.
To read an array of objects, you can extract it from the `Dynamic` column as a sub-column:

```
CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES
('{"a" : {"b" : [{"c" : 42, "d" : "Hello", "f" : [[{"g" : 42.42}]], "k" : {"j" : 1000}}, {"c" : 43}, {"e" : [1, 2, 3], "d" : "My", "f" : [[{"g" : 43.43, "h" : "2020-01-01"}]],  "k" : {"j" : 2000}}]}}'),
('{"a" : {"b" : [1, 2, 3]}}'),
('{"a" : {"b" : [{"c" : 44, "f" : [[{"h" : "2020-01-02"}]]}, {"e" : [4, 5, 6], "d" : "World", "f" : [[{"g" : 44.44}]],  "k" : {"j" : 3000}}]}}');
SELECT json FROM test;
```

```
┌─json────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"a":{"b":[{"c":"42","d":"Hello","f":[[{"g":42.42}]],"k":{"j":"1000"}},{"c":"43"},{"d":"My","e":["1","2","3"],"f":[[{"g":43.43,"h":"2020-01-01"}]],"k":{"j":"2000"}}]}} │
│ {"a":{"b":["1","2","3"]}}                                                                                                                                               │
│ {"a":{"b":[{"c":"44","f":[[{"h":"2020-01-02"}]]},{"d":"World","e":["4","5","6"],"f":[[{"g":44.44}]],"k":{"j":"3000"}}]}}                                                │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

```
SELECT json.a.b, dynamicType(json.a.b) FROM test;
```