CREATE TABLE logs (
    id INTEGER,
    payload VARIANT
);

-- 直接加载 JSON 文件到 VARIANT 列
INSERT INTO logs
SELECT 1 AS id, json_file.* :: VARIANT AS payload
FROM read_json_auto('logs.json');

INSERT INTO logs VALUES
    (1, '{"user": "alice", "action": "login", "metadata": {"ip": "192.168.1.1", "device": "mobile"}}' :: VARIANT),
    (2, '{"user": "bob", "action": "purchase", "metadata": {"ip": "10.0.0.1", "amount": 29.99}}' :: VARIANT),
    (3, '{"user": "charlie", "action": "login", "metadata": {"ip": "172.16.0.1", "device": "desktop", "failed_attempts": 3}}' :: VARIANT);

CREATE TABLE event_log AS
SELECT * FROM read_json_auto('events.json', format='auto', columns={'data': 'VARIANT'});

-- 传统 JSON 查询：需要记住一堆函数
SELECT json_extract(payload, '$.user') FROM logs;

-- VARIANT 点号语法：像访问普通列一样
SELECT payload.user FROM logs;

-- 深层嵌套字段，一行搞定
SELECT
    payload.user AS username,
    payload.metadata.ip AS ip_address,
    payload.metadata.device AS device_type,
    payload.metadata.amount AS amount
FROM logs
WHERE payload.metadata.ip IS NOT NULL;

-- 假设 payload 中有数组字段
SELECT
    payload.tags[1] AS first_tag,
    payload.items[1:3] AS first_three_items
FROM events;

SELECT
    payload.user,
    variant_typeof(payload.user) AS user_type,
    variant_typeof(payload.metadata) AS metadata_type,
    variant_typeof(payload.metadata.amount) AS amount_type
FROM logs;

-- 判断是否为某类型
SELECT
    variant_is_object(payload.metadata),
    variant_is_array(payload.tags),
    variant_is_string(payload.user),
    variant_is_numeric(payload.metadata.amount)
FROM logs;

-- 获取列中的不同结构
SELECT variant_keys(payload) AS all_keys FROM logs LIMIT 1;
-- => ['user', 'action', 'metadata']

-- 展开嵌套数组
SELECT
    payload.user,
    UNNEST(payload.tags) AS tag
FROM events;

-- VARIANT 与 JSON 互转
SELECT
    payload :: JSON AS as_json,        -- VARIANT → JSON
    '{"key": "value"}' :: VARIANT;     -- JSON → VARIANT

-- JSON 字符串方案
SELECT json_extract(raw_json, '$.user_id')
FROM json_table
WHERE json_extract(raw_json, '$.action') = '"purchase"';

-- VARIANT 方案
SELECT payload.user_id
FROM variant_table
WHERE payload.action = 'purchase';

-- 1. 提取常用字段为物化列（最佳实践）
ALTER TABLE logs ADD COLUMN user_id VARCHAR
    GENERATED ALWAYS AS (payload.user_id :: VARCHAR);

-- 2. 常用过滤字段创建索引
CREATE INDEX idx_user_action ON logs (payload.user :: VARCHAR, payload.action :: VARCHAR);

-- 3. 使用 VARIANT 作为中间存储，提取后转为静态表
CREATE TABLE clean_events AS
SELECT
    payload.event_id :: BIGINT AS event_id,
    payload.event_type :: VARCHAR AS event_type,
    payload.timestamp :: TIMESTAMP AS timestamp
FROM raw_events;