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 the server has a canvas and more rows match past a full page, 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. Pass stage false to return the page alone, without staging.
Input 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–50,000). One call returns at most 5,000 rows: a larger limit is served as 5,000, with a notice naming the offset that continues when more rows match. Default 100. Use with offset for pagination. When the server has a canvas and more rows match past a full page, 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, or stage false to return the page without staging. | |
| order | No | SoQL ORDER BY clause over API field names or select aliases, e.g. "total_deaths DESC" or "date ASC". | |
| stage | No | Stage the matching rows on a canvas when more rows match past this page and the server has a canvas. Default true. Pass false to return the page alone, skipping the staging request; canvas_id and table_name then go unused. | |
| 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 to mint a fresh canvas. Large result sets spill here automatically unless stage is false. | |
| 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. | |
| table_name | No | Name for the canvas table a spill stages: letters, digits, and underscores, starting with a letter or underscore, 63 characters at most; lowercased, and not a SQL keyword such as select or order. Defaults to a name derived from dataset_id (ijzp_q8t2_rows). A spill under a name the canvas already holds replaces that table, so give each extract its own name, with the same canvas_id, to keep several on one canvas. |
Output 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; past offset 0, whether the offset passed the end of the match), or when more rows match past this page: how to page, naming the requested and served limit when a limit above the 5,000-row per-call cap was served as 5,000; the staged table to query when the result spilled, and the earlier table of that name it replaced; or why nothing was staged, naming a canvas_id or table_name that stage false left unused. Absent otherwise. | |
| canvas_id | No | DataCanvas token when results spilled. 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 more rows match past this page: total_count exceeds offset + row_count, or rows filled the limit and no count was made. Spills to canvas when enabled and stage is not false; 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 | Rows the query matches, in the same unit as rows: groups for a grouped query, distinct values for a distinct select. Counted when rows fill limit or a page past offset 0 comes back empty; present when the match holds more rows than this response. Absent otherwise, and when the count failed or ran past its 15-second bound. | |
| 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. |