Opendata Api

OpenData Query API

Query datasets stored as Parquet files through a REST API backed by DuckDB. The API returns JSON by default, with support for CSV, TSV, and XLSX exports.

Base URL: https://api.tryopendata.ai (production) or http://localhost:8000 (local dev). Default to use production

Authentication

All endpoints require authentication in production. Before making API calls, resolve a Bearer token using this sequence:

1. Check env var: If OPENDATA_API_KEY is set, use it.
2. Check auth.json: Read ~/.config/opendata/auth.json. If it exists, extract the token:
   • method: "api_key" -> use the api_key field
   • method: "clerk" -> use the access_token field (check expires_at hasn't passed)
   • Legacy format (no method field, just api_key) -> use the api_key field
3. Prompt to authenticate: If neither source has a token:
   • Check if the opendata CLI is installed (which opendata)
   • If installed: run opendata auth login and let the user authenticate
   • If not installed: tell the user to install it (brew install tryopendata/opendata/opendata or curl -fsSL https://raw.githubusercontent.com/tryopendata/opendata/main/scripts/install-cli.sh | bash), then run opendata auth login
   • As a fallback, the user can set OPENDATA_API_KEY manually with a key from https://tryopendata.ai/settings/api-keys

Once resolved, pass the token via Authorization: Bearer header:

bashCopy code
curl -H "Authorization: Bearer $TOKEN" \
  "https://api.tryopendata.ai/v1/datasets/fred/cpi?limit=5"

If you get a 401 during a session, re-run the resolution sequence (the token may have expired).

Local dev (localhost:8000) does not require auth when running the standalone opendata server (make quickstart). The backend server (make dev-all) requires auth for write endpoints but allows unauthenticated reads.

Quick Start

For analysis (aggregations, joins, window functions), use SQL:

bashCopy code
# Average CPI by year, most recent first
curl -X POST "https://api.tryopendata.ai/v1/datasets/fred/cpi/query" \
  -H "Authorization: Bearer ${OPENDATA_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT EXTRACT(YEAR FROM date) as year, AVG(value) as avg_cpi FROM data GROUP BY 1 ORDER BY 1 DESC LIMIT 10"}'

Parameterized queries (avoids escaping issues):

bashCopy code
curl -X POST "https://api.tryopendata.ai/v1/datasets/owid/gdp/query" \
  -H "Authorization: Bearer ${OPENDATA_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM data WHERE country_name = ? AND year >= ? ORDER BY year", "params": ["United States", 2020]}'

For simple row fetches (no aggregation), use the REST endpoint:

bashCopy code
# Get the 5 most recent CPI values
curl -H "Authorization: Bearer ${OPENDATA_API_KEY}" \
  "https://api.tryopendata.ai/v1/datasets/fred/cpi?limit=5&sort=-date"

Do NOT append /query to GET requests. GET /v1/datasets/fred/cpi/query will fail with a SUBDATASET_NOT_FOUND error because the API interprets query as a subdataset name. The POST /query endpoint is a separate SQL interface (see sql-query.md).

All data endpoints live under /v1/datasets/.

MCP Tools (Preferred When Available)

If you have access to OpenData MCP tools (search_datasets, query_dataset, query_sql), prefer them over direct API calls. The MCP tools handle auth, pagination, and response formatting automatically. Use query_sql for analytical queries (aggregations, joins, window functions) and query_dataset for simple row fetches. Fall back to the REST API below only when:

• MCP tools are not connected
• You need endpoints the MCP doesn't cover (graph intelligence, composition, activity feeds)
• You need raw HTTP control (custom headers, streaming, specific formats)

Endpoints

Data & Schema

Enrichment & Intelligence

Composition (Cross-Dataset Joins)

Search & Discovery

Graph Intelligence

Subdatasets

Some datasets contain multiple tables (e.g., multi-sheet Excel workbooks, BLS series groups). For these:

• GET /v1/datasets/{provider}/{dataset} returns data for the default subdataset, or lists available subdatasets
• GET /v1/datasets/{provider}/{dataset}/{subdataset} queries a specific subdataset

If you get a SUBDATASET_NOT_FOUND error, the dataset likely has subdatasets. Check the error response's suggestions field - it includes a link to browse available subdatasets. Any unrecognized path segment after the dataset slug is interpreted as a subdataset name, which is why paths like /query or /search appended to a dataset path produce this error.

