DuckDB’s Nested Data Trinity: LIST, STRUCT, MAP in One SQL Query

-- Create a table with LIST columns
CREATE TABLE orders (
 order_id INTEGER,
 tags LIST(VARCHAR),
 amounts LIST(DOUBLE)
);

-- Insert data
INSERT INTO orders VALUES
 (1, ['Electronics', 'Hot Seller', 'Phone'], [7999.00, 99.00]),
 (2, ['Clothing', 'Summer'], [199.00]),
 (3, ['Electronics', 'Computer', 'Premium'], [12999.00, 299.00, 59.00]);

-- Expand each tag into its own row
SELECT 
 order_id,
 tag
FROM orders,
 UNNEST(tags) AS tag;

-- Count how many times each tag appears
SELECT 
 tag,
 COUNT(*) AS usage_count,
 COUNT(DISTINCT order_id) AS order_count
FROM orders,
 UNNEST(tags) AS tag
GROUP BY tag
ORDER BY usage_count DESC;

-- Create a table with STRUCT columns
CREATE TABLE employees (
 id INTEGER,
 info STRUCT(
 name VARCHAR,
 age INTEGER,
 skills LIST(VARCHAR),
 address STRUCT(
 city VARCHAR,
 district VARCHAR
 )
 )
);

-- Insert data
INSERT INTO employees VALUES
 (1, STRUCT('Alice', 30, ['Python', 'SQL'], STRUCT('Beijing', 'Chaoyang'))),
 (2, STRUCT('Bob', 25, ['Java', 'Go'], STRUCT('Shanghai', 'Pudong'))),
 (3, STRUCT('Charlie', 35, ['Python', 'R'], STRUCT('Guangzhou', 'Tianhe')));

-- Access nested fields with dot notation
SELECT 
 info.name AS name,
 info.age AS age,
 info.skills[1] AS primary_skill,
 info.address.city AS city
FROM employees;

-- Filter: employees older than 28
SELECT info.name, info.skills
FROM employees
WHERE info.age > 28;

-- Aggregate: count how many people have each skill
SELECT 
 UNNEST(info.skills) AS skill,
 COUNT(*) AS count
FROM employees
GROUP BY skill
ORDER BY count DESC;

-- Create a table with MAP columns
CREATE TABLE user_configs (
 user_id INTEGER,
 settings MAP(VARCHAR, VARCHAR),
 preferences MAP(VARCHAR, INTEGER)
);

-- Insert data
INSERT INTO user_configs VALUES
 (1, MAP({'theme': 'dark', 'lang': 'zh', 'notifications': 'true'}),
 MAP({'clicks': 1500, 'views': 8000, 'orders': 23})),
 (2, MAP({'theme': 'light', 'lang': 'en', 'notifications': 'false'}),
 MAP({'clicks': 300, 'views': 1200, 'orders': 5})),
 (3, MAP({'theme': 'dark', 'lang': 'ja', 'notifications': 'true'}),
 MAP({'clicks': 2000, 'views': 10000, 'orders': 45}));

-- Query specific keys
SELECT 
 user_id,
 settings['theme'] AS theme,
 settings['lang'] AS language,
 preferences['clicks'] AS click_count
FROM user_configs;

-- Filter: users with dark theme
SELECT * 
FROM user_configs 
WHERE settings['theme'] = 'dark';

-- Assume orders.json format:
-- [{"order_id":1, "items":[{"name":"iPhone","price":7999,"tags":["Electronics","Hot Seller"]}]}, ...]

SELECT 
 order_id,
 item.name AS product_name,
 item.price AS price,
 item.tags[1] AS primary_tag
FROM read_json_auto('orders.json'),
 LATERAL UNNEST(items) AS item;

-- Extract all tags from orders and count usage
SELECT 
 tag,
 COUNT(*) AS usage_count
FROM read_json_auto('orders.json'),
 LATERAL UNNEST(items) AS item,
 LATERAL UNNEST(item.tags) WITH ORDINALITY AS tags(tag, i)
GROUP BY tag
ORDER BY usage_count DESC;

-- Create complex orders: each order has multiple products (STRUCT in LIST)
CREATE TABLE orders_complex AS
SELECT * FROM (
 VALUES
 (1, [
 STRUCT('iPhone', 7999.00, ['Electronics', 'Hot Seller']),
 STRUCT('Case', 99.00, ['Accessories'])
 ]),
 (2, [
 STRUCT('T-Shirt', 199.00, ['Clothing', 'Summer']),
 STRUCT('Shorts', 149.00, ['Clothing', 'Summer'])
 ]),
 (3, [
 STRUCT('MacBook', 12999.00, ['Electronics', 'Computer', 'Premium'])
 ])
) AS t(order_id, items);

-- Calculate total per order
SELECT 
 order_id,
 SUM(item.col1) AS total
FROM orders_complex,
 UNNEST(items) AS item
GROUP BY order_id;