Querying Data

Once you find a dataset, you need to get the right slice of data out of it. This guide covers every query parameter the API supports, from basic filtering to server-side aggregation.

Filtering

Filtering lets you ask for only the rows you care about. Instead of fetching thousands of rows and filtering client-side, tell the server what you want and get back just the matching data.

Simple equality

The most basic filter matches a column to an exact value. Wrap the column name in filter[]:

GET /v1/datasets/census/state-populations?filter[year]=2023&limit=5

This returns only rows where year equals 2023. The value is automatically coerced to the right type based on the column schema, so you don't need to worry about quoting numbers.

Comparison operators

For anything beyond exact match, add an operator inside a second set of brackets:

# Rows where year is 2020 or later
GET /v1/datasets/census/state-populations?filter[year][gte]=2020&limit=5

# Rows where population is over 10 million
GET /v1/datasets/census/state-populations?filter[population][gt]=10000000&limit=5

Multiple values with IN

The in operator is handy when you want rows matching any of several values. Separate values with commas:

GET /v1/datasets/census/state-populations?filter[state][in]=California,Texas,New York&limit=10

Combining filters

Stack multiple filters and they all apply (AND logic). This example gets rows where the year is 2023 and the population is over 5 million:

GET /v1/datasets/census/state-populations?filter[year]=2023&filter[population][gt]=5000000

Filter keys and column names

Filter keys accept either the raw column name or its display name, so filter[Metro Area] and filter[metro_area] filter the same column. JSON responses always use raw column names; display names appear in /meta and as the headers of CSV and XLSX downloads.

Filter errors

A filter never gets silently ignored. An unknown column, a malformed filter[...] key, or a non-numeric value on a numeric column returns a 400. For an unknown column, the error names the column and suggests close matches:

{
  "detail": {
    "code": "INVALID_PARAMETER",
    "message": "Invalid filter column: 'yr'. Column does not exist in schema.",
    "details": {
      "parameter": "filter",
      "invalid_column": "yr",
      "valid_values": ["year", "state", "population"]
    },
    "suggestions": [
      { "action": "did_you_mean", "title": "Did you mean", "matches": ["year"] }
    ]
  }
}

Field selection

By default, queries return all columns. Use fields to request only what you need. This reduces response size and speeds things up:

GET /v1/datasets/census/state-populations?fields=state,population&limit=5

Column names are validated against the schema. If you ask for a column that doesn't exist, you'll get an error with the list of available columns.

Sorting

Use the sort parameter to control row ordering. Prefix a column name with - for descending order:

# Highest population first
GET /v1/datasets/census/state-populations?sort=-population&limit=5

# Sort by multiple columns (year descending, then state ascending)
GET /v1/datasets/census/state-populations?sort=-year,state&limit=10

Pagination

The API supports two pagination strategies. Use whichever fits your use case.

Offset pagination

The simplest approach. Set limit (max 1000) and offset to page through results:

# First page
GET /v1/datasets/census/state-populations?limit=20

# Second page
GET /v1/datasets/census/state-populations?limit=20&offset=20

# Third page
GET /v1/datasets/census/state-populations?limit=20&offset=40

The response includes total_rows so you know how many pages exist. Use this to calculate how many pages are available at your current page size.

Cursor pagination

For deep result sets, cursor pagination is more efficient than large offsets. Instead of skipping N rows, you continue from where you left off:

# First request (no cursor)
GET /v1/datasets/census/state-populations?limit=20

# The response includes next_cursor:
# { "columns": [...], "rows": [...], "next_cursor": "eyJvZmZzZXQiOjIwfQ==" }

# Use it to get the next page
GET /v1/datasets/census/state-populations?limit=20&cursor=eyJvZmZzZXQiOjIwfQ==

Cursor and offset are mutually exclusive. You can't use both in the same request. When there are no more pages, next_cursor will be null in the response. Cursor pagination is especially useful when working with views that produce large result sets.

Aggregation

Instead of fetching raw rows and computing stats client-side, you can ask the server to do it. Aggregation runs at the database level, so it handles millions of rows efficiently.

Basic aggregation

