1

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?

Ralph Yozzo
  • 1,086
  • 13
  • 25

3 Answers3

2

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).

JoshM
  • 36
  • 5
0

Unfortunately, you can't currently perform LIKE queries through SoQL. However, that's something we look forward to being able to provide for more datasets once we roll out some back-end changes that we're working on.

The same also goes for date truncation at this point :-\

chrismetcalf
  • 1,556
  • 1
  • 8
  • 7
  • Does this still not work? I've been experimenting with it, and I'm getting 'unrecognized arguments' with curl. – Ando Jan 16 '19 at 18:54
0

According to the documentation LIKE is now supported, however I can't get it to work on my example. http://dev.socrata.com/docs/functions/like.html

It should let you use LIKE for text columns in conjunction with $where. There are two wild card characters _ and % which respectively mean "match any single character" and "match any characters".

The documented example works: https://open.whitehouse.gov/resource/9j92-xfdk.json?$where=position_title like '%25ASSISTANT%25'

But I can't get it to work outside of the example, e.g.: https://data.cityofchicago.org/resource/r5kz-chrr.json?$where=legal_name like '%25INC%25'

geneorama
  • 3,620
  • 4
  • 30
  • 41