ClickHouse 25.3 json列类型使用示例

JSON已经成为现代数据系统中处理半结构化和非结构化数据的通用语言。无论是在日志记录和可观察性场景、实时数据流、移动应用存储还是机器学习管道中，JSON的灵活结构使其成为跨分布式系统捕获和传输数据的首选格式。

测试环境

docker镜像
Official Images clickhouse
docker pull clickhouse:25.4.5.24

docker run -d --rm --name ch25 --ulimit nofile=262144:262144 clickhouse:25.4.5.24


docker run -d -p 18123:8123 -p19000:9000 -e CLICKHOUSE_PASSWORD=thinker --name ch25 --ulimit nofile=262144:262144 clickhouse:25.4.5.24

json列类型

ClickHouse 从24.8版本提供了专为半结构化和动态数据设计的原生JSON列类型，需要注意的是它是列类型，不是数据格式， 只有当数据结构是动态的，而不是仅仅存储JSON时，用户才应该使用JSON类型

在ClickHouse中，开源JSON数据类型在25.3版本中被标记为生产就绪。在以前的版本中，不建议在生产中使用这种类型。

在使用25.3以后的版本就可以使用JSON列类型了

何时使用json列类型

• 有不可预测的键，可以随着时间的推移而改变。
• 包含不同类型的值（例如，某个路径的值可能是字符串，也可能是数字）
• 在严格类型不可行的情况下需要模式灵活性。

如果您的数据结构是已知且一致的，则很少需要JSON类型，即使您的数据是JSON格式，数据特点如下：

• 具有已知键的扁平结构：使用标准列类型，例如字符串。
• 可预测嵌套：对这些结构使用元组、数组或嵌套类型。
• 具有不同类型的可预测结构：考虑动态或变体类型。
  针对这种情况不要使用JSON列类型，但可以混合使用各种方法，如对可预测的顶级字段使用静态列，对有效负载的动态部分使用单个JSON列。

如何定义json列类型

要声明JSON类型的列，可以使用以下语法：

<column_name> JSON
(
    max_dynamic_paths=N, 
    max_dynamic_types=M, 
    some.path TypeName, 
    SKIP path.to.skip, 
    SKIP REGEXP 'paths_regexp'
)

• max_dynamic_paths 默认为1024，json paths最大的数量，如果超过这个限制，所有其他路径将一起存储在一个结构中。
• max_dynamic_types 默认为32
• some.path TypeName根据需要指定子列的类型
• SKIP path.to.skip 跳过不需要的path
• SKIP REGEXP ‘path_regexp’ 设置路径表达式跳过不需要的path

函数

• FORMAT PrettyJSONEachRow 指定select数据的输出格式
• FORMAT JSONEachRow 指定insert的数据格式
• FORMAT JSONAsObject 当表中只有一个JSON字段时，指定insert的数据格式
• JSONAllPathsWithTypes(doc) 输出json字段的所有path及其类型
• Using CAST with ::JSON
  • cast string to json: '{"a" : {"b" : 42},"c" : [1, 2, 3], "d" : "Hello, World!"}'::JSON AS json;
  • cast tuple to json:
  • Object(‘json’)toJSON`

-- 建表示例
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;


-- CAST from `String` to `JSON`
SELECT '{"a" : {"b" : 42},"c" : [1, 2, 3], "d" : "Hello, World!"}'::JSON AS json;

-- CAST from `Tuple` to `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;

-- CAST from `Map` to `JSON`
--类型都使用string
SET enable_variant_type=1,