Use the aggregate parameter with one or more functions:

# Count all rows
GET /v1/datasets/census/state-populations?aggregate=count(*)

# Multiple aggregations
GET /v1/datasets/census/state-populations?aggregate=count(*),avg(population),max(population)

{
  "columns": ["count", "avg_population", "max_population"],
  "rows": [
    [2750, 6500000, 39538223]
  ]
}

Group by

Combine aggregate with group_by to get stats per group:

GET /v1/datasets/census/state-populations?aggregate=count(*),avg(population)&group_by=year&sort=-year&limit=5

{
  "columns": ["year", "count", "avg_population"],
  "rows": [
    [2023, 50, 6700000],
    [2022, 50, 6650000],
    [2021, 50, 6600000],
    [2020, 50, 6550000],
    [2019, 50, 6500000]
  ]
}

Supported functions

Aggregation results use auto-generated column names: count for count(*), and function_column for everything else (e.g., avg_population, max_value).

Column introspection

Before writing filters or aggregations, it helps to know what columns exist and what kind of data they contain. The columns endpoint gives you that:

GET /v1/datasets/census/state-populations/columns

{
  "columns": [
    {
      "name": "state",
      "type": "string",
      "distinct_count": 50,
      "null_count": 0,
      "sample_values": ["California", "Texas", "Florida"]
    },
    {
      "name": "year",
      "type": "integer",
      "distinct_count": 55,
      "null_count": 0,
      "min": "1970",
      "max": "2024"
    },
    {
      "name": "population",
      "type": "integer",
      "distinct_count": 2750,
      "null_count": 0,
      "min": "226167",
      "max": "39538223"
    }
  ],
  "row_count": 2750
}

You can also get details for a single column:

GET /v1/datasets/census/state-populations/columns/state

This is especially useful for low-cardinality columns (like states or categories) where you want to see all possible values before filtering.

Debug mode

If a query isn't returning what you expect, add debug=true to see what's actually happening under the hood:

GET /v1/datasets/census/state-populations?filter[year]=2023&sort=-population&limit=3&debug=true

{
  "columns": [...],
  "rows": [...],
  "total_rows": 50,
  "debug": {
    "query": {
      "filters": [{"column": "year", "operator": "eq", "value": 2023}],
      "sort": "-population",
      "fields": null,
      "aggregate": null,
      "view": null
    },
    "sql": "SELECT * FROM read_parquet('***') WHERE year = 2023 ORDER BY population DESC LIMIT 3"
  }
}

The debug object shows two things: the parsed query parameters (so you can verify your filters were understood correctly) and the generated SQL that ran against DuckDB. File paths in the SQL are masked for security.

Putting it all together

All of these parameters work together. Here's an example that combines filtering, field selection, sorting, and pagination in one request:

GET /v1/datasets/census/state-populations?filter[year][gte]=2020&fields=state,year,population&sort=-population&limit=10

And here's a query using aggregation with filters and grouping:

GET /v1/datasets/census/state-populations?filter[year][gte]=2020&aggregate=avg(population),count(*)&group_by=year&sort=-year

Source attribution

Datasets that combine multiple source files track where each row came from. By default these columns are hidden, but you can expose them by adding include_sources=true to any data query:

GET /v1/datasets/census/state-populations?include_sources=true&limit=3

{
  "columns": ["state", "population", "_source_url", "_source_page"],
  "rows": [
    ["California", 39538223, "https://www2.census.gov/programs-surveys/popest/tables/2020-2023/state/totals/NST-EST2023-POP.xlsx", "NST-EST2023-POP"]
  ]
}

Two system columns appear when this is enabled:

• _source_url - The URL the row was originally fetched from
• _source_page - The sheet name, file name, or page within the source (for multi-sheet Excel files, multi-file archives, etc.)

This is useful when you need to trace a specific value back to its original source, or when a dataset aggregates data from several files and you want to filter by origin.

Related

• API Reference - Full endpoint documentation
• Views - Transform data with built-in and custom views
• Data Export - Download in CSV, Excel, JSON
• Search - Find datasets to query
• Rate Limits - API usage limits