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

In daily data analysis work, we frequently need to fetch data from various Web APIs — weather forecasts, stock quotes, social media metrics, e-commerce data, and more. The traditional approach involves writing Python scripts with the requests library, parsing JSON, and importing into Pandas or a database. But with DuckDB’s HTTP extension and built-in JSON functions, all of this can be done in pure SQL.

This article walks you through mastering the complete skill stack for querying Web APIs directly with DuckDB.

POST 请求的限制

注意： DuckDB 的 httpfs 扩展目前仅支持 HTTP GET 请求。如需 POST，请用 Python requests 预处理后再喂给 DuckDB。

Feature 3: Nested JSON Parsing

Real-world API responses often contain deeply nested JSON. DuckDB provides a rich set of JSON functions to handle such cases:

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

Key points:

• json_extract() returns a JSON value (usable for further parsing)
• column->>'field' extracts a string field from JSON
• read_json_auto() auto-detects JSON structure and returns a relational table
• json_object_keys() retrieves the keys of a JSON object

Feature 4: Querying Remote Parquet/CSV Files Directly

One of DuckDB’s most powerful features is querying cloud-stored data files directly, without downloading them:

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

Practical Project: Building an Automated Competitor Monitoring Dashboard

Here’s a complete real-world example — monitoring competitors’ product ratings over time:

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

This query showcases DuckDB’s advantages in handling multi-source data:

1. No need to write Python loops to fetch data from multiple APIs
2. All data can be JOINed and aggregated at the SQL level
3. Query results can be exported to Parquet for downstream use

Performance Optimization Tips

1. Cache HTTP Responses

Frequently querying the same API is wasteful. Use DuckDB’s temporary tables to cache:

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

2. Parallel Reading of Multiple Files

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

3. Predicate Pushdown Filtering

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

Architecture Diagram

Here’s the overall architecture flow for querying Web APIs with DuckDB:

Comparison Summary with Traditional Tools

Monetization Guide: How to Make Money with This Skill

After mastering DuckDB’s HTTP extension and JSON processing capabilities, you can monetize in several directions:

1. Automated Data Reporting Service (Monthly Income $200-$1,000)

Provide daily/weekly automated data reports for small and medium businesses:

• Pull sales data from e-commerce APIs and generate weekly reports automatically
• Scrape brand mentions from social media APIs and produce sentiment analysis reports
• Fetch historical weather data from APIs and provide decision support for agriculture/logistics clients

Implementation Steps:

1. Register for DuckDB Cloud or use local DuckDB
2. Write SQL scripts to pull data from target APIs
3. Use COPY ... TO 'report.csv' to export results
4. Schedule with cron jobs for automation
5. Deliver reports via email or Slack

2. Data Product SaaS (Monthly Income $500-$5,000)

Build data products for specific industries:

• Real Estate Price Monitor: Aggregate APIs from multiple property websites for regional price trends
• Competitor Price Tracker: Periodically scrape e-commerce product prices and inventory
• Creator Analytics Dashboard: Integrate multi-platform data from YouTube/TikTok/Bilibili

Tech Stack: DuckDB (data processing) + FastAPI (backend) + Streamlit (frontend)

3. Data Consulting Services (Per Project $300-$2,000)

Many companies have data but don’t know how to use it. You can offer:

• API data integration solution design
• Migration and optimization of existing data pipelines to DuckDB
• Customized data analysis and reporting development

4. Online Courses and Tutorials (Passive Income)

Turn your experience into paid courses:

• “Building a Data Analysis Pipeline from Scratch with DuckDB”
• “Web API Data Scraping and Analysis in Practice”
• “Advanced DuckDB JSON Processing Techniques”

Key Advantage: DuckDB’s HTTP extension makes data acquisition extremely simple. In your courses, you can focus on “analysis” itself rather than spending extensive time writing scraper code.

Summary: DuckDB’s HTTP extension and JSON functions enable you to complete the entire workflow from data acquisition to analysis using pure SQL. Whether for personal projects or commercial applications, this is a powerful skill combination. Open DuckDB now and try querying an API that interests you!