topic Re: SQL query 10.9 not working in ArcGIS REST APIs and Services Questions

SQL query 10.9 not working

MatthewDriscoll — Fri, 19 Nov 2021 17:12:09 GMT

According to the documentation as of 10.9, hosted feature services support SQL statements.

https://developers.arcgis.com/rest/services-reference/enterprise/query-feature-service-layer-.html

Hosted feature services on a relational data store support SQL expression for the outFields parameter when supportsOutFieldSqlExpression, under advancedQueryCapabilities, is true. Hosted feature services in ArcGIS Online already support this functionality.

https://developers.arcgis.com/javascript/latest/api-reference/esri-rest-support-Query.html

If specifying outFields as expressions on a feature service-based FeatureLayer, the service capabilities advancedQueryCapabilities.supportsOutFieldSQLExpression and useStandardizedQueries must both be true.

But a very simply expression does not work. PartyName LIKE '%driscoll%',

on this layer: https://www.jfksgis.us/server/rest/services/Hosted/Parcel_hosted/FeatureServer/0/

The SQL query works fine in Pro and for Online services: https://services1.arcgis.com/tpr1e2h7Rmn9C5oc/ArcGIS/rest/services/JFKS_GIS_ParcelOrion_Public_V4/FeatureServer/0

So is this not actually supported in server 10.9?

Re: SQL query 10.9 not working

jcarlson — Fri, 19 Nov 2021 19:22:55 GMT

Seems to work just fine, but keep in mind that these queries are two different things:

PartyName LIKE '%driscoll%'
PartyName LIKE '%Driscoll%'

Unfortunately, we can't use ILIKE to ignore the case of the input string. Using a bracketed set of characters, like "[Dd]riscoll" doesn't appear to work here either. I guess you'd have to combine two queries and put an OR between them.

Re: SQL query 10.9 not working

MatthewDriscoll — Fri, 19 Nov 2021 19:35:58 GMT

Thanks. This is a simplified problem, I have many attributes I need to query. ESRI is logging it as an enhancement instead of a bug, even though it is a basic very simple sql statement, so it might be a long time until it gets fixed. They need to update their documentation that supportsOutFieldSQLExpression is not supported currently with rest endpoints.

Re: SQL query 10.9 not working

jcarlson — Fri, 19 Nov 2021 19:38:01 GMT

Personally, if I've got a lot of attributes to sift through, I just bring the entire field into a pandas dataframe and use regular expressions. It's much easier to define a really precise, adaptable regex expression than wrangle the SQL into submission.