Views
Views transform how data comes back from a query. Instead of getting raw rows and processing them yourself, apply a view and get exactly what you need.
Why raw data stays raw
When OpenData ingests a dataset, we keep it as close to the original source as possible. We don't rename columns, cast types, or reformat values during ingestion. This is intentional.
Transforming data at ingestion time creates real problems. If the source publishes updated files next year with slightly different formatting, brittle transforms break. If someone questions whether the numbers on OpenData match the source, there's no clean audit trail when you've modified the data on the way in. And if two users need the same dataset shaped differently, ingestion-time transforms can only serve one of them.
Views solve this. The raw data stays untouched (your source of truth), and views apply transformations at query time. You get clean, shaped output without anyone modifying the underlying data. Different users can define different views on the same dataset, each tailored to their use case.
The problem views solve
Here's the Bureau of Labor Statistics Consumer Price Index. This is what US inflation data actually looks like when you query it raw:
{"columns": ["series_id", "year", "period", "value"],"rows": [["CUSR0000SA0", 2025, "M06", "323.195"],["CUSR0000SA0E", 2025, "M06", "311.284"],["CUSR0000SA0L1", 2025, "M06", "289.732"]]}
What is CUSR0000SA0? What does M06 mean? Is 323.195 high or low? What's SA0L1compared to SA0E? You'd need to cross-reference three separate lookup tables just to understand one row.
Now the same query with a view:
{"series_id": "CUSR0000SA0","year": 2025,"period": "M06","value": "323.195"}
{"date": "2025-06-01","cpi_value": 323.195,"inflation_rate": 2.3,"area_name": "U.S. city average","item_name": "All items","seasonal": "Seasonally Adjusted","series_title": "All items in U.S. city average..."}
The enriched view constructs a proper date from the year and period code, joins three lookup tables to decode the series ID into human-readable names, computes the year-over-year inflation rate using window functions, and casts the value from a string to a number. All at query time, without modifying the source data.
It gets better. The national view goes further: it filters to just US headline inflation, computes the year-over-year inflation rate, and gives you chart-ready output:
{"columns": ["date", "cpi_value", "inflation_rate"],"rows": [["2025-06-01", 323.195, 2.3],["2025-05-01", 322.267, 2.4],["2025-04-01", 321.836, 2.3]]}
Three fields. Date, index value, inflation rate. That's everything you need to chart national inflation, and nothing you don't. The raw dataset has hundreds of thousands of rows across dozens of metro areas, item categories, and seasonal adjustments. The national view cuts through all of that and hands you exactly the data you came for: data over the wire is tiny to deliver this.
From an opaque series ID to "inflation was 2.3% in June 2025" in one query parameter. That's the gap views close.
Using views
Add ?view=<name> to any dataset query:
# Most recent value per entityGET /v1/datasets/worldbank/gdp-per-capita?view=latest# Ranked by value (leaderboard)GET /v1/datasets/worldbank/gdp-per-capita?view=ranked# Dataset-specific view with joinsGET /v1/datasets/bls/cpi-u/data-0-current?view=enriched
Built-in views
These work on any dataset with the right column types. The system detects entity, time, and value columns automatically.
latest
One row per entity, showing only the most recent observation.
[{"country_name": "Aruba", "gdp_per_capita": 6767.56, "year": 1986},{"country_name": "Aruba", "gdp_per_capita": 8244.05, "year": 1987},{"country_name": "Aruba", "gdp_per_capita": 10056.26, "year": 1988},...{"country_name": "Aruba", "gdp_per_capita": 32948.02, "year": 2023},{"country_name": "Aruba", "gdp_per_capita": 34567.81, "year": 2024}]
[{"country_name": "Aruba", "gdp_per_capita": 34567.81, "year": 2024}]
Use for: Current-state dashboards, data exports, snapshots.
ranked
Most recent value per entity, sorted by value descending with a rank column added.
Use for: Leaderboards, top-N lists, comparative analysis.
timeseries
Projects entity, time, and value columns. Sorted by time descending so recent data comes first.
Use for: Charts, trend visualization.
Dataset-specific views
Some datasets include custom views that join related tables, compute new columns, or filter to common subsets. These are defined by dataset maintainers for that specific data.
Example: BLS CPI data
Raw CPI data uses codes for series, periods, and areas. The enriched view joins lookup tables and computes a proper date:
[{"series_id": "CUSR0000SA0", "year": 2025, "period": "M12", "value": "326.030"},{"series_id": "CUSR0000SA0", "year": 2025, "period": "M11", "value": "325.031"},{"series_id": "CUSR0000SA0E", "year": 2025, "period": "M12", "value": "314.218"},{"series_id": "CUSR0000SA0L1", "year": 2025, "period": "M12", "value": "293.447"}]
[{"date": "2025-12-01", "value": "331.593", "area_name": "Houston-The Woodlands", "item_name": "Food away from home"},{"date": "2025-12-01", "value": "134.975", "area_name": "Middle Atlantic", "item_name": "Services less medical care"},{"date": "2025-12-01", "value": "220.806", "area_name": "U.S. city average", "item_name": "Candy and chewing gum"},{"date": "2025-12-01", "value": "141.839", "area_name": "West North Central", "item_name": "Food away from home"}]
The enriched view computes date from year + period code (M01-M12), and joins area and item lookup tables so you get human-readable names instead of cryptic codes.
Filtered views
The national view filters to US headline inflation (series CUSR0000SA0) and includes year-over-year inflation rate:
{"columns": ["date", "cpi_value", "inflation_rate"],"rows": [["2025-12-01", 326.030, 2.9],["2025-11-01", 325.031, 2.7],["2025-10-01", 324.368, 2.6],["2025-09-01", 323.527, 2.4]]}
Checking available views
Each dataset's metadata lists its available views:
GET /v1/datasets/bls/cpi-u/data-0-current/meta
{"views": ["timeseries", "latest", "ranked", "enriched", "national", "core", "by-category", "by-area", "with-series"]}
Combining views with query parameters
Views work alongside filters, field selection, and pagination. The view transforms the output; parameters shape the input.
# Filter + viewGET /v1/datasets/worldbank/gdp-per-capita?view=latest&filter[year]=2024# Field selection + viewGET /v1/datasets/worldbank/gdp-per-capita?view=latest&fields=country_name,gdp_per_capita# Pagination + viewGET /v1/datasets/worldbank/gdp-per-capita?view=latest&limit=10&offset=20
Disabling views
Some datasets have a default view that applies automatically. If you want the raw, untransformed data instead, pass ?view=flat:
# Get raw rows with no view appliedGET /v1/datasets/bls/cpi-u/data-0-current?view=flat
This is useful when you need every column in its original form, or when you want to apply your own grouping and sorting client-side.
Inline transforms
You don't always need a named view. You can group and nest data on the fly using query parameters. This is handy when you want structured, hierarchical output without someone having pre-built a view for your use case.
The relevant parameters:
group_by=category- group rows by a columnnest_fields=item_id,value- which columns to nest inside each groupnest_field=items- name of the nested array (defaults to "items")sort_nested=-date- sort within nested groups (prefix with-for descending)
GET /v1/datasets/bls/cpi-u/data-0-current?group_by=area_code&nest_fields=year,period,value&nest_field=observations&limit=2
{"columns": ["area_code", "observations"],"rows": [["0000", [{"year": 2025, "period": "M12", "value": "326.030"},{"year": 2025, "period": "M11", "value": "325.031"}]],["0100", [{"year": 2025, "period": "M12", "value": "287.614"},{"year": 2025, "period": "M11", "value": "286.883"}]]]}
Expanding references
Many datasets use codes instead of human-readable names. The expand parameter joins lookup tables so you get both.
For example, BLS CPI data stores an area_code like "0000". If you pass ?expand=area, the response adds area_name from the lookup table:
GET /v1/datasets/bls/cpi-u/data-0-current?expand=area&limit=2
{"columns": ["series_id", "area_code", "area_name", "value"],"rows": [["CUSR0000SA0", "0000", "U.S. city average", "326.030"],["CUSR0000SA0E", "0000", "U.S. city average", "314.218"]]}
Pass multiple fields as a comma-separated list: ?expand=area,item.
View aliases
Some dataset-specific views define aliases, which are friendly parameter names that map to underlying column names. For example, a view might let you write ?state=California instead of ?filter[state_code]=CA. Check the dataset metadata to see if any aliases are available.
Performance
Built-in views (latest, ranked, timeseries) and dataset-specific views execute at the SQL level and handle millions of rows efficiently.
Creating views
Views aren't just for reading. You can save your own and share them with others. Think of it like saving a spreadsheet filter that anyone can reuse: you do the work of shaping the data once, and everyone else gets the clean result for free.
What goes into a view
A view is a saved configuration that controls what data comes back and how it's shaped. You can combine any of these:
- Column selection - Pick which columns to include (hide the noise, show what matters)
- Filters - Pre-apply conditions so users don't have to add them every time
- Sorting - Set a default sort order
- Computed columns - Add new columns derived from existing ones using SQL expressions
- Joins - Pull in columns from other datasets (like replacing area codes with area names)
- Grouping - Aggregate rows by a column with functions like
COUNT,AVG,SUM
Creating a view in the app
The Workbench has two modes for building a view: Builder (point-and-click) and SQL (write a query directly). Both produce the same result: a saved, reusable view that anyone can query.
Builder mode
Pick a dataset, then shape it visually:
- Select columns - Toggle which columns to include, drag to reorder
- Add filters - Set conditions like "city equals CHICAGO" or "year between 2018 and 2025"
- Group and aggregate - Group by a column and pick aggregation functions (sum, average, count)
- Sort and limit - Choose a sort column and cap the row count
- Save - Give it a name, and it becomes a reusable view anyone can query
The workbench shows a live preview as you build, so you can see exactly what the output looks like before saving.
SQL mode
If you're comfortable with SQL, switch to SQL mode and write your query directly. Run it to see results, then save it to turn it into a view. The raw SQL gets stored with the view, so loading it later drops you right back into the editor with your query ready to run.
SQL views are useful for complex transformations that are hard to express with the builder: window functions, CTEs, conditional aggregation, or anything that benefits from writing the query by hand.
Beta: The Workbench is under active development and will change significantly. The core concepts (column selection, filters, SQL queries, saving) are stable, but the interface is evolving.
Saving a view
Run a query in the Workbench, then click Save or press Cmd+S (Ctrl+S on Windows and Linux). A dialog titled "Save view to your profile" opens with three fields:
- Name (required) - A human-readable title. The slug auto-derives from the name as you type, so you usually don't have to think about it.
- Slug - The short identifier that goes in the URL. The dialog checks availability live as you type. Edit it by hand any time the auto-generated one isn't what you want.
- Description (optional) - A sentence or two explaining what the view shows.
A slug is lowercase letters, numbers, dashes, and underscores, and can't start with a digit. It has to be unique across all of your views (not just within one dataset), and a handful of words are reserved so they don't collide with URL paths: latest, versions, meta, download, diff, publish, fork, data, json, and csv.
Draft or published?
The dialog has one more control that decides who can see your view: the publish toggle.
- Toggle off (Save as draft) - Only you can see the view. It won't show up in search or to anyone else. Drafts stay fully editable, including renaming the slug, so this is the safe place to iterate.
- Toggle on (Publish at 1.0.0) - Your view gets a stable
@1.0.0endpoint and appears on your public profile. The button reads Save and publish 1.0.0.
If you're still figuring out the query, save a draft. You can always flip the toggle and publish later.
Where your view lives
Every published view gets its own URL under your profile. The @ before your username is part of the path:
# Latest published version (omitting the version resolves to latest)/@alice/views/cpi-ex-shelter# A pinned version/@alice/views/[email protected]# Version history/@alice/views/cpi-ex-shelter/versions
Leave the version off and you always get the newest published version. Pin a version with @1.4.0 when you need the numbers to stay put. The /versions page shows the full history of a view.
Because slugs are unique per author (not per dataset), the same slug always points at the same view of yours no matter which dataset it draws from.
A published view also still works the two ways you'd expect. You can apply it as a query parameter on the dataset it's built from:
GET /v1/datasets/isbe/report-card?view=chicago-high-schools
And it has a stable API endpoint of its own, keyed by your username and slug (this is the URL prefix the save dialog shows you):
GET https://api.tryopendata.ai/v1/views/@alice/cpi-ex-shelter
Publishing new versions
Drafts are loose. Published views are stable. That tradeoff shows up the moment you publish: the slug locks and can't be renamed again. People (and their bookmarks, and their scripts) are relying on that address now, so it stops moving.
You can still edit a published view. Change the name, the description, or the SQL, and your edits land in a working copy. Nothing public changes until you click Publish new version and pick how big the change is: a patch (1.0.0 to 1.0.1) for a small fix, a minor bump (1.0.0 to 1.1.0) for new columns or logic, or a major bump (1.0.0 to 2.0.0) for a breaking reshape. That releases your edits as a new pinned version. Anyone reading the bare slug rolls forward to it; anyone pinned to @1.0.0 keeps the old numbers.
Every version you publish shows up in the history at /@username/views/slug/versions.
Forking someone else's view
Found a view you like but want to tweak it? Load it in the Workbench, make your changes, and save. Since it isn't yours to edit, the save dialog switches to Fork to your profile and saves a copy under your own username. The description pre-fills with the lineage (something like Forked from "National CPI" by alice) so the origin is clear. From there it's your view: edit it, publish it, version it like any other.
Cross-dataset SQL views
A view backed by SQL isn't tied to a single dataset. The query can join up to 5 datasets (the same limit as the SQL sandbox), and the SQL itself is the source of truth: OpenData reads the query to figure out which datasets the view references, so you don't anchor it to one provider/dataset.
Reference each dataset by its qualified name in double quotes:
SELECTpop.state,pop.population,cpi.value AS price_indexFROM "census/state-populations" popJOIN "bls/cpi-u" cpiON pop.year = cpi.yearWHERE pop.year = 2023ORDER BY pop.population DESC
For the full set of cross-dataset rules and limits (which datasets can be joined, how qualified names resolve, and the 5-dataset cap), see the cross-dataset section of the SQL Query documentation.
Working with views via the API
Everything you can do in the Workbench, you can do over REST. Views have a full API for the whole lifecycle:
- Create a view (draft or published)
- Publish a new version and browse version history
- Fork another user's published view
- Delete a view you own
Rather than duplicate the endpoint shapes here (where they'd drift out of date), the request bodies and responses live in the API Reference, which you can call interactively. Cross-dataset SQL rules are documented in the SQL Query documentation. And if your view uses computed columns, the Functions Reference lists the helper functions you can use in their expressions.
One thing the API docs don't cover is how a ?view=name gets resolved when names overlap. The system checks dataset-specific views first (the ones maintainers define, like enriched on BLS CPI data), then built-in views like latest and ranked, and finally user-created views. So a maintainer or built-in view always wins a name collision against a user view.
A real-world example
The Illinois Report Card dataset on OpenData has over a dozen views that show how powerful this system gets. The raw data has 28 years of school records with column names like student_enrollment_black_or_african_american and values suppressed with * for privacy. Here's what the schools view does to clean that up:
- Filters to school-level records only (the raw data also has district and statewide aggregate rows)
- Casts string values to proper numbers, handling
*suppressed values as null - Normalizes inconsistent school type labels across 28 years into clean categories (Elementary, Middle, High School, Charter)
- Computes derived columns: poverty level buckets, majority demographic, a diversity index, and student-to-teacher ratios
{"school_type": "REGULAR - HIGH SCHOOL","student_enrollment": "2847","student_enrollment_low_income": "97.3","student_enrollment_white": "*","student_enrollment_black_or_african_american": "46.2","student_enrollment_hispanic_or_latino": "51.3"}
{"school_category": "High School","enrollment": 2847,"pct_low_income": 97.3,"pct_white": null,"pct_black": 46.2,"pct_hispanic": 51.3,"poverty_level": "Very High Poverty","majority_demographic": "No Majority","diversity_index": 0.505}
Same data, but the view does all the cleanup and enrichment server-side. Anyone querying this dataset gets clean, typed, analysis-ready output without writing any transformation code.
Related
- SQL Sandbox - Build and save views in the browser
- SQL Query - Syntax, functions, and cross-dataset rules
- API Reference - Query parameters and the full views API
- Authentication - Required for creating views
- Data Export - Download in CSV, Excel, JSON
- Querying Guide - Filtering, aggregation, and more query parameters