ApsaraDB for SelectDB:Query complex data types

Create table example

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 data

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]);

Common query functions

For more ARRAY functions, see the Apache Doris Array Functions documentation.

Flattening an array

Use the EXPLODE() function with LATERAL VIEW to expand each array element into a separate row.

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

Create table example

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 data

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'});

Common query functions

For more MAP functions, see the Apache Doris Map Functions documentation.

Flattening a map

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

Create table example

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 data

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'));

Query methods

Use dot notation (.) to access fields within a struct:

-- Access fields of a struct
SELECT order_id, address.city, address.zipcode FROM orders;

-- Use a struct field in a WHERE clause
SELECT * FROM orders WHERE address.city = 'Beijing';

Create table example

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 data

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}');

Common query functions

For more JSON functions, see the Apache Doris JSON Functions documentation.

Usage notes

• For frequently queried JSON fields, consider extracting them into separate columns or using the VARIANT type for better query performance.

• The JSON type cannot be used as a key column or for partitioning and bucketing.

• The maximum size for a single JSON field is 1 GB.

Create table example

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");

Insert data

The VARIANT type accepts JSON-formatted strings. The system automatically infers the schema and stores the data in columnar storage.

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"}');

Query methods

Access VARIANT fields using dot or bracket notation. You can also use CAST to perform type conversions:

-- Directly access fields (returns a VARIANT type)
SELECT event_data['action'], event_data['page'] FROM user_events;

-- Convert types using 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 vs. JSON