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. All SODA 2.1 row values are strings even for numeric columns — check dataType 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 the limit, results spill to a DataCanvas table for SQL-based analysis.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| group | No | SoQL GROUP BY clause. Requires an aggregate function in select. | |
| limit | No | Max rows to return (1–5000). Default 100. Use with offset for pagination. | |
| order | No | SoQL ORDER BY clause, e.g. "total_deaths DESC" or "date ASC". | |
| where | No | SoQL WHERE clause. Check column dataType from socrata_get_dataset first — Number columns: year=2023, Text columns: year='2023'. Operators: =, !=, >, <, LIKE, IN(...), BETWEEN, IS NULL, starts_with(), contains(), AND, OR, NOT. | |
| domain | No | Portal domain (e.g. data.seattle.gov). Defaults to SOCRATA_DEFAULT_DOMAIN or data.seattle.gov. | |
| 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 — column names, aliases, aggregates: "state, sum(deaths) as total_deaths". Omit for all columns. | |
| canvas_id | No | Optional 10-char DataCanvas token from a prior 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). Obtain from socrata_find_datasets. |
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 domain queried. | |
| notice | No | Guidance when the query returned zero rows — suggests narrowing or reviewing the SoQL. Absent on non-empty result sets. | |
| 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 — 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. | |
| dataset_id | No | Dataset ID queried. | |
| 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. |