DuckDB JSON 数据处理完全指南 — 进阶技巧

-- 假设有一个包含用户评论的产品表
CREATE TABLE product_reviews AS
SELECT * FROM read_json_auto('reviews.json');

-- 展开评论中的标签数组
SELECT 
    review_id,
    product_name,
    json_value(review_data, '$.rating') AS rating,
    tag.value AS tag
FROM product_reviews
CROSS JOIN json_each(
    json_extract(review_data, '$.tags')
) AS tag;

-- 探索复杂的嵌套结构
SELECT 
    type,
    key,
    value,
    json_type(value) AS value_type
FROM json_tree(
    '{"user": {"profile": {"name": "张三", "age": 30}, "settings": {"theme": "dark"}}}'
);

-- 提取特定路径的值
SELECT 
    json_extract(
        json_column,
        '$.user.profile.name'
    ) AS username
FROM complex_json_table;

-- 将订单明细聚合成 JSON 数组
SELECT 
    order_id,
    json_group_array(
        json_build_object(
            'product', product_name,
            'quantity', quantity,
            'price', unit_price
        )
    ) AS items
FROM orders
GROUP BY order_id;

-- 创建结构化的客户画像
SELECT 
    customer_id,
    json_build_object(
        'name', first_name || ' ' || last_name,
        'orders', (
            SELECT json_agg(json_build_object(
                'order_id', order_id,
                'total', total_amount,
                'date', order_date
            ))
            FROM orders o
            WHERE o.customer_id = c.customer_id
        ),
        'preferences', json_build_object(
            'newsletter', prefers_newsletter,
            'language', preferred_language
        )
    ) AS customer_profile
FROM customers c;

-- 安全地提取可能不存在的字段
SELECT 
    data->>'$.user.email' AS email,
    data->>'$.user.phone' AS phone,
    COALESCE(data->>'$.user.phone', '未提供') AS phone_with_default
FROM user_data;

-- 处理混合类型的字段
SELECT 
    json_typeof(data->'$.score') AS score_type,
    CASE 
        WHEN json_typeof(data->'$.score') = 'number' THEN CAST(data->'$.score' AS INTEGER)
        ELSE 0
    END AS numeric_score
FROM mixed_data;

-- 检查 JSON 格式的正确性
SELECT 
    id,
    CASE 
        WHEN json_valid(json_column) THEN '有效'
        ELSE '无效'
    END AS validation_status
FROM raw_data;

-- 查找缺少必需字段的记录
SELECT 
    id,
    json_extract(json_column, '$.required_field') AS required_value
FROM data_table
WHERE json_extract(json_column, '$.required_field') IS NULL;

-- 统计字段出现频率
SELECT 
    key,
    COUNT(*) AS frequency
FROM (
    SELECT json_each.key AS key
    FROM data_table,
         json_each(data_table.json_column)
) AS keys
GROUP BY key
ORDER BY frequency DESC;

-- 创建虚拟列用于加速查询
ALTER TABLE users ADD COLUMN email_text TEXT GENERATED ALWAYS AS (
    json_extract(users.json_data, '$.email')
) STORED;

-- 创建索引
CREATE INDEX idx_user_email ON users(email_text);

-- 设置并行度
SET threads TO 8;

-- 执行复杂的 JSON 聚合查询
SELECT 
    category,
    COUNT(*) AS item_count,
    AVG(CAST(json_extract(data, '$.price') AS DOUBLE)) AS avg_price
FROM products
GROUP BY category;

-- 创建物化视图
CREATE MATERIALIZED VIEW user_summary AS
SELECT 
    json_extract(json_data, '$.id') AS user_id,
    json_extract(json_data, '$.name') AS user_name,
    json_extract(json_data, '$.email') AS user_email,
    json_extract(json_data, '$.location') AS location
FROM users;

-- 定期刷新物化视图
REFRESH MATERIALIZED VIEW user_summary;

-- 1. 创建示例数据
CREATE TABLE reviews AS
SELECT * FROM read_json_auto('ecommerce_reviews.json');

-- 2. 提取评论中的关键信息
WITH review_details AS (
    SELECT 
        json_extract(json_data, '$.review_id') AS review_id,
        json_extract(json_data, '$.product_id') AS product_id,
        CAST(json_extract(json_data, '$.rating') AS INTEGER) AS rating,
        json_extract(json_data, '$.review_text') AS review_text,
        json_extract(json_data, '$.timestamp') AS timestamp,
        json_extract(json_data, '$.verified_purchase') AS is_verified
    FROM reviews
),
-- 3. 关键词提取（简化版）
keyword_counts AS (
    SELECT 
        product_id,
        LOWER(SUBSTRING(review_text, 1, POSITION(' ' IN review_text) - 1)) AS keyword,
        COUNT(*) AS count
    FROM review_details
    WHERE LENGTH(review_text) > 0
    GROUP BY product_id, keyword
)
-- 4. 综合分析报告
SELECT 
    rd.product_id,
    AVG(rd.rating) AS avg_rating,
    COUNT(*) AS total_reviews,
    SUM(CASE WHEN rd.is_verified = 'true' THEN 1 ELSE 0 END) AS verified_purchases,
    STRING_AGG(DISTINCT kc.keyword, ', ') AS top_keywords
FROM review_details rd
LEFT JOIN keyword_counts kc ON rd.product_id = kc.product_id
GROUP BY rd.product_id
ORDER BY avg_rating DESC;