云数据库SelectDB版复杂数据类型查询-云数据库 SelectDB 版(SelectDB)-阿里云帮助中心

云数据库 SelectDB 版支持 ARRAY、MAP、STRUCT、JSON 和 VARIANT 等复杂数据类型，适用于存储和查询嵌套、半结构化数据。本文介绍各复杂数据类型的适用场景、建表方式及常用查询函数。

概述

在实际业务中，数据往往包含标签列表、属性键值对、嵌套对象等半结构化信息。传统的关系型列（如 VARCHAR、INT）难以高效表达这些数据。云数据库 SelectDB 版提供以下复杂数据类型：

数据类型	说明	适用场景
ARRAY	有序的同类型元素集合	标签列表、多值属性、评分序列
MAP	键值对集合，键和值分别为指定类型	扩展属性、配置项、动态字段
STRUCT	由多个具名字段组成的结构体	地址信息、坐标点、嵌套实体
JSON	以二进制格式存储的 JSON 数据	Schema 不固定的半结构化数据、日志事件
VARIANT	自动推断类型并按列式存储的半结构化类型	日志分析、用户画像等字段动态变化的场景

ARRAY 类型

ARRAY 类型用于存储一组相同类型的有序元素。声明语法为 ARRAY<element_type>，支持嵌套（如 ARRAY<ARRAY<INT>>）。

建表示例

CREATE TABLE user_tags (
    user_id BIGINT,
    tags ARRAY<VARCHAR(50)>,
    scores ARRAY<INT>
)
DUPLICATE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 4
PROPERTIES ("replication_allocation" = "tag.location.default: 1");

写入数据

INSERT INTO user_tags VALUES
(1, ['sports', 'music', 'travel'], [90, 85, 78]),
(2, ['tech', 'gaming'], [95, 88]),
(3, ['cooking', 'reading', 'sports', 'photography'], [70, 92, 80, 65]);

常用查询函数

函数	说明	示例
`array[index]`	按下标访问元素（从 0 开始）	`SELECT tags[0] FROM user_tags`
`array_size()`	返回数组元素个数	`SELECT array_size(tags) FROM user_tags`
`array_contains()`	判断数组是否包含指定元素	`SELECT * FROM user_tags WHERE array_contains(tags, 'sports')`
`array_sort()`	返回排序后的数组	`SELECT array_sort(scores) FROM user_tags`
`array_distinct()`	返回去重后的数组	`SELECT array_distinct(tags) FROM user_tags`
`array_join()`	将数组元素连接为字符串	`SELECT array_join(tags, ',') FROM user_tags`

更多 ARRAY 函数请参见 Apache Doris Array Functions 文档。

使用 EXPLODE 展开数组

通过 EXPLODE() 函数配合 LATERAL VIEW，可将数组中的每个元素展开为独立的行。

SELECT user_id, tag
FROM user_tags
LATERAL VIEW EXPLODE(tags) tmp AS tag;

MAP 类型

MAP 类型存储键值对集合，声明语法为 MAP<key_type, value_type>。键类型必须为基本类型，值类型支持嵌套复杂类型。

建表示例

CREATE TABLE product_attrs (
    product_id BIGINT,
    attributes MAP<VARCHAR(50), VARCHAR(200)>
)
DUPLICATE KEY(product_id)
DISTRIBUTED BY HASH(product_id) BUCKETS 4
PROPERTIES ("replication_allocation" = "tag.location.default: 1");

写入数据

INSERT INTO product_attrs VALUES
(1001, {'color': 'red', 'size': 'XL', 'material': 'cotton'}),
(1002, {'color': 'blue', 'weight': '500g'}),
(1003, {'brand': 'Acme', 'origin': 'China', 'warranty': '2 years'});

常用查询函数

函数	说明	示例
`map['key']`	按键取值	`SELECT attributes['color'] FROM product_attrs`
`map_size()`	返回键值对数量	`SELECT map_size(attributes) FROM product_attrs`
`map_keys()`	返回所有键组成的数组	`SELECT map_keys(attributes) FROM product_attrs`
`map_values()`	返回所有值组成的数组	`SELECT map_values(attributes) FROM product_attrs`
`map_contains_key()`	判断是否包含指定键	`SELECT * FROM product_attrs WHERE map_contains_key(attributes, 'brand')`

更多 MAP 函数请参见 Apache Doris Map Functions 文档。

使用 EXPLODE_MAP 展开 MAP

SELECT product_id, key, value
FROM product_attrs
LATERAL VIEW EXPLODE_MAP(attributes) tmp AS key, value;

STRUCT 类型

STRUCT 类型由一组具名字段组成，每个字段可以是不同的数据类型。声明语法为 STRUCT<field1:type1, field2:type2, ...>。

建表示例

CREATE TABLE orders (
    order_id BIGINT,
    address STRUCT<city:VARCHAR(50), street:VARCHAR(200), zipcode:VARCHAR(10)>
)
DUPLICATE KEY(order_id)
DISTRIBUTED BY HASH(order_id) BUCKETS 4
PROPERTIES ("replication_allocation" = "tag.location.default: 1");

写入数据

INSERT INTO orders VALUES
(1, NAMED_STRUCT('city', 'Beijing', 'street', 'Zhongguancun Road 1', 'zipcode', '100080')),
(2, NAMED_STRUCT('city', 'Shanghai', 'street', 'Nanjing West Road 100', 'zipcode', '200041'));

查询方式

通过点号（.）访问 STRUCT 内部字段：

