Setting the file. One moment.
Subchapter 2.4
references/composition.mdMarkdown7 KBView on GitHub
Join two datasets via the API without writing SQL. Preview the result interactively, then download the full join as CSV.
/joinable/compose/preview (check column overlap, join quality)/compose/download.csv (auth required)GET /v1/datasets/{provider}/{dataset}/joinableAuth: Public (no auth required).
Returns datasets that can be joined with the given dataset, ranked by confidence. Filters out low-signal joins (date-only at low confidence, cross-community). Falls back to a small set of low-signal matches when the filter would otherwise return nothing.
curl 'https://api.tryopendata.ai/v1/datasets/nces/naep/joinable'{
"source_dataset": {
"provider": "nces",
"slug": "naep",
"name": "NAEP Scores"
},
"targets": [
{
"dataset_id": "uuid-here",
"provider": "census",
"slug": "saipe",
"name": "Small Area Income and Poverty Estimates",
"confidence": 0.85,
"join_type": "geographic",
"source_columns": ["jurisdiction_name"],
"target_columns": ["name"],
"same_community": true,
"target_row_count": 58000,
"hop_count": 1,
"low_signal": false
}
],
"used_fallback": false
}| Field | Type | Description |
|---|---|---|
dataset_id | UUID | Target dataset ID |
provider | string | Target provider slug |
slug | string | Target dataset slug |
name | string | Human-readable name |
confidence | float | Join confidence (0-1) |
join_type | string | Join relationship type (geographic, temporal, etc.) |
source_columns | string[] | Suggested columns in the source dataset |
target_columns | string[] | Suggested columns in the target dataset |
same_community | boolean | Whether both datasets are in the same graph community |
target_row_count | int or null | Row count of target dataset |
hop_count | int | Graph distance (always 1 for direct joins) |
low_signal | boolean | Whether this is a low-confidence fallback match |
When Neo4j is unavailable, returns an empty targets list (not a 503).
POST /v1/datasets/{provider}/{dataset}/compose/previewAuth: Optional. Anonymous users get 100 rows and 10 attempts per session. Authenticated users get 5000 rows.
Executes a LEFT JOIN between the base dataset and one target. Exactly one join spec per request.
{
"joins": [
{
"target": "census/saipe",
"source_column": "jurisdiction_name",
"join_column": "name"
}
],
"columns": null
}Composite keys: source_column and join_column accept either a string or an array of strings. For multi-column joins, pass arrays of equal length:
{
"joins": [
{
"target": "census/saipe",
"source_column": ["state", "year"],
"join_column": ["name", "year"]
}
]
}| Field | Type | Description |
|---|---|---|
joins | array (exactly 1) | Single join spec |
joins[].target | string | Target dataset path (provider/slug) |
joins[].source_column | string or string[] | Column(s) in the base dataset to join on |
joins[].join_column | string or string[] | Column(s) in the target dataset to join on |
columns | string[] or null | Columns to project. Null = auto-select |
{
"data": [{"jurisdiction_name": "Illinois", "year": 2024, "score": 267, "saipe_poverty_rate": 11.2}],
"columns": ["jurisdiction_name", "year", "score", "saipe_poverty_rate"],
"column_types": ["string", "integer", "integer", "float"],
"column_sources": {
"jurisdiction_name": "naep",
"year": "naep",
"score": "naep",
"saipe_poverty_rate": "saipe"
},
"populated_rows": 45,
"cardinality_warning": false,
"effective_joins": [{"target": "census/saipe", "source_column": "jurisdiction_name", "join_column": "name"}]
}| Field | Type | Description |
|---|---|---|
data | object[] | Result rows |
columns | string[] | Column names |
column_types | string[] | Column types |
column_sources | object | Maps each column to the dataset it came from |
populated_rows | int | Rows where the join matched (join quality signal) |
cardinality_warning | boolean | True when join fan-out looks suspicious |
effective_joins | object[] | Echo of the join spec used |
For large base datasets (>10k rows), the API runs a COUNT(*) before executing the preview:
CARDINALITY_EXCEEDEDcardinality_warning: true and caps rows at 100k| Status | When |
|---|---|
| 400 | Invalid join column, lookup error, or cardinality exceeded |
| 404 | Dataset not found or has no data |
| 408 | Query timeout |
| 429 | Anonymous session ran out of preview attempts |
GET /v1/datasets/{provider}/{dataset}/compose/download.csvAuth: Required (returns 401 if not authenticated).
Streams the composed join as CSV. Mirrors the shape of the preview endpoint.
| Param | Type | Required | Description |
|---|---|---|---|
target | string | yes | Target dataset path (e.g., census/saipe) |
source_column | string | yes | Column in the base dataset to join on |
join_column | string | yes | Column in the target dataset to join on |
curl -H "Authorization: Bearer od_live_..." \
'https://api.tryopendata.ai/v1/datasets/nces/naep/compose/download.csv?target=census/saipe&source_column=jurisdiction_name&join_column=name' \
-o composed.csv| Limit | Value |
|---|---|
| Max rows | 100,000 |
| Rate limit | 10 downloads/hour per user |
| Concurrent streams | 5 (process-wide) |
| Wall-clock timeout | 300s |
| DuckDB query timeout | 60s |
| Status | When |
|---|---|
| 401 | Not authenticated |
| 404 | Dataset not found or has no data |
| 429 | Rate limit exceeded |
| 503 | At capacity (includes Retry-After: 30 header) |
Base dataset columns keep their original names. Joined columns are prefixed with the target dataset slug: {slug}_{column}. For example, joining with census/saipe produces columns like saipe_poverty_rate, saipe_median_income.
| Composition endpoints | POST /v1/query (SQL) | |
|---|---|---|
| Auth | Preview: optional. Download: required | Required |
| Complexity | Single join, no SQL knowledge needed | Arbitrary SQL, multiple joins |
| Cardinality protection | Built-in preflight checks | None (hits timeout/row limits) |
| Output | JSON preview or CSV stream | JSON (columnar or objects) |
| Use case | Quick enrichment, data exploration | Complex analysis, custom aggregations |