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

-- Start DuckDB
$ duckdb

-- Install and load the HTTP extension
INSTALL http;
LOAD http;

-- View available functions
SHOW ALL FUNCTIONS LIKE '%http%';

-- Load HTTP extension
INSTALL http;
LOAD http;

-- Directly query GitHub repository information
SELECT 
    json_extract_scalar(value, '$.login') AS username,
    json_extract_scalar(value, '$.avatar_url') AS avatar,
    json_extract_scalar(value, '$.public_repos') AS repos,
    json_extract_scalar(value, '$.followers') AS followers
FROM json_each(
    http_get(
        'https://api.github.com/repos/duckdb/duckdb',
        {'headers': {'Accept': 'application/vnd.github.v3+json'}}
    ),
    '$.contributors'
) AS t(value);

-- Using Open-Meteo free weather API (no API key required)
SELECT 
    json_extract_scalar(value, '$.time') AS date,
    json_extract_scalar(value, '$.weather_code') AS weather_code,
    json_extract_scalar(value, '$.temperature_2m_max') AS temp_max,
    json_extract_scalar(value, '$.temperature_2m_min') AS temp_min,
    json_extract_scalar(value, '$.precipitation_sum') AS precipitation
FROM json_each(
    http_get('https://archive-api.open-meteo.com/v1/archive?latitude=39.9&longitude=116.3&start_date=2025-01-01&end_date=2025-01-31&daily=temperature_2m_max,temperature_2m_min,precipitation_sum&timezone=Asia/Shanghai'),
    '$.daily.time'
) AS t(time);

-- POST request example: sending data to a webhook
SELECT http_post(
    'https://hooks.slack.com/services/YOUR/WEBHOOK/URL',
    '{"text":"DuckDB report has been generated"}',
    {'headers': {'Content-Type': 'application/json'}}
);

-- PUT request (simulated via http_post)
SELECT http_post(
    'https://api.example.com/resource/123',
    '{"name":"updated","status":"active"}',
    {'method': 'PUT', 'headers': {'Content-Type': 'application/json'}}
);

-- Parse deeply nested e-commerce API response
WITH api_response AS (
    SELECT http_get(
        'https://api.example.com/orders?limit=100'
    ) AS raw_data
),
parsed_json AS (
    SELECT 
        json_extract(raw_data, '$.orders') AS orders_json
    FROM api_response
),
order_items AS (
    SELECT 
        json_extract_scalar(order_val, '$.id') AS order_id,
        json_extract_scalar(order_val, '$.customer.name') AS customer_name,
        json_extract_scalar(order_val, '$.customer.email') AS customer_email,
        json_extract_scalar(order_val, '$.status') AS status,
        json_extract_scalar(order_val, '$.total') AS total_amount
    FROM parsed_json,
    json_each(parsed_json.orders_json) 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://example.com/data/dataset.parquet');

-- Read multiple files (glob pattern)
SELECT COUNT(*) FROM read_parquet('https://storage.example.com/logs/*.parquet');

-- Read remote CSV (auto-detect delimiter)
SELECT * FROM read_csv_auto('https://example.com/data/sales.csv');

-- Read remote JSON file
SELECT * FROM read_json_auto('https://example.com/data/users.json');

-- 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://api.analytics.example.com/export?format=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 
        json_extract_scalar(item, '$.product_name') AS product,
        json_extract_scalar(item, '$.rating') AS rating,
        json_extract_scalar(item, '$.review_date') AS review_date,
        json_extract_scalar(item, '$.source') AS source
    FROM json_each(
        http_get('https://api.review-tracker.com/v1/products?ids=101,102,103'),
        '$.reviews'
    ) AS t(item)
),
-- Source 2: Social media mentions
social_mentions AS (
    SELECT 
        json_extract_scalar(m, '$.mention_text') AS text,
        json_extract_scalar(m, '$.sentiment') AS sentiment,
        json_extract_scalar(m, '$.platform') AS platform,
        json_extract_scalar(m, '$.timestamp') AS mentioned_at
    FROM json_each(
        http_get('https://api.social-tracker.com/v1/mentions?q=competitor'),
        '$.results'
    ) AS t(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 json_each(
    http_get('https://api.github.com/users/duckdb/repos'),
    '$[*]'
) AS t(value);

-- Subsequent analysis queries the cache directly
SELECT 
    json_extract_scalar(value, '$.name') AS repo_name,
    json_extract_scalar(value, '$.stargazers_count') AS stars,
    json_extract_scalar(value, '$.language') AS language
FROM cached_github_repos
WHERE json_extract_scalar(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://storage.example.com/large-dataset.parquet')
WHERE date >= '2025-06-01' AND category = 'electronics';