Querying Web APIs Directly with DuckDB: HTTP Extension + JSON Functions in Action

-- Parse deeply nested e-commerce API response
WITH api_response AS (
 SELECT read_json_auto('https://jsonplaceholder.typicode.com/posts', auto_detect=true) AS raw_data
),
parsed_json AS (
 SELECT 
 json_extract(raw_data, '$.orders') AS orders_json
 FROM api_response
),
order_items AS (
 SELECT 
 order_val->>'id' AS order_id,
 order_val->>'customer.name' AS customer_name,
 order_val->>'customer.email' AS customer_email,
 order_val->>'status' AS status,
 order_val->>'total' AS total_amount
 FROM parsed_json,
 read_json_auto('https://jsonplaceholder.typicode.com/posts', auto_detect=true) AS t(order_val)
)
SELECT 
 status,
 COUNT(*) AS order_count,
 ROUND(SUM(total_amount::DOUBLE), 2) AS total_revenue,
 ROUND(AVG(total_amount::DOUBLE), 2) AS avg_order_value
FROM order_items
GROUP BY status
ORDER BY total_revenue DESC;

-- Read a Parquet file directly from a URL
SELECT * FROM read_parquet('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet');

-- Read multiple files (glob pattern)
SELECT COUNT(*) FROM read_parquet('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet');

-- Read remote CSV (auto-detect delimiter)
SELECT * FROM read_csv_auto('https://raw.githubusercontent.com/vincentarelbundock/Rdatasets/master/datasets.csv');

-- Read remote JSON file
SELECT * FROM read_json_auto('https://jsonplaceholder.typicode.com/users');

-- Combine: query Parquet from an API and analyze
SELECT 
 region,
 SUM(revenue) AS total_revenue,
 AVG(order_count) AS avg_orders
FROM read_parquet('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet')
WHERE date >= '2025-01-01'
GROUP BY region
ORDER BY total_revenue DESC;

-- Step 1: Aggregate from multiple data sources
WITH competitor_data AS (
 -- Source 1: App store review API
 SELECT 
 item->>'product_name' AS product,
 item->>'rating' AS rating,
 item->>'review_date' AS review_date,
 item->>'source' AS source
 FROM read_json_auto('https://api.review-tracker.com/v1/products?ids=101,102,103', auto_detect=true) AS item
),
-- Source 2: Social media mentions
social_mentions AS (
 SELECT 
 m->>'mention_text' AS text,
 m->>'sentiment' AS sentiment,
 m->>'platform' AS platform,
 m->>'timestamp' AS mentioned_at
 FROM read_json_auto('https://api.social-tracker.com/v1/mentions?q=competitor', auto_detect=true) AS m
),
-- Combined analysis
analysis AS (
 SELECT 
 product,
 AVG(rating::DOUBLE) AS avg_rating,
 COUNT(*) AS review_count,
 MIN(review_date) AS first_review,
 MAX(review_date) AS last_review
 FROM competitor_data
 GROUP BY product
)
-- Final output: competitor list sorted by rating
SELECT 
 a.product,
 a.avg_rating,
 a.review_count,
 a.first_review,
 a.last_review,
 CASE 
 WHEN a.avg_rating >= 4.5 THEN '🟢 Strong'
 WHEN a.avg_rating >= 4.0 THEN '🟡 Stable'
 ELSE '🔴 Alert'
 END AS status
FROM analysis a
ORDER BY a.avg_rating DESC;

-- Cache API response to a temporary table
CREATE TEMP TABLE cached_github_repos AS
SELECT * FROM read_json_auto('https://api.github.com/users/duckdb/repos', auto_detect=true) AS value;

-- Subsequent analysis queries the cache directly
SELECT 
 value->>'name' AS repo_name,
 value->>'stargazers_count' AS stars,
 value->>'language' AS language
FROM cached_github_repos
WHERE value->>'stargazers_count'::BIGINT > 1000
ORDER BY stars DESC;

-- Parallel reading of multiple Parquet files (DuckDB parallelizes automatically)
SELECT 
 file_name,
 COUNT(*) AS row_count,
 SUM(size_bytes) AS total_size
FROM parquet_metadata('s3://bucket/data/*.parquet')
GROUP BY file_name
ORDER BY total_size DESC;

-- Filter during read to reduce data transfer
SELECT * FROM read_parquet('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet')
WHERE date >= '2025-06-01' AND category = 'electronics';