Query Parameters

Common Pitfalls

Use filter[col]=val, not ?col=val. Bare column names as query params are silently ignored. The API returns a structured warning, but you still get unfiltered data back.

bashCopy code
# Wrong - returns ALL rows, with a warning
curl '.../nces/naep?year=2024'

# Right
curl '.../nces/naep?filter[year]=2024'

URL-encode brackets in curl. Some shells interpret [ and ]. Use %5B / %5D or quote the URL.

bashCopy code
curl 'https://api.tryopendata.ai/v1/datasets/nces/naep?filter%5Byear%5D=2024'

Check warnings in the response. Unknown parameters produce structured QueryWarning objects with code, message, and param. The X-OpenData-Warnings HTTP header also carries these for piped workflows.

Use ?debug=true to see generated SQL. Returns a debug object with debug.query (echo of your parameters) and debug.sql (the DuckDB SQL that ran). Useful for verifying filters and sorts are applied correctly.

aggregate and nest_fields are mutually exclusive. You get a 400 error if you combine them. Aggregation produces flat summary rows; nesting produces grouped hierarchical data.

If a SQL query returns an error, check the error response body for details. Common causes: invalid column names (verify with GET .../columns), syntax issues, or timeout on very large datasets. For simple aggregations that don't need SQL features (window functions, CTEs, joins), the REST aggregate + group_by params are an alternative.

Sorting on computed aggregation columns works. When using aggregate + group_by, you can sort on the computed column names (e.g., sort=-count_event_id for aggregate=count(event_id)). Invalid sort fields return a 400 with valid_values showing available options.

Always use api.tryopendata.ai for POST endpoints. The frontend at tryopendata.ai/api/ proxies GET requests only. POST requests to tryopendata.ai/api/v1/query return 405. Use api.tryopendata.ai/v1/query directly for SQL and cross-dataset queries.

Set a User-Agent header in API requests. Some CDN/WAF configurations may block requests with missing or generic user agents. Include a descriptive identifier:

bashCopy code
curl -H "User-Agent: claude-code/opendata-skill" \
  -H "Authorization: Bearer ${OPENDATA_API_KEY}" \
  "https://api.tryopendata.ai/v1/datasets/fred/cpi?limit=5"

SQL Query

The POST /v1/datasets/{provider}/{dataset}/query endpoint accepts raw SQL and executes it against the dataset. Requires authentication (API key or session). The dataset table is available as data or "provider/dataset". SQL is validated against an allowlist (SELECT only, no DDL/DML/IO) and runs with resource limits (5s timeout, 10k rows, 512MB memory).

Parameterized queries: Use ? placeholders with a params array to avoid string quoting issues:

jsonCopy code
{
  "sql": "SELECT * FROM data WHERE country IN (?, ?) AND year >= ?",
  "params": ["United States", "Japan", 2020]
}

This eliminates the triple-nested escaping problem (SQL quotes inside JSON inside shell). See sql-query.md for details.

Composition (Cross-Dataset Joins)

The compose endpoints let you join two datasets and preview or download the result without writing SQL. Useful for enriching a dataset with columns from a related one (e.g., joining county-level education data with census demographics).

Workflow: Call /joinable to discover what can be joined, /compose/preview to check the result, then /compose/download.csv to export. See composition.md for full details.

Composite keys: source_column and join_column accept arrays for multi-column joins. Both arrays must have the same length.

bashCopy code
# 1. What can this dataset join with?
curl 'https://api.tryopendata.ai/v1/datasets/nces/naep/joinable'

# 2. Preview the join (anonymous: 100 rows, authenticated: 5000 rows)
curl -X POST 'https://api.tryopendata.ai/v1/datasets/nces/naep/compose/preview' \
  -H 'Content-Type: application/json' \
  -d '{"joins": [{"target": "census/saipe", "source_column": "jurisdiction_name", "join_column": "name"}]}'

# 2b. Composite key join (match on multiple columns)
curl -X POST 'https://api.tryopendata.ai/v1/datasets/nces/naep/compose/preview' \
  -H 'Content-Type: application/json' \
  -d '{"joins": [{"target": "census/saipe", "source_column": ["state", "year"], "join_column": ["name", "year"]}]}'

# 3. Download the full join as CSV (auth required)
curl -H "Authorization: Bearer ${OPENDATA_API_KEY}" \
  'https://api.tryopendata.ai/v1/datasets/nces/naep/compose/download.csv?target=census/saipe&source_column=jurisdiction_name&join_column=name' \
  -o composed.csv

