Filtering

[nickevansuk]

Requirements

To allow for collections of resources to be filtered within the API, a standard approach to filtering/querying collections should be used.

The approach must be compatible with the PropertyValueSpecification for query string properties.

The approach should cover:

1) The following for numeric and date types:

• Range filters for dates and values (e.g. remainingAttendeeCapacity>2 or startDate>2018-01-01T12:00:00)
• Component filters for dates, to only search on time component or date component (e.g. startDate>12:00)

2) The following for enum types and controlled vocabularies:

• Standard filtering using schema.org enums (e.g. genderRestriction=Male)
• Filtering using SKOS concepts (e.g. activity=Yoga)
• Restriction to set of enums (e.g. genderRestriction=Male or genderRestriction=Mixed)

3) The following regarding query structure:

• AND and OR nesting definition, to remove ambiguity
• Nested properties to be included in the filter, e.g. offers.price

4) Boolean values as primitive types, including null values (for undefined properties):

• isAccessibleForFree=true,null

5) Geo search

• Including a filter for radial and boundingBox search

For example, for both of the endpoints below:

• /sessions?
• /facility-uses?

References

• https://specs.openstack.org/openstack/api-wg/guidelines/pagination_filter_sort.html
• http://apistylebook.com/design/topics/collection-filtering
• https://cloud.google.com/apis/design/design_patterns#list_sub-collections
• https://github.com/Haufe-Lexware/api-style-guide/blob/master/filtering-sorting-field-selection-and-paging/filtering-sorting-field-selection-and-paging.md#filtering
• https://github.com/paypal/api-standards/blob/master/api-style-guide.md#time-selection
• https://docs.google.com/document/d/1-vs8bFt7iEZ1I3V6t8FYt5KlkS1ZYiJIFDlsnivH9bA/edit#
• https://docs.microsoft.com/en-us/azure/architecture/best-practices/api-design
• https://api-platform.com/docs/core/filters/
• https://blog.octo.com/en/design-a-rest-api/
• https://blog.octo.com/wp-content/uploads/2014/12/OCTO-Refcard_API_Design_EN_3.0.pdf
• http://www.odata.org/documentation/odata-version-2-0/uri-conventions/

Discounted options

A good discussion of available options is available here: https://www.moesif.com/blog/technical/api-design/REST-API-Design-Filtering-Sorting-and-Pagination/

• hydra:search is "deliberately fuzzy", so too generic to be applicable
• The common option of just using standard enum filters ?type=japanese,chinese&rating=4,5&days=sunday is not expressive enough
• The option of using OData style ?$filter=price lt 10.00 was overly expressive, and implied a greater complexity of query capability than is likely available in most cases. It also requires the use of a complex $filter syntax even for simple cases, where most APIs implement something similar to ?status=open
• The option of using {property_name}_from and {property_name}_to query range was also too simplistic, and not easily extensible.
• For filter functions on specific properties, creating objects such aslocation.geo.radialFilter.latitude={latitude}&location.geo.radial.longitude={longitude}&location.geo.radial.radius={radius} intrudes on the properties namespace and extends the length of the GET request unnecessarily. Using the operator pattern consistently here resolves this e.g. location.geo=radial:{location.geo:radial.latitude},{location.geo:radial.longitude},{location.geo:radial.radius}

Proposal with Examples

Of all the options investigated, the OpenStack approach appears to be the most user-friendly, as it strikes the best balance between extensibility and familiarity.

The following prefixed operators are allowed: in, nin, neq, gt, gte, lt, and lte e.g. ?size=gt:8. A comma separated list as an operand is synonymous with "in".

