How do I request LIKE queries in Socrata SODA API?
For example, I take all the Socrata data and ETL (extract, transform, and load) it into BigQuery
For example, start with
https://data.cityofnewyork.us/Housing-Development/DOB-Permit-Issuance/ipu4-2q9a
And after transforming it, upload it into BigQuery, or mysql or any sql database
https://bigquery.cloud.google.com/table/personal-real-estate:nyc.dob_permit_issuance (requires google login, i believe, but is basically free or almost free to query)
Once in BigQuery or any sql database I can issue LIKE queries, etc.
The question is how do I run LIKE queries via the SODA API.
How do I transform a timestamp field to the YEAR only for a group by query. For example, if the recorded date column is a timestamp and I want to see the number of permits grouped by year how do I do this via the SODA API?
The 'Like' functionality seems to work now via the API, so long as you use the new version of the API - which was released about September 2015.
For example, geneorama's example in his answer now works with the updated API: https://data.cityofchicago.org/resource/xqx5-8hwx.json?$where=legal_name%20like%20%27%25INC%25%27 (notice that the identifier 9j92-xfdk has been updated to xqxf-8hwx).
Similarly with the new API you can do a date truncation to group records by year, in your example like this: https://data.cityofnewyork.us/resource/83x8-shf7.json?$select=date_trunc_y%28issuance_date%29%20as%20year,count%28issuance_date%29&$group=year (slightly strangely the resulting year categories are not displayed truncated, but it shows the count for 2013, 2014, etc). You'll notice again that the resource identifier is changed from the one in the question to use the new API identifier (83x8-shf7 instead of ipu4-2q9a).