Search

The GET /v1/search endpoint supports three modes:

• keyword: Traditional full-text search with tsvector matching. Supports Google-style query syntax: quotes for phrases, - to exclude, OR for alternatives.
• semantic: Embedding-based similarity search for conceptual matching (e.g., "inflation data" finds CPI datasets).
• hybrid (default): Combines both using Reciprocal Rank Fusion (RRF). Best for most queries.

Sort options: relevance (default), recency, name, popularity (stars), trending (time-decayed activity), queries, downloads.

Filters: provider, format, category, status (defaults to "ready").

Time ranges (for trending/queries/downloads sort): today, week, month, year, all_time.

Autocomplete: GET /v1/search/suggest?q=con returns dataset names matching the prefix for typeahead.

All search results include graph intelligence fields (importance, bridge_score, community_id, community_label, graph_available). Graph scores contribute to search ranking via a multiplicative boost.

View results: Search may return dataset views alongside regular datasets. View results have result_type: "view", a view_name field, and a parent_ref linking to the parent dataset. Query views using colon syntax: FROM "provider/dataset:view_name".

Enriched Metadata

The GET /v1/datasets/{provider}/{dataset}/meta/enriched endpoint returns AI-enriched metadata including:

• Provider and dataset-level descriptions (short, long, layman, technical)
• Subject tags, geographic/temporal granularity
• Column metadata (display names, descriptions, aliases, semantic types)
• Methodology (structured bullets or summary text)
• Known limitations
• Canonical questions
• Shape classification and KPI snapshot
• Metadata coverage score (8 quality checks across 3 tiers)
• YAML-declared joins with measured coverage percentages

Chart Data

The GET /v1/datasets/{provider}/{dataset}/chart endpoint returns pre-aggregated chart data optimized for each dataset shape:

Downsampling (timeseries only): When raw data exceeds 500 points, the endpoint auto-buckets via date_trunc at the finest granularity that fits (week/month/quarter/year). Response includes downsampled: true, granularity, aggregation ("avg" or "count"), and raw_count. Returns 404 for tabular/text shapes.

Activity Feed

The GET /v1/datasets/{provider}/{dataset}/activity endpoint returns recent system events (enrichment, ingestion, schema changes) in reverse chronological order. Accepts ?limit= (1-50, default 20).

Graph Intelligence

Datasets are connected in a knowledge graph (Neo4j). Graph algorithms (PageRank, betweenness centrality, Leiden community detection) produce scores that surface in search rankings, dataset metadata, and related datasets.

On dataset metadata: Pass ?include_graph=true to /meta to get a graph block with importance, bridge_score, and community info.

Dataset-specific graph endpoints live under /v1/graph/datasets/{provider}/{dataset}/:

• stats - Graph-computed statistics (importance, bridge score, community, connection count)
• join-paths - Multi-hop join paths with configurable max_hops (1-3), min_confidence, and limit
• related - Blended structural + semantic related datasets
• neighbors - Direct 1-hop connections, filterable by edge_types (comma-separated, e.g., SIMILAR_TO,BELONGS_TO)
• schema-graph - Schema-level subgraph for D3 visualization with configurable depth (1-3)

Global graph endpoints live under /v1/graph/:

• communities - List communities with top datasets and dominant topics
• communities/{id}/datasets - Datasets in a community, sorted by importance
• bridges - Top bridge datasets by betweenness centrality
• subgraph - Seeded subgraph for graph explorer (accepts seed_type, seed_id, depth, limit). Dataset seeds use provider/slug format.
• entities/{type}/{id}/datasets - Datasets referencing a specific entity
• health - Graph connection status and sync info

All graph endpoints return 503 when Neo4j is unavailable. See graph.md for details.

Discovery

The GET /v1/discover endpoint returns datasets matching a natural language query, enriched with metadata tailored for LLM agents and programmatic integrations. Results include column schemas (with units, value ranges, display names), available views, canonical questions, methodology summaries, sample rows, and relevance scores. Unlike /v1/search, discover is authenticated and optimized for machine consumption rather than human browsing.

Batch discover: POST /v1/discover/batch accepts multiple queries in one call, deduplicates results, and returns per-query dataset references alongside the full metadata. See discover.md for details.

Reference Files