The Socrata APIs provide rich query functionality through a query language we call the “Socrata Query Language” or “SoQL”. As its name might suggest, it borrows heavily from Structured Query Language (SQL), used by many relational database systems. Its paradigms should be familiar to most developers who have previously worked with SQL, and are easy to learn for those who are new to it.
SoQL statements are broken into “parameters” similar to clauses in SQL statements. Each clause can be expressed either directly as a URL parameter or as a SoQL statement. If a parameter is not specified, then the default is used. Click each parameter name for more details:
| Parameter | Description | Default | In $query |
|---|---|---|---|
$select | The set of columns to be returned, similar to a SELECT in SQL | All columns, equivalent to $select=* | SELECT |
$where | Filters the rows to be returned, similar to WHERE | No filter | WHERE |
$order | Column to order results on, similar to ORDER BY in SQL | Unspecified order | ORDER BY |
$group | Column to group results on, similar to GROUP BY in SQL | No grouping | GROUP BY |
$having | Filters the rows that result from an aggregation, similar to HAVING | No filter | HAVING |
$limit | Maximum number of results to return | 1000 (2.0 endpoints: maximum of 50,000; 2.1: unlimited ») | LIMIT |
$offset | Offset count into the results to start at, used for paging | 0 | OFFSET |
$q | Performs a full text search for a value. | No search | N/A |
$query | A full SoQL query string, all as one parameter | N/A | N/A |
$$bom | Prepends a UTF-8 Byte Order Mark to the beginning of CSV output | false | N/A |
Note that for equality comparisons, the $where clause can be replaced with using the column name as the query parameter. See filtering for more details.
These parameters can then be directly added to the API endpoint. For example, here is how you would query the USGS Earthquakes datasets for quakes of greater than 3.0 on the Richter scale:
https://soda.demo.socrata.com/resource/4tka-6guv.json?$where=magnitude > 3.0
In examples, we will leave the parameters as is, but it is best to URL Encode your parameters to ensure they are parsed correctly.