Query Dataset
socrata_query_datasetRead-onlyIdempotent
Execute a SoQL query against any dataset on any Socrata portal. Use the search parameter for quick full-text lookup, or combine select/where/group/having/order for full analytical control. Returns rows plus the assembled SoQL string so you can learn the pattern. Columns are referenced by API field name (field_name from socrata_get_dataset, e.g. cuisine_description), never the display label. All SODA 2.1 row values are strings even for numeric columns — check data_type from socrata_get_dataset to determine correct WHERE quoting: Number columns use bare literals (year=2023), Text columns use single-quoted strings (year='2023'). To enumerate distinct values, use select="col, count(*) as n" with group="col" and order="n DESC". When CANVAS_PROVIDER_TYPE=duckdb and rows fill limit, up to 50,000 matching rows spill to a DataCanvas table whatever the limit: list its columns with socrata_dataframe_describe, then run SQL with socrata_dataframe_query.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| group | No | SoQL GROUP BY clause over API field names (field_name from socrata_get_dataset). Requires an aggregate function in select. | |
| limit | No | Max rows to return (1–5000). Default 100. Use with offset for pagination. When the canvas is enabled and the page fills limit, up to 50,000 matching rows are staged on it whatever the limit — pass a small limit (e.g. 10) to stage a large match without a large inline page. | |
| order | No | SoQL ORDER BY clause over API field names or select aliases, e.g. "total_deaths DESC" or "date ASC". | |
| where | No | SoQL WHERE clause over API field names (field_name from socrata_get_dataset). Check column data_type there first — Number columns: year=2023, Text columns: year='2023'; an unquoted text value is read as a column name. Operators: =, !=, >, <, LIKE, IN(...), BETWEEN, IS NULL, starts_with(), contains(), AND, OR, NOT. | |
| domain | No | Portal the dataset lives on, as a bare hostname (e.g. data.cityofnewyork.us); URL forms like https://data.cityofnewyork.us/ are accepted and reduced to the host. Pass the domain from the same socrata_find_datasets result as dataset_id. Defaults to SOCRATA_DEFAULT_DOMAIN or data.seattle.gov, which is wrong for another portal’s ID. | |
| having | No | SoQL HAVING clause. Filters on aggregated results, e.g. count > 100. | |
| offset | No | Row offset for pagination. Default 0. | |
| search | No | Full-text search across all text columns ($q). For field-specific filtering, use where instead. | |
| select | No | SoQL SELECT clause — API field names (field_name from socrata_get_dataset, not display labels), aliases, aggregates: "state, sum(deaths) as total_deaths". Omit for all columns. | |
| canvas_id | No | Optional 10-char DataCanvas token from a prior socrata_query_dataset or socrata_dataframe_describe call. Omit on first call when CANVAS_PROVIDER_TYPE=duckdb to mint a fresh canvas. Large result sets spill here automatically. | |
| dataset_id | Yes | Four-by-four dataset ID (e.g. kzjm-xkqj). IDs are portal-scoped: take it from socrata_find_datasets together with that result’s domain. |
Output Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| cap | No | The row limit that was applied when capped. | |
| rows | No | Result rows. Scalar values are strings (SODA 2.1); geo/location columns return nested objects. Use column schema from socrata_get_dataset for type context. | |
| error | No | Present when the call failed. Absent on success. | |
| shown | No | Rows returned in this response when capped. | |
| domain | No | Portal hostname queried, normalized from the domain input. | |
| notice | No | Guidance when the query returned zero rows (review the SoQL or broaden the filter), or when rows filled the limit (how to page, and the staged table to query when the result spilled). Absent otherwise. | |
| canvas_id | No | DataCanvas token when results spilled (requires CANVAS_PROVIDER_TYPE=duckdb). Pass to socrata_dataframe_query to run SQL over the staged rows in table_name — a bounded copy of the matching set (up to 50,000 rows, reported in canvas_row_count), not the full set when total_count exceeds that cap. Page with offset to reach rows beyond it. | |
| row_count | No | Rows returned in this response. | |
| truncated | No | True when rows filled the limit — more rows may match (see total_count when present). Spills to canvas when enabled; table_name names the staged table. | |
| dataset_id | No | Dataset ID queried. | |
| table_name | No | Canvas table holding the staged rows; present when canvas_id is. Use it as the FROM target in socrata_dataframe_query SQL; list its columns with socrata_dataframe_describe. | |
| total_count | No | Total matching source rows when a plain row query is truncated (row_count < total_count). Absent when the full result fits and for grouped/aggregate queries (group set), where a source-row count would not describe the returned groups. | |
| assembled_query | No | SoQL clauses assembled for this request — useful for learning the syntax. | |
| canvas_row_count | No | Rows staged onto the DataCanvas — a bounded copy of the matching result set (capped at 50,000). Fewer than total_count when the match exceeds the cap. Present only when canvas_id is. |