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 table
CREATE TABLE IF NOT EXISTS 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 table
-- 注：DuckDB 不支持 REFRESH materialized table
-- 请 DROP TABLE IF EXISTS user_summary; CREATE TABLE user_summary AS ...

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