Querying Feature Services: Having Clause

Welcome to the first installation of the blog series “Querying Feature Services”. This series of articles will dive into the details and provide examples to describe what’s available for querying feature services. This article will focus on the having clause; a query operation you can do on the layer resource through the REST API as of ArcGIS 10.6.1.

The having clause partners with groupByForStatistics and outStatistics query parameters to allow extra powerful queries using aggregate functions. The where clause, geometry, and time parameters can be used with having to further limit your query results. It’s supported on all feature services except those hosted in a spatiotemporal big data store. If you’re not sure whether your feature service or map service supports querying with the having clause, you’re in luck because there is a property in the service’s layer resource called supportsHavingClause (I know, right?). Now, supportsHavingClause is true if the layer supports querying with the having clause. If you don’t see the property, or the property is false, you’re not able to use having. Wait a second, how do you use having?

I mentioned previously that having is used with groupByForStatistics, outStatistics, and sometimes the where clause, geometry and time parameters, but I didn’t tell you how they might be used together. Let’s set up a scenario to help explain. You’re looking for a hotel in Los Angeles between August 16th and August 18th and you have data that contains information about the date, price per night, and the overall customer rating. You published your data as a feature service and you want to do some queries using the having clause to narrow down your choices (whoa, what a coincidence!). Suppose you want to find a hotel where the average price per nice is less than $200, but you only want hotels with a minimum customer rating greater than or equal to 4/5 using the subset of data below.

Composing the Query

Let’s break that query down into its parts, starting with the where clause. The where clause will need to limit your query results to all hotels in Los Angeles between August 16th and August 18th, and may look like this: city_name = 'Los Angeles' and date between '08/16/2018' and '08/18/2018'.

Alright, now that the search is limited to hotels in Los Angeles within your date range, you can enhance your results by calculating the average price per night by using the outStatistics parameter. Using outStatistics requires a specific syntax containing statisticType, onStatisticField, and outStatisticFieldName.  Your input will look like this: [{"statisticType": "AVG", "onStatisticField": "price", "outStatisticFieldName": "AvgPrice"}]. It’s important to note that outStatistics can’t be used without groupByFieldsForStatistics. This means that you have to specify the field for which to calculate the average price per night. Since you’re looking for hotels, you’ll use hotel_name.

So, by now you have all the hotels in Los Angeles and their average price per night. To narrow it down further and make sure you only get hotels with an average price of less than $200 and a customer rating greater than or equal to 4, you have to use the having clause. Using the having clause also requires a specific syntax containing an aggregate function. Your having clause will look like this: avg(price) < 200 and min(customer_rating) >= 4.

It’s important to note that any supported aggregate function or comparison operator can be used in the having clause.

Now let’s see this in the layer query resource:

Note: This layout may be different depending on what feature service you’re working with.

The query returned three results:

This example covered finding a hotel with an average price and minimum rating. These represent aggregate functions. You aren’t limited to average and minimum though; there are seven types of aggregate functions you can make use of in both the having clause and outStatistics parameters: MIN, MAX, AVG, SUM, STDDEV, COUNT, and VAR.

The having clause is an important and powerful tool for getting the most out of your data. For more information on querying using the feature service layer resource, visit this page. For more information on layer properties, visit here.