-- 访问 STRUCT 字段
SELECT order_id, address.city, address.zipcode FROM orders;

-- 在 WHERE 条件中使用 STRUCT 字段
SELECT * FROM orders WHERE address.city = 'Beijing';

JSON 类型

JSON 类型以二进制格式存储 JSON 数据，适用于 Schema 不固定的半结构化场景。与 VARCHAR 存储 JSON 字符串相比，JSON 类型在查询时无需重复解析，性能更优。

建表示例

CREATE TABLE event_logs (
    event_id BIGINT,
    event_time DATETIME,
    payload JSON
)
DUPLICATE KEY(event_id)
DISTRIBUTED BY HASH(event_id) BUCKETS 4
PROPERTIES ("replication_allocation" = "tag.location.default: 1");

写入数据

INSERT INTO event_logs VALUES
(1, '2024-01-15 10:30:00', '{"action": "login", "device": "mobile", "os": "iOS", "version": "17.2"}'),
(2, '2024-01-15 10:31:00', '{"action": "purchase", "amount": 99.9, "items": ["book", "pen"], "coupon": null}'),
(3, '2024-01-15 10:32:00', '{"action": "logout", "duration_sec": 300}');

常用查询函数

函数	说明	示例
`json_extract()`	通过 JSON Path 提取值	`SELECT json_extract(payload, '$.action') FROM event_logs`
`json_extract_string()`	提取 JSON 值并返回字符串	`SELECT json_extract_string(payload, '$.device') FROM event_logs`
`json_extract_int()`	提取 JSON 值并返回整数	`SELECT json_extract_int(payload, '$.duration_sec') FROM event_logs`
`json_extract_double()`	提取 JSON 值并返回浮点数	`SELECT json_extract_double(payload, '$.amount') FROM event_logs`
`json_exists_path()`	判断指定路径是否存在	`SELECT * FROM event_logs WHERE json_exists_path(payload, '$.coupon')`
`json_type()`	返回 JSON 值的类型	`SELECT json_type(payload, '$.items') FROM event_logs`

更多 JSON 函数请参见 Apache Doris JSON Functions 文档。

使用建议

对于频繁查询的 JSON 字段，可考虑提取为独立列或使用 VARIANT 类型以获得更好的查询性能。
JSON 类型不支持作为 Key 列或用于分区、分桶。
JSON 类型的单个字段最大支持 1 GB。

VARIANT 类型

VARIANT 是 SelectDB 提供的半结构化数据类型，能够自动推断数据的具体类型并以列式存储。与 JSON 类型相比，VARIANT 在查询性能上更优，适合于字段可变但需要高性能分析的场景。

建表示例

CREATE TABLE user_events (
    event_id BIGINT,
    event_time DATETIME,
    event_data VARIANT
)
DUPLICATE KEY(event_id)
DISTRIBUTED BY HASH(event_id) BUCKETS 4
PROPERTIES ("replication_allocation" = "tag.location.default: 1");

写入数据

VARIANT 类型可以接收 JSON 格式的字符串，系统自动推断并以列式存储。

INSERT INTO user_events VALUES
(1, '2024-01-15 10:00:00', '{"uid": 1001, "action": "click", "page": "/home", "duration": 5.2}'),
(2, '2024-01-15 10:01:00', '{"uid": 1002, "action": "scroll", "page": "/products", "items_viewed": 12}'),
(3, '2024-01-15 10:02:00', '{"uid": 1001, "action": "purchase", "amount": 299.0, "payment": "alipay"}');

查询方式

通过点号或方括号语法访问 VARIANT 字段，并可使用 CAST 进行类型转换：

-- 直接访问字段（返回 VARIANT 类型）
SELECT event_data['action'], event_data['page'] FROM user_events;

-- 使用 CAST 转换为具体类型
SELECT
    CAST(event_data['uid'] AS BIGINT) AS uid,
    CAST(event_data['action'] AS VARCHAR) AS action,
    CAST(event_data['amount'] AS DOUBLE) AS amount
FROM user_events
WHERE CAST(event_data['action'] AS VARCHAR) = 'purchase';

VARIANT 与 JSON 的选择

对比维度	VARIANT	JSON
存储方式	列式存储，自动类型推断	二进制 JSON 格式
查询性能	高（列式存储+向量化执行）	中（需运行时解析）
Schema 灵活性	自动适应，无需预定义	完全灵活
推荐场景	日志分析、用户行为等需要高性能分析的场景	写入频率极高且字段极不固定的场景

使用限制

ARRAY、MAP、STRUCT、JSON 和 VARIANT 类型不能作为表的 Key 列（如 DUPLICATE KEY、UNIQUE KEY、AGGREGATE KEY 中的列）。
复杂数据类型不能用于 DISTRIBUTED BY 的分桶列。
ARRAY 和 MAP 类型支持嵌套，最大嵌套深度为 9 层。
在 AGGREGATE KEY 模型的表中，复杂类型列的聚合函数只支持 REPLACE_IF_NOT_NULL 和 NONE。

概述

ARRAY 类型

建表示例

写入数据

常用查询函数

使用 EXPLODE 展开数组

MAP 类型

建表示例

写入数据

常用查询函数

使用 EXPLODE_MAP 展开 MAP

STRUCT 类型

建表示例

写入数据

查询方式

JSON 类型

建表示例

写入数据

常用查询函数

使用建议

VARIANT 类型

建表示例

写入数据

查询方式

VARIANT 与 JSON 的选择

使用限制

相关文档