I am working with the NYC crime dataset and I notice that the offense type for homicides is MURDER & NON-NEGL. MANSLAUGHTE. (Yes, that’s “manslaughte” without the “r”.) However, when I try to filter for this using the SODA API, I get errors:
https://data.cityofnewyork.us/resource/hyij-8hr7.json?$where=offense='MURDER & NON-NEGL. MANSLAUGHTE'{ "error" : true, "message" : "Unrecognized arguments [ NON-NEGL. MANSLAUGHTE']" }
It seems to me that the ampersand in the string is escaping.
Note that I tried to use the like=‘MURDER’ command to avoid needing to type the ampersand, so for instance querying https://data.cityofnewyork.us/resource/hyij-8hr7.json?$where=offense like 'MURDER', but this gives me the error
{ "error" : true, "message" : "Error: function #LIKE is not defined in SoQL.: Error: function #LIKE is not defined in SoQL.” }
This must mean that the NYC dataset is using SoQL. v. 2.0 and not 2.1.
So in conclusion, how can I query for homicides? Is there a way to either (a) properly escape the ampersand, or (b) search for substrings?
Thanks for any assistance on either of these requests.
You're correct, hyij-8hr7 is the SODA 2.0 endpoint for that dataset. However, a 2.1 endpoint also exists: https://dev.socrata.com/foundry/data.cityofnewyork.us/e4qk-cpnv
Using the 2.1 endpoint, you can escape the ampersand as a URL-encoded %26: $where=offense='MURDER %26 NON-NEGL. MANSLAUGHTE'. Full URL, fully-escaped:
GET https://data.cityofnewyork.us/resource/e4qk-cpnv.json?$where=offense%3D%27MURDER%20%26%20NON-NEGL.%20MANSLAUGHTE%27
With the 2.1 endpoint, you can also use LIKE to do SQL-style substring searches or $q to use the full-text search index.