DuckDB JSON Data Processing Complete Guide — Advanced Techniques

-- Assuming a products table containing user reviews
CREATE TABLE product_reviews AS
SELECT * FROM read_json_auto('reviews.json');

-- Expand tags array in reviews
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;

-- Explore complex nested structures
SELECT 
    type,
    key,
    value,
    json_type(value) AS value_type
FROM json_tree(
    '{"user": {"profile": {"name": "John Doe", "age": 30}, "settings": {"theme": "dark"}}}'
);

-- Extract values at specific paths
SELECT 
    json_extract(
        json_column,
        '$.user.profile.name'
    ) AS username
FROM complex_json_table;

-- Aggregate order details into JSON array
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;

-- Create structured customer profiles
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;

-- Safely extract potentially non-existent fields
SELECT 
    data->>'$.user.email' AS email,
    data->>'$.user.phone' AS phone,
    COALESCE(data->>'$.user.phone', 'Not provided') AS phone_with_default
FROM user_data;

-- Handle mixed-type fields
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;

-- Check JSON format validity
SELECT 
    id,
    CASE 
        WHEN json_valid(json_column) THEN 'Valid'
        ELSE 'Invalid'
    END AS validation_status
FROM raw_data;

-- Find records missing required fields
SELECT 
    id,
    json_extract(json_column, '$.required_field') AS required_value
FROM data_table
WHERE json_extract(json_column, '$.required_field') IS NULL;

-- Count field occurrence frequency
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;

-- Create virtual column for faster queries
ALTER TABLE users ADD COLUMN email_text TEXT GENERATED ALWAYS AS (
    json_extract(users.json_data, '$.email')
) STORED;

-- Create index
CREATE INDEX idx_user_email ON users(email_text);

-- Set parallelism level
SET threads TO 8;

-- Execute complex JSON aggregation query
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
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;

-- Periodically refresh materialized view
REFRESH MATERIALIZED VIEW user_summary;

-- 1. Create sample data
CREATE TABLE reviews AS
SELECT * FROM read_json_auto('ecommerce_reviews.json');

-- 2. Extract key information from reviews
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 extraction (simplified version)
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. Comprehensive analysis report
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;