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?

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL query 10.9 not working

MatthewDriscoll

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?

Find more posts tagged with

Accepted answers

jcarlson

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.

All comments

jcarlson

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.

MatthewDriscoll

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.

jcarlson

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

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.