SQL Query
Structured query parameters handle most use cases. But sometimes you need a GROUP BY with a window function, a subquery, or a CASE expression. The SQL query endpoint gives you that power.
Posting SQL against a dataset
Send a POST request with your SQL. The dataset is available as a table called data. You get back JSON with the results, column names, and timing info.
curl -X POST 'https://api.tryopendata.ai/v1/datasets/nces/naep/query' \-H 'Authorization: Bearer YOUR_API_KEY' \-H 'Content-Type: application/json' \-d '{"sql": "SELECT subject, ROUND(AVG(score), 1) as avg_score FROM data GROUP BY subject"}'
{"data": [{"subject": "Mathematics", "avg_score": 267.5},{"subject": "Reading", "avg_score": 254.3}],"columns": ["subject", "avg_score"],"row_count": 2,"execution_time_ms": 42,"truncated": false}
Authentication required. You need an API key (Authorization: Bearer header) or an active session. Structured query parameters on GET endpoints don't require auth, but SQL does.
The data table
Every dataset is exposed as a table called data. You can also use the qualified name "provider/dataset" if you prefer:
SELECT * FROM dataWHERE year >= 2020ORDER BY year DESC
SELECT * FROM "nces/naep"WHERE year >= 2020ORDER BY year DESC
Both return the same data.
When SQL beats query parameters
Structured params cover filtering, sorting, and basic aggregation. SQL is for everything else:
GET /v1/datasets/nces/naep?aggregate=avg(score)&group_by=subject
POST /v1/datasets/nces/naep/querySELECT subject,ROUND(AVG(score), 1) as avg_score,COUNT(*) as sample_size,MIN(score) as lowest,MAX(score) as highestFROM dataGROUP BY subject
With query params, you get one aggregate at a time. With SQL, you can compute multiple aggregates, add conditions with CASE/WHEN, use window functions, and write subqueries.
Examples
Filter and aggregate
SELECT jurisdiction, ROUND(AVG(score), 1) as avg_scoreFROM dataWHERE year = (SELECT MAX(year) FROM data)GROUP BY jurisdictionORDER BY avg_score DESC
Window functions
SELECTcountry_name,year,gdp_per_capita,gdp_per_capita - LAG(gdp_per_capita) OVER (PARTITION BY country_name ORDER BY year) as yoy_changeFROM dataWHERE country_name = 'United States'ORDER BY year DESC
Top-N per group with CTEs
WITH ranked AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) as rnFROM dataWHERE year = 2024)SELECT * FROM ranked WHERE rn <= 3
Recursive CTEs
WITH RECURSIVE is supported for walking hierarchies (parent/child rollups, category trees, org charts). Queries are automatically depth-bounded to 64 recursive iterations: an over-deep or cyclic recursion stops at the ceiling instead of running until the timeout. The bounding is invisible — no extra columns appear in your results, even with an outer SELECT *.
WITH RECURSIVE tree AS (SELECT id, parent_id, name FROM data WHERE parent_id IS NULLUNION ALLSELECT c.id, c.parent_id, c.nameFROM data c JOIN tree t ON c.parent_id = t.id)SELECT * FROM tree
A few shapes are rejected with a clear error because they can't be depth-bounded safely: USING KEY, multiple recursive branches, more than one self-reference in the recursive branch, ORDER BY or LIMIT inside the CTE body, GROUP BY, HAVING, DISTINCT, or QUALIFY in the recursive branch, and a bare * in the recursive branch's select list (list the columns explicitly). Apply ordering and limits in the outer query instead. The identifier _od_depth and names starting with __od_ are reserved for the internal depth bounding.
Conditional aggregation
SELECTjurisdiction,SUM(CASE WHEN score >= 250 THEN 1 ELSE 0 END) as above,COUNT(*) as total,ROUND(100.0 * SUM(CASE WHEN score >= 250 THEN 1 ELSE 0 END) / COUNT(*), 1) as pctFROM dataGROUP BY jurisdictionORDER BY pct DESC
Parameterized queries
Use ? placeholders with a params array to avoid string quoting headaches. Each ? in the SQL is replaced with the corresponding value from params (in order).
{"sql": "SELECT * FROM data WHERE country IN (?, ?, ?) AND year >= ?","params": ["United States", "Japan", "Germany", 2020]}
This eliminates the triple-nested escaping problem when building queries from code or shell. The SQL string contains no quotes at all. Values live in a plain JSON array. The number of ? placeholders must exactly match the length of params.
Request and response format
Request body
{"sql": "SELECT * FROM data WHERE country = ? AND year >= ?","params": ["United States", 2020],"timeout_ms": 5000,"row_limit": 1000}
| Field | Type | Default | Description |
|---|---|---|---|
sql | string | required | The SQL query to execute |
params | any[] | null | Positional bind parameters for ? placeholders |
timeout_ms | integer | 20,000 | Query timeout (max 30,000ms) |
row_limit | integer | 10,000 | Max rows returned (max 10,000) |
Response body
{"data": [{"year": 2024, "score": 267}],"columns": ["year", "score"],"row_count": 1,"execution_time_ms": 23,"truncated": false}
When truncated is true, your query produced more rows than the limit. Add a LIMIT clause to your SQL or increase row_limit (up to 10,000).
Allowed functions
SQL queries are validated against a function allowlist before execution. Any function not on this list will be rejected. This is a security measure: it ensures queries can only read and transform data, never access the filesystem, make network requests, or modify anything.
| Category | Functions |
|---|---|
| Aggregate | COUNT, SUM, AVG, MIN, MAX, ARRAY_AGG, STRING_AGG, COUNT_IF, SUM_IF, AVG_IF, BOOL_AND, BOOL_OR, MEDIAN, MODE, STDDEV, VARIANCE |
| Math | ABS, CEIL, FLOOR, ROUND, TRUNC, SQRT, POWER, EXP, LN, LOG, SIGN, GREATEST, LEAST |
| String | CONCAT, CONCAT_WS, SUBSTRING, LENGTH, UPPER, LOWER, TRIM, LTRIM, RTRIM, LPAD, RPAD, REPLACE, SPLIT_PART, LEFT, RIGHT, REVERSE, CONTAINS, STARTS_WITH, ENDS_WITH, POSITION, REGEXP_EXTRACT, REGEXP_REPLACE, REGEXP_MATCHES |
| Date/Time | MAKE_DATE, DATE_TRUNC, DATE_PART, EXTRACT, DATE_DIFF, DATE_ADD, DATE_SUB, STRFTIME, STRPTIME, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, NOW, CURRENT_DATE, AGE, EPOCH, EPOCH_MS |
| Null handling | COALESCE, NULLIF |
| Type casting | CAST, TRY_CAST, TYPEOF |
| Window | ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, PERCENT_RANK, CUME_DIST |
| Conditional | CASE WHEN ... THEN ... ELSE ... END, IF, IIF |
| List | LIST_VALUE, LIST_AGGREGATE, LIST_SORT, LIST_DISTINCT, LIST_CONTAINS, LIST_FILTER, LIST_TRANSFORM, ARRAY_LENGTH, UNNEST |
| Other | MD5, HASH, FORMAT, PRINTF |
Standard SQL operators (+, -, *, /, %, ||, LIKE, ILIKE, IN, BETWEEN, IS NULL) work as expected and don't require allowlisting.
Limitations
Queries run in a sandboxed, read-only environment with resource limits to prevent runaway queries from affecting other users.
What's not supported
| Category | Blocked | Why |
|---|---|---|
| Data modification | INSERT, UPDATE, DELETE | Queries are read-only. Use the API to modify data. |
| Schema changes | CREATE, DROP, ALTER | You can't create or modify tables, views, or indexes. |
| File I/O | File read/write functions | Queries can only read the dataset table. No filesystem access. |
| Network | HTTP and network functions | Network access is not available from queries. |
| System introspection | System catalog queries | Metadata tables and system catalogs are not accessible. |
| Configuration | PRAGMA, SET, INSTALL, LOAD | Configuration changes are not permitted. |
| Multiple statements | Semicolons between queries | One SELECT statement per request. No batching. |
Resource limits
| Limit | Value |
|---|---|
| Timeout | 5s default, 10s max |
| Row limit | 10,000 rows |
| Memory | 512 MB per query |
| Recursive CTE depth | 64 iterations |
Error handling
Errors return structured JSON with a code, a descriptive message, and a hint with recovery guidance when available. The errors array (for validation failures) lists each specific issue found.
{"detail": {"message": "SQL validation failed","code": "SQL_VALIDATION_ERROR","errors": ["Function 'READ_CSV' is not supported. File and network I/O functions are disabled for security. Only SELECT queries against the dataset table are allowed."],"hint": "Only read-only SELECT queries with allowed functions are supported. See /docs/sql-query for the full list of allowed functions and limitations."}}
{"detail": {"message": "SQL validation failed","code": "SQL_VALIDATION_ERROR","errors": ["DELETE statements are not supported. Only read-only SELECT queries are allowed. Data modification (INSERT, UPDATE, DELETE) and schema changes (CREATE, DROP, ALTER) are not permitted."],"hint": "Only read-only SELECT queries with allowed functions are supported. See /docs/sql-query for the full list of allowed functions and limitations."}}
{"detail": {"message": "Binder Error: Referenced column 'nonexistent' not found","code": "SQL_EXECUTION_ERROR","hint": "Check your SQL syntax and column names. Use GET .../meta to see available columns."}}
{"detail": {"message": "Query exceeded time limit","hint": "Try adding filters or a LIMIT clause to reduce the work"}}
A 404 means the dataset doesn't exist or hasn't been ingested yet (no Parquet files).
Cross-dataset queries
Need to join or compare data across multiple datasets? The cross-dataset endpoint lets you reference up to 5 datasets in a single SQL query.
Endpoint
Cross-dataset queries use a different endpoint than single-dataset queries:
curl -X POST 'https://api.tryopendata.ai/v1/query' \-H 'Authorization: Bearer YOUR_API_KEY' \-H 'Content-Type: application/json' \-d '{"sql": "SELECT a.state, a.population, b.gdp FROM census.state_populations a JOIN bea.gdp b ON a.state = b.state WHERE a.year = 2023"}'
Dataset notation
Reference datasets using dot notation (provider.dataset) or slash notation ("provider/dataset"). Both work the same way:
SELECT * FROM census.state_populationsWHERE year = 2023
SELECT * FROM "census/state_populations"WHERE year = 2023
Dot notation is more natural for SQL. Slash notation matches the URL path format you see elsewhere in the API. Pick whichever reads better for your query.
Limits
| Limit | Value |
|---|---|
| Max datasets per query | 5 |
| Combined parquet size | 150 MB |
| Timeout | 5s default, 10s max |
| Row limit | 10,000 rows |
If the combined size of all referenced datasets exceeds 150 MB, the API returns a 400 error with a breakdown showing the size of each dataset.
Example: joining two datasets
curl -X POST 'https://api.tryopendata.ai/v1/query' \-H 'Authorization: Bearer YOUR_API_KEY' \-H 'Content-Type: application/json' \-d '{"sql": "SELECT p.state, p.population, e.total_employment FROM census.state_populations p JOIN bls.employment e ON p.state = e.state WHERE p.year = 2023 LIMIT 10"}'
{"data": [{"state": "California", "population": 39538223, "total_employment": 18542000},{"state": "Texas", "population": 29145505, "total_employment": 13890000}],"columns": ["state", "population", "total_employment"],"row_count": 2,"execution_time_ms": 87,"truncated": false}
The request body format is identical to single-dataset queries (sql, timeout_ms, row_limit). The only difference is the endpoint path and how you reference tables. CTEs work as expected and won't be confused with dataset references.
Related
- API Reference - Structured query parameters
- Views - Server-side transforms without writing SQL
- Functions Reference - Helper functions for computed columns in views
- Authentication - Getting an API key