500 Error When Using $where, $group, and $select all at once

Example query:

https://data.cityofnewyork.us/resource/8h5j-fqxa.json?%24where=(street_number+%3D+%271855%27)+AND+(street_name+%3D+%27EAST+15%27)&%24select=document_id&%24group=document_id

$where = (street_number = '1855') AND (street_name = 'EAST 15') $select = document_id $group = document_id

Hi @marklar423 - it seems like the part of your query that is having issues is the $group. What are you trying to do so maybe I or someone else can help?

For example, are you trying to get a count grouped by document_id?

Hey Mark, thanks for the quick reply. What I'm really trying to do is get a distinct list of document_ids, so I figured $group would be a handy way to do that. Unfortunately the above query gives me a 500 error in return.

I'm not using an aggregate function in the query - perhaps that's the cause of the error? Is an aggregate function mandatory when using $group?

Hey @marklar423 - Yes, I do think an aggregate function is required with $group but when I try https://data.cityofnewyork.us/resource/8h5j-fqxa.json?$select=document_id,count(document_id)&$group=document_id it doesnt work either.

Sorry but it looks like there might be an anomaly with that data set.. $groups I throw at it do error out. I am sure @chrismetcalf and the rest of our team will take a look in short order.

In the mean time, you can do something like https://data.cityofnewyork.us/resource/8h5j-fqxa.json?$where=(street_name+=+%27EAST+15%27)&$select=document_id and then de-dupe document_ids in your programming language of choice. Is that a suitable workaround?

Yeah, that's basically what I've implemented on my end. I guess I'll just wait until the team can get to it, before implementing the ideal solution.

Thanks for your help!

On Feb 2, 2015, at 9:21 AM, Mark Silverberg [email protected] wrote:

Hey @marklar423 - Yes, I do think an aggregate function is required with $group but when I try https://data.cityofnewyork.us/resource/8h5j-fqxa.json?$select=document_id,count(document_id)&$group=document_id it doesnt work either.

Sorry but it looks like there might be an anomaly with that data set.. $groups I throw at it do error out. I am sure @chrismetcalf and the rest of our team will take a look in short order.

In the mean time, you can do something like https://data.cityofnewyork.us/resource/8h5j-fqxa.json?$where=(street_name+=+%27EAST+15%27)&$select=document_id and then de-dupe document_ids in your programming language of choice. Is that a suitable workaround?

— Reply to this email directly or view it on GitHub.

@marklar423 - Please try https://data.cityofnewyork.us/resource/8h5j-fqxa.json?$where=(street_name+=+%27EAST+15%27)&$select=document_id,count(document_id)&$$pipe=false&$group=document_id -- the $$pipe=false has been explained to me as necessary for a bug we have identified in our stack.

Thanks and let me know how it goes!