socrata_query
Retrieve rows from state and local government Socrata/SODA open-data portals, applying filters, pagination, and SoQL aggregates to get exact totals or top-N vendor rankings without manual summing.
Instructions
Query rows from an allowlisted Socrata/SODA open-data portal (keyless; ~a dozen US state portals + USAC E-rate on one identical API — state spend/checkbook/contract/vendor-payment datasets). State procurement mirrors: NY ehig-g5x3, NJ ubnu-tqu7, WA s8d5-pj78, MA cthru.data.socrata.com pegc-naaa (~49M payment rows). Full map: read resource samgov://data-map/state-local. Input domain (curated allowlist enum — the SSRF host guard), datasetId (4x4, from socrata_discover_datasets), optional SoQL select/where/order/q, limit (≤1000, def 100), offset, withTotal (def true). AGGREGATES: for a grand total pass select='sum(amount)' with a where filter; for top-N vendors pass select='vendor_name, sum(amount) as total' with order='total DESC' — these return the final answer directly, NOT a page to manually sum. HONESTY: SODA's row response has no total, so a count(*) companion supplies an exact totalAvailable; if it fails the rows still return with totalAvailable:null + a note (hasMore is then inferred from page-fill, never a false complete). Genuine-empty ⇒ complete:true/total:0; an outage/400/404 THROWS (never a fake empty). Value fields are strings.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| q | No | Optional SoQL $q full-text search across the row. | |
| limit | No | Rows per page ($limit), 1..1000, default 100. | |
| order | No | Optional SoQL $order, e.g. 'amount DESC'. | |
| where | No | Optional SoQL $where filter, e.g. "fiscal_year='2024' AND amount>1000". A bad column ⇒ upstream HTTP 400 ⇒ invalid_input (surfaced, never silent). | |
| domain | Yes | Which allowlisted Socrata portal to query (the SSRF host allowlist — no free host). Jurisdiction of non-obvious hosts: cthru.data.socrata.com=MASSACHUSETTS statewide (CTHRU); atlanta.data.socrata.com=Atlanta GA; controllerdata.lacity.org+data.lacity.org=Los Angeles; www.dallasopendata.com=Dallas TX; data.brla.gov=Baton Rouge LA; data.kcmo.org=Kansas City MO; data.cstx.gov=College Station TX; data.weho.org=West Hollywood CA; opendata.usac.org+datahub.usac.org=federal USAC E-rate. data.colorado.gov's procurement data is CITY OF DENVER, not CO state. | |
| offset | No | 0-based row offset ($offset) for pagination, default 0. | |
| select | No | Optional SoQL $select (column projection or aggregate). For a TOTAL: 'sum(amount)' (add a where for vendor/fiscal-year filter). For a TOP-N ranking: 'vendor_name, sum(amount) as total' — pair with order='total DESC'. Any SoQL function call (sum/count/avg/min/max) or 'distinct' activates aggregate mode: totalAvailable becomes null (no raw-row total for aggregates) and the count(*) companion is skipped. NEVER sum rows from one page to get a total — always use an aggregate select. | |
| datasetId | Yes | The dataset's Socrata 4x4 id, e.g. 'kwxv-fwze' (from socrata_discover_datasets). Exactly [a-z0-9]{4}-[a-z0-9]{4} (9 chars; no surrounding whitespace). | |
| withTotal | No | true (default) ⇒ issue a count(*) companion query so totalAvailable is exact. false ⇒ skip it (one fewer request); totalAvailable is null and a note discloses results may be truncated at $limit. |