ClickHouse 通过 *ARRAY JOIN* 结合 Map 类型的内置函数取数值
在 ClickHouse 中,可以通过 ARRAY JOIN 结合 Map 类型的内置函数,将 Map 字段的键值对展开为多行数据。以下是具体操作方法和示例:
一、使用 mapKeys
和 mapValues
展开 Map
1. 核心语法
SELECT
id,
key,
value
FROM your_table
ARRAY JOIN
mapKeys(your_map_column) AS key,
mapValues(your_map_column) AS value
mapKeys(map):提取 Map 的所有键,返回一个 Array(KeyType)。mapValues(map):提取 Map 的所有值,返回一个 Array(ValueType)。ARRAY JOIN:将两个数组按顺序展开为多行,键和值一一对应。
2. 示例
假设表结构如下:
CREATE TABLE user_tags (
user_id Int32,
tags Map(String, String)
) ENGINE = MergeTree()
ORDER BY user_id;
-- 插入数据
INSERT INTO user_tags VALUES
(1, {'gender': 'male', 'country': 'US'}),
(2, {'age': '25', 'os': 'iOS'});
-- 展开查询:
SELECT
user_id,
key,
value
FROM user_tags
ARRAY JOIN
mapKeys(tags) AS key,
mapValues(tags) AS value
输出:
┌─user_id─┬─key─────┬─value─┐
│ 1 │ gender │ male │
│ 1 │ country │ US │
│ 2 │ age │ 25 │
│ 2 │ os │ iOS │
└─────────┴─────────┴───────┘
二、直接使用 .Keys
和 .Values
语法(ClickHouse 22.6+ 支持)
从 ClickHouse 22.6 开始,可以直接通过 Map.Keys
和 Map.Values
访问键值数组:
SELECT
user_id,
key,
value
FROM user_tags
ARRAY JOIN
tags.Keys AS key,
tags.Values AS value
三、使用 mapEntries
展开为键值对元组
1. 核心语法
SELECT
id,
entry.1 AS key, -- 元组第一个元素为键
entry.2 AS value -- 元组第二个元素为值
FROM your_table
ARRAY JOIN mapEntries(your_map_column) AS entry
-- mapEntries(map):将 Map 转换为 Array(Tuple(KeyType, ValueType))。
2. 示例
SELECT
user_id,
entry.1 AS key,
entry.2 AS value
FROM user_tags
ARRAY JOIN mapEntries(tags) AS entry
四、过滤特定键值对
在展开后,可通过 WHERE
子句筛选特定键或值:
SELECT
user_id,
key,
value
FROM user_tags
ARRAY JOIN mapKeys(tags) AS key, mapValues(tags) AS value
WHERE key = 'os'
输出:
┌─user_id─┬─key─┬─value─┐
│ 2 │ os │ iOS │
└─────────┴─────┴───────┘
五、性能优化建议
-
避免展开大 Map
Map 展开后行数会爆炸式增长(如 10,000 个键值对 → 10,000 行),建议在 WHERE 条件中提前过滤。 -
预聚合高频键值
将常用键单独存为列,避免每次查询展开:
ALTER TABLE user_tags
ADD COLUMN os String MATERIALIZED tags['os'];
六、总结
通过 ARRAY JOIN
结合以下方法展开 Map:
方法 | 适用场景 |
---|---|
mapKeys + mapValues | 兼容所有 ClickHouse 版本 |
.Keys + .Values | ClickHouse 22.6+ 版本更简洁写法 |
mapEntries | 直接展开为元组,适合键值对联合操作 |