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.

POST /v1/datasets/nces/naep/query
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"}'
Response
{
"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:

BeforeUsing data alias
SELECT * FROM data
WHERE year >= 2020
ORDER BY year DESC
AfterUsing qualified name
SELECT * FROM "nces/naep"
WHERE year >= 2020
ORDER 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:

BeforeQuery params: average score by subject
GET /v1/datasets/nces/naep
?aggregate=avg(score)
&group_by=subject
AfterSQL: same thing, but you can go further
POST /v1/datasets/nces/naep/query
SELECT subject,
ROUND(AVG(score), 1) as avg_score,
COUNT(*) as sample_size,
MIN(score) as lowest,
MAX(score) as highest
FROM data
GROUP 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

Average score by state for the most recent year
SELECT jurisdiction, ROUND(AVG(score), 1) as avg_score
FROM data
WHERE year = (SELECT MAX(year) FROM data)
GROUP BY jurisdiction
ORDER BY avg_score DESC

Window functions

Year-over-year GDP change
SELECT
country_name,
year,
gdp_per_capita,
gdp_per_capita - LAG(gdp_per_capita) OVER (
PARTITION BY country_name ORDER BY year
) as yoy_change
FROM data
WHERE country_name = 'United States'
ORDER BY year DESC

Top-N per group with CTEs

Top 3 scores per subject
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY subject ORDER BY score DESC
) as rn
FROM data
WHERE 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 *.

Walk a category tree from its root
WITH RECURSIVE tree AS (
SELECT id, parent_id, name FROM data WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, c.name
FROM 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

Percentage of jurisdictions above a threshold
SELECT
jurisdiction,
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 pct
FROM data
GROUP BY jurisdiction
ORDER 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).

Parameterized query
{
"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

POST body
{
"sql": "SELECT * FROM data WHERE country = ? AND year >= ?",
"params": ["United States", 2020],
"timeout_ms": 5000,
"row_limit": 1000
}
FieldTypeDefaultDescription
sqlstringrequiredThe SQL query to execute
paramsany[]nullPositional bind parameters for ? placeholders
timeout_msinteger20,000Query timeout (max 30,000ms)
row_limitinteger10,000Max rows returned (max 10,000)

Response body

Response
{
"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.

CategoryFunctions
AggregateCOUNT, SUM, AVG, MIN, MAX, ARRAY_AGG, STRING_AGG, COUNT_IF, SUM_IF, AVG_IF, BOOL_AND, BOOL_OR, MEDIAN, MODE, STDDEV, VARIANCE
MathABS, CEIL, FLOOR, ROUND, TRUNC, SQRT, POWER, EXP, LN, LOG, SIGN, GREATEST, LEAST
StringCONCAT, 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/TimeMAKE_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 handlingCOALESCE, NULLIF
Type castingCAST, TRY_CAST, TYPEOF
WindowROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, PERCENT_RANK, CUME_DIST
ConditionalCASE WHEN ... THEN ... ELSE ... END, IF, IIF
ListLIST_VALUE, LIST_AGGREGATE, LIST_SORT, LIST_DISTINCT, LIST_CONTAINS, LIST_FILTER, LIST_TRANSFORM, ARRAY_LENGTH, UNNEST
OtherMD5, 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

CategoryBlockedWhy
Data modificationINSERT, UPDATE, DELETEQueries are read-only. Use the API to modify data.
Schema changesCREATE, DROP, ALTERYou can't create or modify tables, views, or indexes.
File I/OFile read/write functionsQueries can only read the dataset table. No filesystem access.
NetworkHTTP and network functionsNetwork access is not available from queries.
System introspectionSystem catalog queriesMetadata tables and system catalogs are not accessible.
ConfigurationPRAGMA, SET, INSTALL, LOADConfiguration changes are not permitted.
Multiple statementsSemicolons between queriesOne SELECT statement per request. No batching.

Resource limits

LimitValue
Timeout5s default, 10s max
Row limit10,000 rows
Memory512 MB per query
Recursive CTE depth64 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.

400 - Unsupported function
{
"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."
}
}
400 - Blocked statement type
{
"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."
}
}
400 - Execution error (bad column name, type mismatch, etc.)
{
"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."
}
}
408 - Query timeout
{
"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:

POST /v1/query
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:

BeforeDot notation
SELECT * FROM census.state_populations
WHERE year = 2023
AfterSlash notation (quoted)
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

LimitValue
Max datasets per query5
Combined parquet size150 MB
Timeout5s default, 10s max
Row limit10,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

POST /v1/query
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"
}'
Response
{
"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

Search datasets

Search for datasets or run quick actions