JSON 类型会在单个列中存储 JavaScript Object Notation (JSON) 文档。
注意
在 ClickHouse 开源版中，JSON 数据类型从 25.3 版本开始被标记为可用于生产环境。不建议在更早的版本中在生产环境使用此类型。
要声明一个 JSON 类型的列，可以使用以下语法：
<column_name> JSON
(
max_dynamic_paths=N,
max_dynamic_types=M,
some.path TypeName,
SKIP path.to.skip,
SKIP REGEXP 'paths_regexp'
)
上述语法中的各参数定义如下：
| Parameter | Description | Default Value |
|---|
max_dynamic_paths | 一个可选参数，表示在单个独立存储的数据块中 (例如 MergeTree 表的单个 data part) ，最多可以有多少个路径以子列形式单独存储。
如果超过此限制，所有其余路径将合并存储在一个名为共享数据的单一结构中。
还可以通过其他方式在不修改该参数的情况下更改动态路径的限制。 | 1024 |
max_dynamic_types | 一个取值范围在 1 到 255 之间的可选参数，表示在单个独立存储的数据块中 (例如 MergeTree 表的单个 data part) ，在类型为 Dynamic 的单个路径列中最多可以有多少种不同的数据类型单独存储。
如果超过此限制，所有新增类型将合并存储在一个名为 shared variant 的单一结构中。 | 32 |
some.path TypeName | 针对 JSON 中特定路径的可选类型提示。此类路径将始终作为具有指定类型的子列进行存储。 | |
SKIP path.to.skip | 针对在 JSON 解析期间应跳过的特定路径的可选提示。此类路径将永远不会存储在 JSON 列中。若指定路径是一个嵌套 JSON 对象，则整个嵌套对象都会被跳过。 | |
SKIP REGEXP 'path_regexp' | 使用正则表达式在 JSON 解析期间跳过路径的可选提示。所有匹配此正则表达式的路径将永远不会存储在 JSON 列中。 | |
何时使用 JSON 类型
JSON 类型专为在结构动态或不可预测的 JSON 对象中，对特定字段进行查询、过滤和聚合而设计。它通过将 JSON 对象拆分为多个子列来实现这一点，与使用 Map 或解析字符串等方案相比，可以显著减少选定字段的数据读取量，从而加速查询。
不过，这也带来了一些重要的权衡：
INSERT 操作较慢 —— 将 JSON 拆分为子列、执行类型推断以及管理灵活的存储结构，会使插入操作比将 JSON 作为简单的 String 列存储更慢。- 读取整个对象时较慢 —— 如果你需要获取完整的 JSON 文档（而不是特定字段），从
JSON 类型读取会比从 String 列读取更慢。当你不进行字段级查询时，从多个子列重建对象的开销并不会带来任何收益。
- 存储开销更大 —— 相比将 JSON 存为单个字符串值，维护独立的子列会增加结构性存储开销。
在以下情况使用 JSON 类型：
- 当数据结构具有动态性或不可预测性，不同文档之间的键各不相同
- 字段类型或模式（schema）会随时间变化，或在不同记录之间有所差异
- 你需要在无法预先预测结构的 JSON 对象中，对特定路径进行查询、过滤或聚合
- 你的使用场景涉及半结构化数据，如日志、事件，或模式（schema）不一致的用户生成内容
在以下情况下使用 String 列（或结构化类型）：
- 你的数据结构是已知且保持一致的——在这种情况下，应使用常规列、
Tuple、Array、Dynamic 或 Variant 类型来代替
JSON 文档被当作不透明的二进制大对象（blob），只以整体形式存储和检索，而不进行字段级分析- 你不需要在数据库中对单个 JSON 字段进行查询或过滤
JSON 只是传输/存储格式，不在 ClickHouse 内进行分析
提示
如果 JSON 是在数据库内部不被分析、仅用于存储和读取的不透明文档，则应将其存储为 String 字段。只有当你需要在动态 JSON 结构中的特定字段上进行高效的查询、过滤或聚合时，JSON 类型的优势才会体现出来。
你也可以组合使用多种方式——对可预测的顶层字段使用标准列，对负载中动态部分使用 JSON 列。
创建 JSON
本节将介绍创建 JSON 的多种方法。
在表的列定义中使用 JSON
CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : 42}, "c" : [1, 2, 3]}'), ('{"f" : "Hello, World!"}'), ('{"a" : {"b" : 43, "e" : 10}, "c" : [4, 5, 6]}');
SELECT json FROM test;
┌─json────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"]} │
│ {"f":"Hello, World!"} │
│ {"a":{"b":"43","e":"10"},"c":["4","5","6"]} │
└─────────────────────────────────────────────┘
CREATE TABLE test (json JSON(a.b UInt32, SKIP a.e)) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : 42}, "c" : [1, 2, 3]}'), ('{"f" : "Hello, World!"}'), ('{"a" : {"b" : 43, "e" : 10}, "c" : [4, 5, 6]}');
SELECT json FROM test;
┌─json──────────────────────────────┐
│ {"a":{"b":42},"c":["1","2","3"]} │
│ {"a":{"b":0},"f":"Hello, World!"} │
│ {"a":{"b":43},"c":["4","5","6"]} │
└───────────────────────────────────┘
使用 ::JSON 进行 CAST
可以使用特殊语法 ::JSON 对多种类型进行类型转换。
使用 CAST 将 String 转换为 JSON
SELECT '{"a" : {"b" : 42},"c" : [1, 2, 3], "d" : "Hello, World!"}'::JSON AS json;
┌─json───────────────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"],"d":"Hello, World!"} │
└────────────────────────────────────────────────────────┘
使用 CAST 将 Tuple 转换为 JSON
SET enable_named_columns_in_function_tuple = 1;
SELECT (tuple(42 AS b) AS a, [1, 2, 3] AS c, 'Hello, World!' AS d)::JSON AS json;
┌─json───────────────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"],"d":"Hello, World!"} │
└────────────────────────────────────────────────────────┘
使用 CAST 将 Map 转换为 JSON
SET use_variant_as_common_type=1;
SELECT map('a', map('b', 42), 'c', [1,2,3], 'd', 'Hello, World!')::JSON AS json;
┌─json───────────────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"],"d":"Hello, World!"} │
└────────────────────────────────────────────────────────┘
注意
JSON 路径会被存储为扁平结构。这意味着，当根据类似 a.b.c 这样的路径来构造一个 JSON 对象时，
无法确定该对象应被构造为 { "a.b.c" : ... } 还是 { "a": { "b": { "c": ... } } }。
我们的实现始终会采用后者的形式。
例如：
SELECT CAST('{"a.b.c" : 42}', 'JSON') AS json
将返回：
┌─json───────────────────┐
1. │ {"a":{"b":{"c":"42"}}} │
└────────────────────────┘
而不是：
┌─json───────────┐
1. │ {"a.b.c":"42"} │
└────────────────┘
将 JSON 路径读取为子列
JSON 类型支持将 JSON 中的每个路径读取为独立的子列。
如果在 JSON 类型的声明中未指定所请求路径的类型，
则该路径对应的子列类型始终为 Dynamic。
例如：
CREATE TABLE test (json JSON(a.b UInt32, SKIP a.e)) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : 42, "g" : 42.42}, "c" : [1, 2, 3], "d" : "2020-01-01"}'), ('{"f" : "Hello, World!", "d" : "2020-01-02"}'), ('{"a" : {"b" : 43, "e" : 10, "g" : 43.43}, "c" : [4, 5, 6]}');
SELECT json FROM test;
┌─json────────────────────────────────────────────────────────┐
│ {"a":{"b":42,"g":42.42},"c":["1","2","3"],"d":"2020-01-01"} │
│ {"a":{"b":0},"d":"2020-01-02","f":"Hello, World!"} │
│ {"a":{"b":43,"g":43.43},"c":["4","5","6"]} │
└─────────────────────────────────────────────────────────────┘
SELECT json.a.b, json.a.g, json.c, json.d FROM test;
┌─json.a.b─┬─json.a.g─┬─json.c──┬─json.d─────┐
│ 42 │ 42.42 │ [1,2,3] │ 2020-01-01 │
│ 0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 2020-01-02 │
│ 43 │ 43.43 │ [4,5,6] │ ᴺᵁᴸᴸ │
└──────────┴──────────┴─────────┴────────────┘
你还可以使用 getSubcolumn 函数从 JSON 类型中读取子列：
SELECT getSubcolumn(json, 'a.b'), getSubcolumn(json, 'a.g'), getSubcolumn(json, 'c'), getSubcolumn(json, 'd') FROM test;
┌─getSubcolumn(json, 'a.b')─┬─getSubcolumn(json, 'a.g')─┬─getSubcolumn(json, 'c')─┬─getSubcolumn(json, 'd')─┐
│ 42 │ 42.42 │ [1,2,3] │ 2020-01-01 │
│ 0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 2020-01-02 │
│ 43 │ 43.43 │ [4,5,6] │ ᴺᵁᴸᴸ │
└───────────────────────────┴───────────────────────────┴─────────────────────────┴─────────────────────────┘
如果在数据中找不到请求的路径，则会用 NULL 值进行填充：
SELECT json.non.existing.path FROM test;
┌─json.non.existing.path─┐
│ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ │
└────────────────────────┘
让我们检查一下这些返回子列的数据类型：
SELECT toTypeName(json.a.b), toTypeName(json.a.g), toTypeName(json.c), toTypeName(json.d) FROM test;
┌─toTypeName(json.a.b)─┬─toTypeName(json.a.g)─┬─toTypeName(json.c)─┬─toTypeName(json.d)─┐
│ UInt32 │ Dynamic │ Dynamic │ Dynamic │
│ UInt32 │ Dynamic │ Dynamic │ Dynamic │
│ UInt32 │ Dynamic │ Dynamic │ Dynamic │
└──────────────────────┴──────────────────────┴────────────────────┴────────────────────┘
如上所示，对于 a.b，其类型为 UInt32，这是在 JSON 类型声明中显式指定的；
而所有其他子列的类型则为 Dynamic。
也可以使用特殊语法 json.some.path.:TypeName 来读取 Dynamic 类型的子列：
SELECT
json.a.g.:Float64,
dynamicType(json.a.g),
json.d.:Date,
dynamicType(json.d)
FROM test
┌─json.a.g.:`Float64`─┬─dynamicType(json.a.g)─┬─json.d.:`Date`─┬─dynamicType(json.d)─┐
│ 42.42 │ Float64 │ 2020-01-01 │ Date │
│ ᴺᵁᴸᴸ │ None │ 2020-01-02 │ Date │
│ 43.43 │ Float64 │ ᴺᵁᴸᴸ │ None │
└─────────────────────┴───────────────────────┴────────────────┴─────────────────────┘
Dynamic 子列可以转换为任意数据类型。在这种情况下，如果 Dynamic 中的内部类型无法转换为所请求的类型，则会抛出异常：
SELECT json.a.g::UInt64 AS uint
FROM test;
┌─uint─┐
│ 42 │
│ 0 │
│ 43 │
└──────┘
SELECT json.a.g::UUID AS float
FROM test;
Received exception from server:
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception:
Conversion between numeric types and UUID is not supported.
Probably the passed UUID is unquoted:
while executing 'FUNCTION CAST(__table1.json.a.g :: 2, 'UUID'_String :: 1) -> CAST(__table1.json.a.g, 'UUID'_String) UUID : 0'.
(NOT_IMPLEMENTED)
将 JSON 子对象读取为子列
JSON 类型支持使用特殊语法 json.^some.path 将嵌套对象读取为 JSON 类型的子列：
CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : {"c" : 42, "g" : 42.42}}, "c" : [1, 2, 3], "d" : {"e" : {"f" : {"g" : "Hello, World", "h" : [1, 2, 3]}}}}'), ('{"f" : "Hello, World!", "d" : {"e" : {"f" : {"h" : [4, 5, 6]}}}}'), ('{"a" : {"b" : {"c" : 43, "e" : 10, "g" : 43.43}}, "c" : [4, 5, 6]}');
SELECT json FROM test;
┌─json──────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"a":{"b":{"c":"42","g":42.42}},"c":["1","2","3"],"d":{"e":{"f":{"g":"Hello, World","h":["1","2","3"]}}}} │
│ {"d":{"e":{"f":{"h":["4","5","6"]}}},"f":"Hello, World!"} │
│ {"a":{"b":{"c":"43","e":"10","g":43.43}},"c":["4","5","6"]} │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘
SELECT json.^a.b, json.^d.e.f FROM test;
┌─json.^`a`.b───────────────────┬─json.^`d`.e.f──────────────────────────┐
│ {"c":"42","g":42.42} │ {"g":"Hello, World","h":["1","2","3"]} │
│ {} │ {"h":["4","5","6"]} │
│ {"c":"43","e":"10","g":43.43} │ {} │
└───────────────────────────────┴────────────────────────────────────────┘
注意
当路径存储在基础 (map) 共享数据中时，读取子对象子列的效率可能较低，因为这需要扫描整个共享数据结构。使用 map_with_buckets 或 advanced 共享数据序列化时，从共享数据中读取子列的效率会大幅提升。
读取 JSON 组合子列
JSON 类型支持使用特殊语法 [email protected]，将某一路径作为组合子列读取。
给定路径的组合子列会返回：
- 如果该路径具有字面值，则返回存储在该路径上的字面值，类型为
Dynamic。
- 如果该路径没有字面值，但存在嵌套子路径，则返回该路径上的 JSON 子对象，类型为
Dynamic。
- 如果该路径既不存在字面值，也不存在任何子路径，则返回
NULL。
当某一路径在不同的行中既可能保存标量值，也可能保存嵌套对象时，这种方式非常有用；相比于分别查询字面子列 (json.a) 和子对象子列 (json.^a) ，也更方便。
以下示例比较了路径 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 │
└────────┴─────────────────────┴───────────────┴─────────────────────┴───────────────┴──────────────────────┘
- 第 1 行：
a 保存的是字面量 42。json.a 返回 Dynamic(Int64)，json.^a 返回空子对象 {} (a 下没有嵌套键) ，而 json.@a 返回字面量 42。
- 第 2 行：
a 保存的是一个嵌套对象。json.a 返回 NULL (该路径上没有字面量) ，json.^a 以 JSON 形式返回该子对象，json.@a 也会以 Dynamic(JSON) 形式返回该子对象。
- 第 3 行：
a 完全不存在。json.a 和 json.@a 都返回 NULL，而 json.^a 返回空的 {}。
注意
当路径存储在基础 (map) 共享数据中时，读取组合子列的效率可能较低，因为这需要扫描整个共享数据结构。使用 map_with_buckets 或 advanced 共享数据序列化时，从共享数据中读取子列会得到高度优化。
路径的类型推断
在解析 JSON 时，ClickHouse 会尝试为每个 JSON 路径推断出最合适的数据类型。
其工作方式类似于基于输入数据自动推断表结构，
并且由相同的设置控制：
下面来看一些示例：
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)'} │
└──────────────────────┘
处理 JSON 对象数组
包含对象数组的 JSON 路径会被解析为 Array(JSON) 类型，并插入到该路径对应的 Dynamic 列中。
要读取对象数组，可以从 Dynamic 列中将其提取为子列：
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;
┌─json.a.b──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─dynamicType(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"}}'] │ Array(JSON(max_dynamic_types=16, max_dynamic_paths=256)) │
│ [1,2,3] │ Array(Nullable(Int64)) │
│ ['{"c":"44","f":[[{"h":"2020-01-02"}]]}','{"d":"World","e":["4","5","6"],"f":[[{"g":44.44}]],"k":{"j":"3000"}}'] │ Array(JSON(max_dynamic_types=16, max_dynamic_paths=256)) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────┘
正如你可能已经注意到的，嵌套 JSON 类型的 max_dynamic_types/max_dynamic_paths 参数相比默认值被调低了。
这是为了避免在嵌套的 JSON 对象数组中，子列数量不受控制地增长。
让我们尝试从一个嵌套的 JSON 列中读取子列：
SELECT json.a.b.:`Array(JSON)`.c, json.a.b.:`Array(JSON)`.f, json.a.b.:`Array(JSON)`.d FROM test;
┌─json.a.b.:`Array(JSON)`.c─┬─json.a.b.:`Array(JSON)`.f───────────────────────────────────┬─json.a.b.:`Array(JSON)`.d─┐
│ [42,43,NULL] │ [[['{"g":42.42}']],NULL,[['{"g":43.43,"h":"2020-01-01"}']]] │ ['Hello',NULL,'My'] │
│ [] │ [] │ [] │
│ [44,NULL] │ [[['{"h":"2020-01-02"}']],[['{"g":44.44}']]] │ [NULL,'World'] │
└───────────────────────────┴─────────────────────────────────────────────────────────────┴───────────────────────────┘
我们可以使用一种特殊语法来避免显式写出 Array(JSON) 子列名：
SELECT json.a.b[].c, json.a.b[].f, json.a.b[].d FROM test;
┌─json.a.b.:`Array(JSON)`.c─┬─json.a.b.:`Array(JSON)`.f───────────────────────────────────┬─json.a.b.:`Array(JSON)`.d─┐
│ [42,43,NULL] │ [[['{"g":42.42}']],NULL,[['{"g":43.43,"h":"2020-01-01"}']]] │ ['Hello',NULL,'My'] │
│ [] │ [] │ [] │
│ [44,NULL] │ [[['{"h":"2020-01-02"}']],[['{"g":44.44}']]] │ [NULL,'World'] │
└───────────────────────────┴─────────────────────────────────────────────────────────────┴───────────────────────────┘
路径后面的 [] 数量表示数组的嵌套层级。例如，json.path[][] 会被转换为 json.path.:Array(Array(JSON))
让我们检查一下 Array(JSON) 中的路径和类型：
SELECT DISTINCT arrayJoin(JSONAllPathsWithTypes(arrayJoin(json.a.b[]))) FROM test;
┌─arrayJoin(JSONAllPathsWithTypes(arrayJoin(json.a.b.:`Array(JSON)`)))──┐
│ ('c','Int64') │
│ ('d','String') │
│ ('f','Array(Array(JSON(max_dynamic_types=8, max_dynamic_paths=64)))') │
│ ('k.j','Int64') │
│ ('e','Array(Nullable(Int64))') │
└───────────────────────────────────────────────────────────────────────┘
让我们从 Array(JSON) 列中读取子列：
SELECT json.a.b[].c.:Int64, json.a.b[].f[][].g.:Float64, json.a.b[].f[][].h.:Date FROM test;
┌─json.a.b.:`Array(JSON)`.c.:`Int64`─┬─json.a.b.:`Array(JSON)`.f.:`Array(Array(JSON))`.g.:`Float64`─┬─json.a.b.:`Array(JSON)`.f.:`Array(Array(JSON))`.h.:`Date`─┐
│ [42,43,NULL] │ [[[42.42]],[],[[43.43]]] │ [[[NULL]],[],[['2020-01-01']]] │
│ [] │ [] │ [] │
│ [44,NULL] │ [[[NULL]],[[44.44]]] │ [[['2020-01-02']],[[NULL]]] │
└────────────────────────────────────┴──────────────────────────────────────────────────────────────┴───────────────────────────────────────────────────────────┘
我们还可以从嵌套的 JSON 列中读取子对象中的子列：
SELECT json.a.b[].^k FROM test
┌─json.a.b.:`Array(JSON)`.^`k`─────────┐
│ ['{"j":"1000"}','{}','{"j":"2000"}'] │
│ [] │
│ ['{}','{"j":"3000"}'] │
└──────────────────────────────────────┘
处理包含 NULL 的 JSON 键
在我们的 JSON 实现中，null 与值的缺失被视为等同：
SELECT '{}'::JSON AS json1, '{"a" : null}'::JSON AS json2, json1 = json2
┌─json1─┬─json2─┬─equals(json1, json2)─┐
│ {} │ {} │ 1 │
└───────┴───────┴──────────────────────┘
这意味着无法确定原始 JSON 数据中，是包含某个路径且该路径的值为 NULL，还是根本不包含该路径。
处理包含点号的 JSON 键
在内部实现中，JSON 列会以扁平化的形式存储所有路径和值。这意味着在默认情况下，下面这两个对象会被视为相同：
{"a" : {"b" : 42}}
{"a.b" : 42}
它们在内部都会以路径和值的二元组形式存储，即路径 a.b 和值 42。在格式化 JSON 时，我们始终基于以点分隔的路径片段来构造嵌套对象：
SELECT '{"a" : {"b" : 42}}'::JSON AS json1, '{"a.b" : 42}'::JSON AS json2, JSONAllPaths(json1), JSONAllPaths(json2);
┌─json1────────────┬─json2────────────┬─JSONAllPaths(json1)─┬─JSONAllPaths(json2)─┐
│ {"a":{"b":"42"}} │ {"a":{"b":"42"}} │ ['a.b'] │ ['a.b'] │
└──────────────────┴──────────────────┴─────────────────────┴─────────────────────┘
如你所见，最初的 JSON {"a.b" : 42} 现在被格式化为 {"a" : {"b" : 42}}。
这一限制还会导致在解析如下这种有效 JSON 对象时失败：
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON AS json;
Code: 117. DB::Exception: Cannot insert data into JSON column: Duplicate path found during parsing JSON object: a.b. You can enable setting type_json_skip_duplicated_paths to skip duplicated paths during insert: In scope SELECT CAST('{"a.b" : 42, "a" : {"b" : "Hello, World"}}', 'JSON') AS json. (INCORRECT_DATA)
如果你希望保留带点号的键并避免将其格式化为嵌套对象，可以启用 json_type_escape_dots_in_keys 设置（自 25.8 版本起可用）。在这种情况下，解析时 JSON 键中的所有点号都会被转义为 %2E，并在格式化时再还原为点号。
SET json_type_escape_dots_in_keys=1;
SELECT '{"a" : {"b" : 42}}'::JSON AS json1, '{"a.b" : 42}'::JSON AS json2, JSONAllPaths(json1), JSONAllPaths(json2);
┌─json1────────────┬─json2────────┬─JSONAllPaths(json1)─┬─JSONAllPaths(json2)─┐
│ {"a":{"b":"42"}} │ {"a.b":"42"} │ ['a.b'] │ ['a%2Eb'] │
└──────────────────┴──────────────┴─────────────────────┴─────────────────────┘
SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON AS json, JSONAllPaths(json);
┌─json──────────────────────────────────┬─JSONAllPaths(json)─┐
│ {"a.b":"42","a":{"b":"Hello World!"}} │ ['a%2Eb','a.b'] │
└───────────────────────────────────────┴────────────────────┘
要将键中包含已转义点号的部分作为子列读取时，必须在子列名称中同样使用该转义点号：
SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON AS json, json.`a%2Eb`, json.a.b;
┌─json──────────────────────────────────┬─json.a%2Eb─┬─json.a.b─────┐
│ {"a.b":"42","a":{"b":"Hello World!"}} │ 42 │ Hello World! │
└───────────────────────────────────────┴────────────┴──────────────┘
注意：由于标识符解析器和分析器的限制，子列 json.`a.b` 等价于子列 json.a.b，因此无法读取带有转义点号的路径：
SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON AS json, json.`a%2Eb`, json.`a.b`, json.a.b;
┌─json──────────────────────────────────┬─json.a%2Eb─┬─json.a.b─────┬─json.a.b─────┐
│ {"a.b":"42","a":{"b":"Hello World!"}} │ 42 │ Hello World! │ Hello World! │
└───────────────────────────────────────┴────────────┴──────────────┴──────────────┘
此外，如果你想为键名中包含点号的 JSON 路径指定提示（或在 SKIP/SKIP REGEX 部分中使用它），则必须在提示中对点号进行转义：
SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON(`a%2Eb` UInt8) as json, json.`a%2Eb`, toTypeName(json.`a%2Eb`);
┌─json────────────────────────────────┬─json.a%2Eb─┬─toTypeName(json.a%2Eb)─┐
│ {"a.b":42,"a":{"b":"Hello World!"}} │ 42 │ UInt8 │
└─────────────────────────────────────┴────────────┴────────────────────────┘
SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON(SKIP `a%2Eb`) as json, json.`a%2Eb`;
┌─json───────────────────────┬─json.a%2Eb─┐
│ {"a":{"b":"Hello World!"}} │ ᴺᵁᴸᴸ │
└────────────────────────────┴────────────┘
从数据中读取 JSON 类型
所有文本格式
(JSONEachRow,
TSV,
CSV,
CustomSeparated,
Values 等) 都支持读取 JSON 类型的数据。
示例：
SELECT json FROM format(JSONEachRow, 'json JSON(a.b.c UInt32, SKIP a.b.d, SKIP d.e, SKIP REGEXP \'b.*\')', '
{"json" : {"a" : {"b" : {"c" : 1, "d" : [0, 1]}}, "b" : "2020-01-01", "c" : 42, "d" : {"e" : {"f" : ["s1", "s2"]}, "i" : [1, 2, 3]}}}
{"json" : {"a" : {"b" : {"c" : 2, "d" : [2, 3]}}, "b" : [1, 2, 3], "c" : null, "d" : {"e" : {"g" : 43}, "i" : [4, 5, 6]}}}
{"json" : {"a" : {"b" : {"c" : 3, "d" : [4, 5]}}, "b" : {"c" : 10}, "e" : "Hello, World!"}}
{"json" : {"a" : {"b" : {"c" : 4, "d" : [6, 7]}}, "c" : 43}}
{"json" : {"a" : {"b" : {"c" : 5, "d" : [8, 9]}}, "b" : {"c" : 11, "j" : [1, 2, 3]}, "d" : {"e" : {"f" : ["s3", "s4"], "g" : 44}, "h" : "2020-02-02 10:00:00"}}}
')
┌─json──────────────────────────────────────────────────────────┐
│ {"a":{"b":{"c":1}},"c":"42","d":{"i":["1","2","3"]}} │
│ {"a":{"b":{"c":2}},"d":{"i":["4","5","6"]}} │
│ {"a":{"b":{"c":3}},"e":"Hello, World!"} │
│ {"a":{"b":{"c":4}},"c":"43"} │
│ {"a":{"b":{"c":5}},"d":{"h":"2020-02-02 10:00:00.000000000"}} │
└───────────────────────────────────────────────────────────────┘
对于 CSV、TSV 等文本格式，JSON 将从包含 JSON 对象的字符串中进行解析：
SELECT json FROM format(TSV, 'json JSON(a.b.c UInt32, SKIP a.b.d, SKIP REGEXP \'b.*\')',
'{"a" : {"b" : {"c" : 1, "d" : [0, 1]}}, "b" : "2020-01-01", "c" : 42, "d" : {"e" : {"f" : ["s1", "s2"]}, "i" : [1, 2, 3]}}
{"a" : {"b" : {"c" : 2, "d" : [2, 3]}}, "b" : [1, 2, 3], "c" : null, "d" : {"e" : {"g" : 43}, "i" : [4, 5, 6]}}
{"a" : {"b" : {"c" : 3, "d" : [4, 5]}}, "b" : {"c" : 10}, "e" : "Hello, World!"}
{"a" : {"b" : {"c" : 4, "d" : [6, 7]}}, "c" : 43}
{"a" : {"b" : {"c" : 5, "d" : [8, 9]}}, "b" : {"c" : 11, "j" : [1, 2, 3]}, "d" : {"e" : {"f" : ["s3", "s4"], "g" : 44}, "h" : "2020-02-02 10:00:00"}}')
┌─json──────────────────────────────────────────────────────────┐
│ {"a":{"b":{"c":1}},"c":"42","d":{"i":["1","2","3"]}} │
│ {"a":{"b":{"c":2}},"d":{"i":["4","5","6"]}} │
│ {"a":{"b":{"c":3}},"e":"Hello, World!"} │
│ {"a":{"b":{"c":4}},"c":"43"} │
│ {"a":{"b":{"c":5}},"d":{"h":"2020-02-02 10:00:00.000000000"}} │
└───────────────────────────────────────────────────────────────┘
达到 JSON 中动态路径数量的上限
JSON 数据类型在内部只能将有限数量的路径存储为单独的子列。
默认情况下，此上限为 1024，但你可以在类型声明中通过参数 max_dynamic_paths 来修改。
当达到上限时，插入到 JSON 列中的所有新路径都会存储在一个共享数据结构中。
这些路径仍然可以作为子列读取，
但效率可能会较低 (参见关于共享数据的章节) 。
设置这个上限是为了避免出现数量巨大的不同子列，从而导致表无法正常使用。
下面我们来看看在几种不同场景下达到该上限时会发生什么。
在数据解析过程中达到上限
在从数据中解析 JSON 对象时，当当前数据块的路径数量达到上限后，
所有新路径都会存储在一个共享数据结构中。我们可以使用以下两个自省函数 JSONDynamicPaths、JSONSharedDataPaths：
SELECT json, JSONDynamicPaths(json), JSONSharedDataPaths(json) FROM format(JSONEachRow, 'json JSON(max_dynamic_paths=3)', '
{"json" : {"a" : {"b" : 42}, "c" : [1, 2, 3]}}
{"json" : {"a" : {"b" : 43}, "d" : "2020-01-01"}}
{"json" : {"a" : {"b" : 44}, "c" : [4, 5, 6]}}
{"json" : {"a" : {"b" : 43}, "d" : "2020-01-02", "e" : "Hello", "f" : {"g" : 42.42}}}
{"json" : {"a" : {"b" : 43}, "c" : [7, 8, 9], "f" : {"g" : 43.43}, "h" : "World"}}
')
┌─json───────────────────────────────────────────────────────────┬─JSONDynamicPaths(json)─┬─JSONSharedDataPaths(json)─┐
│ {"a":{"b":"42"},"c":["1","2","3"]} │ ['a.b','c','d'] │ [] │
│ {"a":{"b":"43"},"d":"2020-01-01"} │ ['a.b','c','d'] │ [] │
│ {"a":{"b":"44"},"c":["4","5","6"]} │ ['a.b','c','d'] │ [] │
│ {"a":{"b":"43"},"d":"2020-01-02","e":"Hello","f":{"g":42.42}} │ ['a.b','c','d'] │ ['e','f.g'] │
│ {"a":{"b":"43"},"c":["7","8","9"],"f":{"g":43.43},"h":"World"} │ ['a.b','c','d'] │ ['f.g','h'] │
└────────────────────────────────────────────────────────────────┴────────────────────────┴───────────────────────────┘
正如我们所见，在插入路径 e 和 f.g 之后，已经达到了上限，
它们被插入到一个共享数据结构中。
在 MergeTree 表引擎中合并数据片段时
在 MergeTree 表中合并多个数据片段时，结果数据片段中的 JSON 列可能会达到动态路径的限制，
从而无法将所有源数据片段中的路径都作为子列进行存储。
在这种情况下，ClickHouse 会选择哪些路径在合并后保留为子列，哪些路径存储在共享数据结构中。
在大多数情况下，ClickHouse 会尽量保留包含最多非空值的路径，并将最少见的路径移到共享数据结构中。不过，这也取决于具体实现。
我们来看一个这样的合并示例。
首先，让我们创建一个包含 JSON 列的表，将动态路径的限制设置为 3，然后插入具有 5 个不同路径的值：
CREATE TABLE test (id UInt64, json JSON(max_dynamic_paths=3)) ENGINE=MergeTree ORDER BY id;
SYSTEM STOP MERGES test;
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as a) FROM numbers(5);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as b) FROM numbers(4);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as c) FROM numbers(3);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as d) FROM numbers(2);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as e) FROM numbers(1);
每次插入都会创建一个单独的数据片段，其中 JSON 列只包含一个路径：
SELECT
count(),
groupArrayArrayDistinct(JSONDynamicPaths(json)) AS dynamic_paths,
groupArrayArrayDistinct(JSONSharedDataPaths(json)) AS shared_data_paths,
_part
FROM test
GROUP BY _part
ORDER BY _part ASC
┌─count()─┬─dynamic_paths─┬─shared_data_paths─┬─_part─────┐
│ 5 │ ['a'] │ [] │ all_1_1_0 │
│ 4 │ ['b'] │ [] │ all_2_2_0 │
│ 3 │ ['c'] │ [] │ all_3_3_0 │
│ 2 │ ['d'] │ [] │ all_4_4_0 │
│ 1 │ ['e'] │ [] │ all_5_5_0 │
└─────────┴───────────────┴───────────────────┴───────────┘
现在，让我们把所有部分合并起来，看看会发生什么：
SELECT
count(),
groupArrayArrayDistinct(JSONDynamicPaths(json)) AS dynamic_paths,
groupArrayArrayDistinct(JSONSharedDataPaths(json)) AS shared_data_paths,
_part
FROM test
GROUP BY _part
ORDER BY _part ASC
┌─count()─┬─dynamic_paths─┬─shared_data_paths─┬─_part─────┐
│ 15 │ ['a','b','c'] │ ['d','e'] │ all_1_5_2 │
└─────────┴───────────────┴───────────────────┴───────────┘
正如我们所见，ClickHouse 保留了最常见的路径 a、b 和 c，并将路径 d 和 e 放入了一个共享数据结构中。
共享数据结构
如前一节所述，当达到 max_dynamic_paths 限制时，所有新的路径都会存储在一个共享数据结构中。
本节将详细介绍该共享数据结构的实现方式，以及如何从中读取路径子列。
有关用于检查 JSON 列内容的函数的详细信息，请参见“自省函数”一节。
内存中的共享数据结构
在内存中，共享数据结构只是一个类型为 Map(String, String) 的子列，用于存储从扁平化 JSON 路径到二进制编码值的映射。
要从中提取路径子列，只需遍历该 Map 列中的所有行，并尝试查找请求的路径及其对应的值。
MergeTree 部件中的共享数据结构
在 MergeTree 表中，我们将数据存储在数据部件中，这些部件会将所有内容存储在磁盘上 (本地或远程) 。而磁盘上的数据存储方式可能与内存中的不同。
目前，在 MergeTree 数据部件中存在 3 种不同的共享数据结构序列化方式：map、map_with_buckets
和 advanced。
序列化版本由 MergeTree 设置
object_shared_data_serialization_version
和 object_shared_data_serialization_version_for_zero_level_parts 控制
(零级部件是在向表中插入数据时创建的部件，在合并过程中生成的部件级别更高) 。
注意：仅当使用 v3 对象序列化版本 时，才支持更改共享数据结构的序列化方式。
Map
在 map 序列化版本中，共享数据会序列化为一个类型为 Map(String, String) 的单列，其存储方式与内存中相同。
要从这种序列化方式中读取路径子列，ClickHouse 会读取整个 Map 列，并在内存中提取请求的路径。
这种序列化方式在写入数据和读取整个 JSON 列时效率较高，但在读取路径子列时效率不高。
带桶的 Map
在 map_with_buckets 序列化版本中，共享数据会序列化为 N 列 (“桶”) ，每列的类型为 Map(String, String)。
每个桶仅包含路径的一个子集。要从这种序列化方式中读取路径子列，ClickHouse
会从单个桶中读取整个 Map 列，并在内存中提取请求的路径。
这种序列化方式在写入数据和读取整个 JSON 列时效率较低，但在读取路径子列时效率更高，
因为它只会从所需的桶中读取数据。
桶的数量 N 由 MergeTree 设置 object_shared_data_buckets_for_compact_part (默认值为 8)
和 object_shared_data_buckets_for_wide_part (默认值为 32) 控制。
这两个设置的最大允许值均为 256。
Advanced
在 advanced 序列化版本中，共享数据会序列化为一种专门的数据结构，通过存储一些额外信息来最大化路径子列的读取性能，从而只读取请求路径的数据。
这种序列化方式同样支持桶，因此每个桶也只包含路径的一个子集。
这种序列化方式在写入数据时效率较低 (因此不建议将其用于零级 parts) ，读取整个 JSON 列时的效率相比 map 序列化略低，但在读取路径子列时非常高效。
注意：由于在数据结构中存储了额外信息，与 map 和 map_with_buckets 序列化方式相比，这种序列化在磁盘上的存储空间占用更高。
如需更详细地了解新的共享数据序列化方式及其实现细节，请参阅这篇博客文章。
控制 MergeTree 分区片段中 JSON 内部动态路径的数量
在 JSON 中设置动态路径数量上限的主要方式，是在 JSON 类型声明中使用 max_dynamic_paths 参数。
但是，修改已有列的 max_dynamic_paths 需要执行 ALTER TABLE <table> MODIFY COLUMN <column> JSON(max_dynamic_paths=K)，这会启动一个后台 mutation，用于重写所有已有的分区片段。
此类 mutation 可能非常耗费资源，并在完成之前影响服务器性能。为避免这种情况，可以使用以下 3 个设置项，仅针对新的数据分区片段调整 MergeTree 表中动态路径的上限：
merge_max_dynamic_subcolumns_in_wide_part —— 一个 MergeTree 设置，用于在合并为 Wide 数据分区片段时，限制每个 JSON 列的动态子列数量。merge_max_dynamic_subcolumns_in_compact_part —— 一个 MergeTree 设置，用于在合并为 Compact 数据分区片段时，限制每个 JSON 列的动态子列数量。max_dynamic_subcolumns_in_json_type_parsing —— 一个会话级设置，用于在将 JSON 数据解析到 JSON 列时，限制每个 JSON 列的动态子列数量。
注意：动态路径的上限不能超过 max_dynamic_paths 参数中指定的值，即使上述设置项的取值更高也不会生效。
自省函数
有多个函数可以用于查看 JSON 列的内容：
示例
让我们来分析一下 GH Archive 数据集中日期为 2020-01-01 的内容：
SELECT arrayJoin(distinctJSONPaths(json))
FROM s3('s3://clickhouse-public-datasets/gharchive/original/2020-01-01-*.json.gz', JSONAsObject)
┌─arrayJoin(distinctJSONPaths(json))─────────────────────────┐
│ actor.avatar_url │
│ actor.display_login │
│ actor.gravatar_id │
│ actor.id │
│ actor.login │
│ actor.url │
│ created_at │
│ id │
│ org.avatar_url │
│ org.gravatar_id │
│ org.id │
│ org.login │
│ org.url │
│ payload.action │
│ payload.before │
│ payload.comment._links.html.href │
│ payload.comment._links.pull_request.href │
│ payload.comment._links.self.href │
│ payload.comment.author_association │
│ payload.comment.body │
│ payload.comment.commit_id │
│ payload.comment.created_at │
│ payload.comment.diff_hunk │
│ payload.comment.html_url │
│ payload.comment.id │
│ payload.comment.in_reply_to_id │
│ payload.comment.issue_url │
│ payload.comment.line │
│ payload.comment.node_id │
│ payload.comment.original_commit_id │
│ payload.comment.original_position │
│ payload.comment.path │
│ payload.comment.position │
│ payload.comment.pull_request_review_id │
...
│ payload.release.node_id │
│ payload.release.prerelease │
│ payload.release.published_at │
│ payload.release.tag_name │
│ payload.release.tarball_url │
│ payload.release.target_commitish │
│ payload.release.upload_url │
│ payload.release.url │
│ payload.release.zipball_url │
│ payload.size │
│ public │
│ repo.id │
│ repo.name │
│ repo.url │
│ type │
└─arrayJoin(distinctJSONPaths(json))─────────────────────────┘
SELECT arrayJoin(distinctJSONPathsAndTypes(json))
FROM s3('s3://clickhouse-public-datasets/gharchive/original/2020-01-01-*.json.gz', JSONAsObject)
SETTINGS date_time_input_format = 'best_effort'
┌─arrayJoin(distinctJSONPathsAndTypes(json))──────────────────┐
│ ('actor.avatar_url',['String']) │
│ ('actor.display_login',['String']) │
│ ('actor.gravatar_id',['String']) │
│ ('actor.id',['Int64']) │
│ ('actor.login',['String']) │
│ ('actor.url',['String']) │
│ ('created_at',['DateTime']) │
│ ('id',['String']) │
│ ('org.avatar_url',['String']) │
│ ('org.gravatar_id',['String']) │
│ ('org.id',['Int64']) │
│ ('org.login',['String']) │
│ ('org.url',['String']) │
│ ('payload.action',['String']) │
│ ('payload.before',['String']) │
│ ('payload.comment._links.html.href',['String']) │
│ ('payload.comment._links.pull_request.href',['String']) │
│ ('payload.comment._links.self.href',['String']) │
│ ('payload.comment.author_association',['String']) │
│ ('payload.comment.body',['String']) │
│ ('payload.comment.commit_id',['String']) │
│ ('payload.comment.created_at',['DateTime']) │
│ ('payload.comment.diff_hunk',['String']) │
│ ('payload.comment.html_url',['String']) │
│ ('payload.comment.id',['Int64']) │
│ ('payload.comment.in_reply_to_id',['Int64']) │
│ ('payload.comment.issue_url',['String']) │
│ ('payload.comment.line',['Int64']) │
│ ('payload.comment.node_id',['String']) │
│ ('payload.comment.original_commit_id',['String']) │
│ ('payload.comment.original_position',['Int64']) │
│ ('payload.comment.path',['String']) │
│ ('payload.comment.position',['Int64']) │
│ ('payload.comment.pull_request_review_id',['Int64']) │
...
│ ('payload.release.node_id',['String']) │
│ ('payload.release.prerelease',['Bool']) │
│ ('payload.release.published_at',['DateTime']) │
│ ('payload.release.tag_name',['String']) │
│ ('payload.release.tarball_url',['String']) │
│ ('payload.release.target_commitish',['String']) │
│ ('payload.release.upload_url',['String']) │
│ ('payload.release.url',['String']) │
│ ('payload.release.zipball_url',['String']) │
│ ('payload.size',['Int64']) │
│ ('public',['Bool']) │
│ ('repo.id',['Int64']) │
│ ('repo.name',['String']) │
│ ('repo.url',['String']) │
│ ('type',['String']) │
└─arrayJoin(distinctJSONPathsAndTypes(json))──────────────────┘
使用 ALTER MODIFY COLUMN 修改为 JSON 类型
可以对现有表执行修改操作，将列的类型更改为新的 JSON 类型。目前仅支持对 String 类型列执行 ALTER 操作。
示例
CREATE TABLE test (json String) ENGINE=MergeTree ORDER BY tuple();
INSERT INTO test VALUES ('{"a" : 42}'), ('{"a" : 43, "b" : "Hello"}'), ('{"a" : 44, "b" : [1, 2, 3]}'), ('{"c" : "2020-01-01"}');
ALTER TABLE test MODIFY COLUMN json JSON;
SELECT json, json.a, json.b, json.c FROM test;
┌─json─────────────────────────┬─json.a─┬─json.b──┬─json.c─────┐
│ {"a":"42"} │ 42 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
│ {"a":"43","b":"Hello"} │ 43 │ Hello │ ᴺᵁᴸᴸ │
│ {"a":"44","b":["1","2","3"]} │ 44 │ [1,2,3] │ ᴺᵁᴸᴸ │
│ {"c":"2020-01-01"} │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 2020-01-01 │
└──────────────────────────────┴────────┴─────────┴────────────┘
惰性类型提示 (实验性)
注意
此功能为实验性功能，需要启用设置 allow_experimental_json_lazy_type_hints。
当你使用 ALTER TABLE ... MODIFY COLUMN 在 JSON 列上添加或修改类型提示时，ClickHouse 通常会重写所有分区片段以物化新的类型提示。对于拥有海量历史数据 (数百 TB) 的表而言，这样的开销可能极其巨大。
惰性类型提示 允许以仅修改元数据的方式添加类型提示，而无需重写已有数据：
- 旧分区片段：在查询时，通过将
Dynamic 转换为目标类型来应用类型提示
- 新分区片段：在执行
INSERT 操作时物化类型提示
- 合并操作：在分区片段合并时物化类型提示
这意味着你可以立即添加类型提示，而数据会在正常的后台合并过程中逐步完成转换。
启用延迟类型提示
SET allow_experimental_json_lazy_type_hints = 1;
-- Create a table and insert data
CREATE TABLE test_lazy (json JSON) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO test_lazy VALUES ('{"user_id": "123", "score": "95.5"}');
-- Enable experimental setting
SET allow_experimental_json_lazy_type_hints = 1;
-- Add type hints - this completes instantly without mutation
ALTER TABLE test_lazy MODIFY COLUMN json JSON(user_id UInt64, score Float64);
-- Query the data - type hints are applied at read time
SELECT json.user_id, toTypeName(json.user_id), json.score, toTypeName(json.score) FROM test_lazy;
┌─json.user_id─┬─toTypeName(json.user_id)─┬─json.score─┬─toTypeName(json.score)─┐
│ 123 │ UInt64 │ 95.5 │ Float64 │
└──────────────┴──────────────────────────┴────────────┴────────────────────────┘
验证未发生变更
可以通过检查 system.mutations 表来确认 ALTER 是否在未触发变更的情况下完成：
SELECT * FROM system.mutations WHERE table = 'test_lazy' AND NOT is_done;
启用惰性类型提示后，此查询不会返回任何行，从而确认该操作只是元数据操作。
物化类型提示
要在现有数据中物化类型提示，可以：
- 等待后台合并：当分区片段被合并时，ClickHouse 会自动物化类型提示
- 强制合并：使用
OPTIMIZE TABLE test_lazy FINAL 立即合并所有分区片段
- 重写分区片段：使用
ALTER TABLE test_lazy REWRITE PARTS 按新的元数据重写分区片段
- 此功能为实验性功能，将来版本中可能发生变化
- 与预先物化的类型相比，在查询时进行类型转换会带来显著的性能开销，特别是对于大型 JSON 对象
- 此功能仅在修改
typed_paths (类型提示) 时适用；其他 JSON 参数如 max_dynamic_paths、SKIP 或 SKIP REGEXP 仍然需要通过 mutation 来修改
JSON 类型值的比较
JSON 对象的比较规则与 Map 类型类似。
例如：
CREATE TABLE test (json1 JSON, json2 JSON) ENGINE=Memory;
INSERT INTO test FORMAT JSONEachRow
{"json1" : {}, "json2" : {}}
{"json1" : {"a" : 42}, "json2" : {}}
{"json1" : {"a" : 42}, "json2" : {"a" : 41}}
{"json1" : {"a" : 42}, "json2" : {"a" : 42}}
{"json1" : {"a" : 42}, "json2" : {"a" : [1, 2, 3]}}
{"json1" : {"a" : 42}, "json2" : {"a" : "Hello"}}
{"json1" : {"a" : 42}, "json2" : {"b" : 42}}
{"json1" : {"a" : 42}, "json2" : {"a" : 42, "b" : 42}}
{"json1" : {"a" : 42}, "json2" : {"a" : 41, "b" : 42}}
SELECT json1, json2, json1 < json2, json1 = json2, json1 > json2 FROM test;
┌─json1──────┬─json2───────────────┬─less(json1, json2)─┬─equals(json1, json2)─┬─greater(json1, json2)─┐
│ {} │ {} │ 0 │ 1 │ 0 │
│ {"a":"42"} │ {} │ 0 │ 0 │ 1 │
│ {"a":"42"} │ {"a":"41"} │ 0 │ 0 │ 1 │
│ {"a":"42"} │ {"a":"42"} │ 0 │ 1 │ 0 │
│ {"a":"42"} │ {"a":["1","2","3"]} │ 0 │ 0 │ 1 │
│ {"a":"42"} │ {"a":"Hello"} │ 1 │ 0 │ 0 │
│ {"a":"42"} │ {"b":"42"} │ 1 │ 0 │ 0 │
│ {"a":"42"} │ {"a":"42","b":"42"} │ 1 │ 0 │ 0 │
│ {"a":"42"} │ {"a":"41","b":"42"} │ 0 │ 0 │ 1 │
└────────────┴─────────────────────┴────────────────────┴──────────────────────┴───────────────────────┘
注意： 当两个路径中包含不同数据类型的值时，将根据 Variant 数据类型的比较规则进行比较。
JSON 的数据跳过索引
数据跳过索引 可通过三种方式用于 JSON 列：
- 特定子列上的索引 — 在已知的 JSON 路径上创建标准跳过索引，与普通列的做法相同。这会为该路径上的值建立索引。
- 基于路径的
JSONAllPaths 索引 — 为每个 粒度 中存在的路径集合建立索引，从而跳过不可能包含所查询路径的 粒度。
- 基于值的
JSONAllValues 索引 — 使用文本索引为所有 JSON 路径上的所有值建立索引，从而只需一个索引即可加速对任意 JSON 子列的全文检索。
特定子列上的索引
您可以像对普通列那样，在任何 JSON 子列上创建跳过索引。
任何受支持的索引类型都可以使用 (minmax、set、bloom_filter、tokenbf_v1、ngrambf_v1 等) 。
在索引表达式中引用 JSON 子列有两种方式：
- 在 JSON 类型提示中声明的类型路径——直接按名称访问：
json.a。
- 带显式类型转换的动态路径——使用
:: 转换语法：json.b::String。
您也可以使用组合多个子列的表达式，例如 json.a || json.b::String。
CREATE TABLE sensor_data
(
data JSON(sensor_id UInt32),
INDEX idx_sensor data.sensor_id TYPE minmax GRANULARITY 1,
INDEX idx_location data.location::String TYPE bloom_filter GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 1;
INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', number, 'location', 'room_' || toString(number))) FROM numbers(4);
INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', number, 'location', 'room_' || toString(number))) FROM numbers(4, 4);
类型化子列 data.sensor_id 上的 minmax 索引会将扫描范围缩小到匹配的粒度：
EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.sensor_id < 2;
...
Indexes:
Skip
Name: idx_sensor
Description: minmax GRANULARITY 1
Parts: 1/2
Granules: 2/8
对经类型转换的子列 data.location::String 使用 bloom_filter 索引也同样有效：
EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.location::String = 'room_5';
...
Indexes:
Skip
Name: idx_location
Description: bloom_filter GRANULARITY 1
Parts: 1/2
Granules: 1/8
使用 JSONAllPaths 的路径索引
也可以使用 JSONAllPaths 函数，在 JSON 列上创建数据跳过索引。
其工作方式类似于通过 mapKeys 在 Map 列上创建跳过索引：索引会存储每个粒度中存在的 JSON 路径集合，并据此跳过不可能包含所查询路径的粒度。
支持的索引类型
JSONAllPaths 可与以下跳过索引类型配合使用：
CREATE TABLE events
(
data JSON,
INDEX idx JSONAllPaths(data) TYPE bloom_filter GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY tuple();
INSERT INTO events VALUES ('{"user": {"name": "Alice"}, "action": "login"}');
INSERT INTO events VALUES ('{"metric": {"cpu": 0.95}, "host": "srv1"}');
您可以使用 EXPLAIN indexes = 1 来确认是否使用了跳过索引。当某个路径仅存在于一个分片中时，索引会跳过另一个分片：
EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name = 'Alice';
...
Indexes:
Skip
Name: idx
Description: bloom_filter GRANULARITY 1
Parts: 1/2
Granules: 1/2
如果某个路径在所有 part 中都不存在，则会跳过所有 parts 和 粒度：
EXPLAIN indexes = 1 SELECT * FROM events WHERE data.nonexistent = 1;
...
Indexes:
Skip
Name: idx
Description: bloom_filter GRANULARITY 1
Parts: 0/2
Granules: 0/2
IS NOT NULL 也会使用索引——它会跳过路径不存在的粒度 (因为此时该值会是 NULL) ：
EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name IS NOT NULL;
...
Indexes:
Skip
Name: idx
Description: bloom_filter GRANULARITY 1
Parts: 1/2
Granules: 1/2
工作原理
JSONAllPaths(json_column) 表达式会生成一个包含 JSON 值中所有现有路径的 Array(String)。
数据跳过索引会将这些路径字符串存储在其数据结构中 (布隆过滤器或转置索引) 。
当查询按 json.some.path 进行筛选时，索引会检查每个粒度的索引中是否存在字符串 "some.path"，并跳过不包含该字符串的粒度。
缺失路径时的安全性
当某个 JSON 路径在一个 粒度 中不存在时，子列的求值结果为：
- 对于
Dynamic 类型 (例如 json.path) 和 Nullable 类型的子列 (例如 json.path.:Int64) ，结果为 NULL —— 与 NULL 的比较始终返回 false，因此可以安全跳过。
- 对于非
Nullable 的 CAST 表达式，结果为该类型的默认值 (例如路径缺失时，json.path::Int64 会得到 0) —— 仅当比较值与默认值不同时，跳过才是安全的。索引会自动处理这种差异。
使用 JSONAllValues 进行全文检索
文本索引可通过 JSONAllValues 函数加速对 JSON 列的全文检索。
JSONAllValues 会将 JSON 列中的所有值以 Array(String) 形式返回，这些值可以建立文本索引。
在 JSONAllValues(json_column) 上创建单个索引即可覆盖所有 JSON 路径，从而无需为每个路径分别创建索引，也能对任意子列执行全文检索。
有关详细信息和示例，请参阅文本索引文档中的使用 JSONAllValues 的基于值的索引。
更高效使用 JSON 类型的技巧
在创建 JSON 列并向其中加载数据之前，请考虑以下几点建议：
- 先分析你的数据，并尽可能为更多路径提供带类型的路径提示 (path hint) 。这将显著提升存储和读取效率。
- 考虑在实际使用中会需要哪些路径，以及哪些路径基本不会被使用。对于不需要的路径，在
SKIP 部分中进行指定；如有必要，再在 SKIP REGEXP 部分中指定。这将改善存储效果。
- 不要将
max_dynamic_paths 参数设置得过高，否则可能会降低存储和读取效率。
虽然具体数值高度依赖于内存、CPU 等系统参数，但一个经验法则是：对于本地文件系统存储，不要将 max_dynamic_paths 设置为大于 10 000；对于远程文件系统存储，不要设置为大于 1024。
延伸阅读
