如何提取ClickHouse中JSON类型列的所有唯一键名

针对单层无嵌套结构的JSON类型列，直接使用内置JSON键提取函数搭配数组拆分行、去重即可实现需求，具体方案如下：

核心实现SQL #

假设你的业务表名为target_table，存储JSON的字段为properties，执行以下查询即可拿到所有不重复的键名：

SELECT DISTINCT json_key
FROM target_table
ARRAY JOIN JSONExtractKeys(properties) AS json_key

逻辑说明 #

• JSONExtractKeys(properties)：该函数会提取每行JSON对象的所有顶层键名，返回Array(String)类型的键名数组，完全适配无嵌套、无数组的单层JSON对象场景
• ARRAY JOIN：将每行返回的键名数组拆分为独立的多行数据，每个键名单独作为一行
• DISTINCT：对拆出的所有键名做去重，最终得到全表该JSON列下存在的所有唯一键名

效果验证 #

插入举例的两条测试数据：

INSERT INTO target_table(properties) VALUES
('{"age": "25"}'),
('{"genre": "pop"}');

执行上述查询后，返回结果为：

┌─json_key─┐
│ age      │
│ genre    │
└──────────┘

完全匹配预期结果。

注意事项 #

• 该方法仅提取JSON的顶层键，不会递归解析嵌套对象、嵌套数组内的键，刚好匹配单层JSON结构的需求
• 空JSON对象{}传入JSONExtractKeys会返回空数组，经ARRAY JOIN处理后不会生成冗余行，不影响最终结果
• 如果你使用的是ClickHouse 22.3+版本的原生JSON类型（非字符串存储的JSON），可以将JSONExtractKeys替换为JSONKeys函数，查询性能会更优

内容的提问来源于stack exchange，提问作者Luis Novo