• Standard filtering using schema.org enums (e.g. genderRestriction=Male)
  • ?genderRestriction=Female (only the string following the # is required from e.g. ID https://www.openactive.io/ns#Female)
• Filtering using SKOS concepts (e.g. activity=Yoga)
  • ?activity=d5f34cb1-35c0-46e5-ad6d-181f77274640 (only the string following the # is required from e.g. ID https://www.openactive.io/activity-list/#d5f34cb1-35c0-46e5-ad6d-181f77274640)
• Restriction to set of enums (e.g. genderRestriction=Male or genderRestriction=Mixed)
  • ?genderRestriction=in:Female,Male (only the string following the # is required from e.g. ID https://www.openactive.io/ns#Female)
• Range filters for dates and values (e.g. remainingAttendeeCapacity>2 or startDate>2018-01-01T12:00:00)
  • ?remainingAttendeeCapacity=gt:2
  • ?startDate=gt:2018-01-01T12:00:00Z
  • ?startDate=gt:2018-01-01T12:00:00Z&startDate=lt:2018-03-01T12:00:00Z
• Component filters for dates, to only search on time component or date component (e.g. startDate>12:00)
  • ?startDate=gt:10:00Z&startDate=lt:14:00Z
  • ?startDate=gte:2018-01-01&startDate=lte:2018-01-01 is the same as ?startDate=2018-01-01 - all will return results for startDate any time on 2018-01-01
• Boolean values as primitive types
  • ?isAccessibleForFree=true
  • ?isAccessibleForFree=in:true,null
• AND and OR nesting definition, to remove ambiguity
  • AND represented by multiple params: ?startDate=gt:10:00Z&startDate=lt:14:00Z
  • OR represented by specific operators: ?genderRestriction=in:Female,Male
  • AND's connect each parameter, with ORs used within specific operator, hence ?startDate=gt:10:00Z&startDate=lt:14:00Z&genderRestriction=in:Female,Male is parsed as startDate>10:00 AND startDate<14:00 AND (genderRestriction = Female OR genderRestriction)
• Nested properties to be included in the filter, e.g. offers.price
  • Use dot notation to access properties?slot.startDate=gt:10:00Z&slot.startDate=lt:14:00Z
• Because operators are only available on numeric, date and enum types, and not strings, there is no issue of literal values matching operators.
• Boolean values as primitive types, including null values (for undefined properties):
  • isAccessibleForFree=true,null
  • Available values: true, false, null
• null is a reserved value for all types, and can be used to filter on where a specific property is undefined
• Including a filter of latitude, longitude, and radius
  • geo objects are afforded specific search objects:
    • location.geo=radial:{location.geo:radial.latitude},{location.geo:radial.longitude},{location.geo:radial.radius}
    • location.geo=radial:{location.geo:radial.latitude},{location.geo:radial.longitude} (automatic radius)
    • location.geo=boundingBox:{location.geo:boundingBox.topLeft.latitude},{location.geo:boundingBox.topLeft.longitude},{location.geo:boundingBox.bottomRight.latitude},{location.geo:boundingBox.bottomRight.longitude}

[lukehesluke]

@nickevansuk this looks interesting and very powerful

In a much simpler query parameter specification without a syntax like this, every single value would require a new separate query parameter e.g. startDate__gt=2018-... and startDate__lt=2018-... rather than startDate=lt:2018-... and startDate=gt:2018-...

Cons to the much simpler approach:

• Requires many more query parameters
• Consistency between concepts like "greater than", "less than", etc is in the naming of parameters rather than a language

Pros:

• A query specification (e.g. using OpenAPI / Swagger) can easily be made for this
• That above query specification would wholly define all possible interaction with the API. For example, I don't expect APIs would be supporting activity=gt:d5f34cb1-35c0-46e5-ad6d-181f77274640. "Greater than" is a silly concept to apply to a UUID. In the simpler specification, activity__gt simply wouldn't be defined
• For the implementer of the API, this is simpler to set up. Each query param value is either accepted as a string or coerced into a number or boolean

---

My main concern with the proposal is that it would be difficult to spec comprehensively with widely available tools like Swagger ("type": "string" doesn't really capture that the string can be "2", "gt:2", "in:2,3", etc). It would also be more effort to implement than something simpler and I suspect would leave developers or project managers spending time on deciding if "remainingAttendeeCapacity" needs the "in" operator or not 😝

---

To me, the qualities of a "simple" spec (simple to spec, simple to implement, simple to test) are:

• Query parameter value is to be interpreted as one of a very limited number of native data types (e.g.: string, integer or boolean)
• Any query parameter key should unambiguously take only one type of value rather than many alternatives (e.g. startDate__gt=2018-07-17T13:00:00Z or startDate__time__gt=13:00Z rather than startDate for either)

[nickevansuk]

Totally see where you're coming from @lukehesluke - in the interests of following at least one of the existing conventions, could that be expressed with square brackets instead (e.g startDate[gt]=2018-07-17T13:00:00Z) as per https://www.moesif.com/blog/technical/api-design/REST-API-Design-Filtering-Sorting-and-Pagination/ ?

[lukehesluke]

@nickevansuk sure I'm in favour of that

• We don't need to worry about the AND/OR logic as separate query operators for the same key (like startDate=gt:2018-...&startDate=lt:2018-...) as each operator will have its own key (e.g. startDate[gt]=2018-...&startDate[lt]=2018-...) We will use the natural query parameter logic of AND-ing separate query parameters and OR-ing repeated query parameters

Some examples to further flesh this out:

• startDate
  • startDate[lt]=2018-...
  • startDate[gte]=2018-...
  • startDate[time-lt]=21:30Z
  • startDate[time-gte]=18:00Z
• ageRange.minValue[gte]=18
• location.geo[radial]=51.5,-0.28,30
  • unfortunately, this was a toss up between:
    1. three separate query params with more complex conditional logic - all three fields must be present
    2. or one query param with more complex parsing logic - comma splitting the three values
  • Am more in favour of #2 as it's easier to specify in a spec like OpenAPI / Swagger

[lukehesluke]

Actually should startDate be subEvent.startDate or subEvent[0].startDate?

[lukehesluke]

The square brackets does get in the way of indexing arrays by number

[lukehesluke]

subEvent.0.startDate

subEvent.-1.startDate

[nickevansuk]

This all looks excellent! And agree on #2, for simplicity of having one param (easier to reason about in terms of dev UX too)

One final thing we probably should check, how do different frameworks/languages interpret the square brackets? E.g. what does PHP do with it (i seem to recall it contructs foo[]=1&foo[]=2 into an array).

Assuming that there's no likely implementation issues, then lgtm

[nickevansuk]

sorry just saw your other posts: good point, i guess we're assuming subEvent.startDate is just returning those subEvents that match the startDate (and those Events that contain such subEvents)?

Not sure if we'd need to subEvent[0].startDate, as the ordering of items in an array in the spec is currently not meaningful/important (except for offers, but that's only de-facto not in the spec)?

Could subEvent[].startDate, or just assume it's never going to be a requirement and instead subEvent.startDate

Even with this logic it will be hard to represent "sort by junior price", as that's a property of one of the orders.prices? That's probably niche enough in both implementation and requirement to have its own operator though?

[lukehesluke]

@nickevansuk another remaining question is how we specify items in an array like for subEvent.startDate

• There is a clear purpose for a query like subEvent[-1].startDate[gte]=2018-... as "only include Events which have last occurrence later than time X" (assuming subEvents are ordered)
• There is also a clear purpose for a query like subEvent[0].startDate[lt]=2018... as "only include Events which have first occurrence greater than time X" (again - assuming subEvents are ordered)

[lukehesluke]

Ordering of items in subEvent is not important? I suppose I can see that for where subEvents can't strictly be sorted in time order (e.g. overlaps, multiple subEvents at the same time but with other differences)

[lukehesluke]

Maybe a special operator for this use case like:

• subEvent.startDate[latest-gte]=2018-...
• subEvent.startDate[earliest-lt]=2018-...

As we're trying to reduce the amount of ambiguity / guesswork

See next comment for a better idea

[lukehesluke]

Considering settling upon:

• subEvent.startDate[gte]=2018-...
• subEvent.startDate[lt]=2018-...

Where the explicit assumption is that, for arrays, the filter passes if any of the items in the array pass the filter

So in the first above case, this filter passes if ANY of the subEvent items have startDate greater than or equal to 2018-... (some datetime)

[lukehesluke]

@nickevansuk a conundrum:

startDate[time-gte]=23:30Z

During British Summer Time, 23:30Z is equivalent to 00:30 (BST). If the consumer of the opportunity API is a British app, making an API call during BST, they are likely filtering for sessions which, on any given day, start at or later than half past midnight local time (i.e. 00:30:00+01:00 -> 23:59:59+01:00). However, it is not clear to the API whether they mean 23:30:00Z -> 23:59:59Z or 23:30:00Z -> 22:59:59Z without knowing the consumer's understanding of local time

Given that this query parameter is going to be used to determine things like "get me all sessions in the morning" or "get me all sessions that start after I leave work", it would seem to me that this query parameter serves no value being timezone-aware and should instead expect a local time

This could be made a little more explicit by renaming the operator to:

startDate[local-time-gte]=00:30

[lukehesluke]

One option is having the consumer always specify the end time

This can be enforced by putting the from and to in the same operator e.g.

startDate[time-range]=23:30Z,23:00Z

The range will now have to be cyclical in order to support non-UTC timezones. The above range would give 23.5 hours

[lukehesluke]

You know what - this still doesn't work 😨

• Let's say we have a consumer Charlie and an API provider Annie
• Charlie and Annie are both UK organisations
• Charlie is an app and has a customer called Connie
• Connie wants to see a list of sessions in the 7 days from Thursday 25th October 2018 to Thursday 1st November 2018
• In 2018, British Summer Time ends on Sunday 28th October
• Connie is looking for something to do in the mornings, specifically: 6am to 9am (local time - Connie doesn't care about timezones when making this choice. Why should she?)
• If Charlie sends Connie's preferences to Annie as subEvent.startDate[time-range]=05:00Z,08:00Z, Annie will return sessions that fall within the desired 6am to 9am range from the 25th - 27th. Curiously, the sessions from 28th - 1st will fall within an undesired 5am - 8am range
• However, if Charlie sends Connie's preferences to Annie as subEvent.startDate[time-range]=06:00Z,09:00Z, Annie will return the wrong sessions for the first two days (in BST) and then the right sessions for the remaining days (in GMT) Charlie makes a query on sessions beginning

This problem is avoided only if time preferences are specified in local time.

This could either look like:

1.

    subEvent.startDate[local-time-gte]=06:00
    &subEvent.startDate[local-time-lte]=09:00

2.

    subEvent.startDate[time-gte]=06:00,Europe/London
    &subEvent.startDate[time-lte]=09:00,Europe/London

My personal preference is with option 1

[civsiv]

Number 1 for me too, timezone names are ambiguous and so best to just remove that ambiguity

[nickevansuk]

How does one know what local time is from an API call?

[lukehesluke]

@nickevansuk when you say "one", I'm going to assume you're referring to the API consumer.

Local time is a property of a place. Any sessions located in Great Britain will have local time Europe/London

[nickevansuk]

Sorry what I mean is subEvent.startDate[local-time-gte]=06:00 - what is 06:00? What is "local-time"? If feeds are presenting data in UTC (the current recommendation), how are we determining what 06:00 is in UTC for the query?

[nickevansuk]

Sorry I now understand, subEvent.startDate[local-time-gte]=06:00 is always considering the startDate in the local time relative to that event. Hence if you searched for an event in the Japan it would return events that are 6pm in Japan, as if you book a holiday to Japan and you are searching for a Yoga class from London, you would still expect results to appear in Japan local time for when you land there.

TLDR;LGTM :)