Filtering (#160)
Filtering
Often, when listing resources it is desirable to filter over the collection and
only return results that the user is interested in.
It is tempting to define a structure to handle the precise filtering needs for
each API. However, filtering requirements evolve frequently, and therefore it is
prudent to use a string field with a structured syntax accessible to a
non-technical audience. This allows updates to be able to be made transparently,
without waiting for UI or client updates.
Guidance
APIs should provide filtering to users when listing resources. If they choose
to do so, they should follow the specification discussed here.
When employing filtering, a request should have field-specific filters in the
form of ?filter[field]=value.
The list of fields a given list endpoint supports must be documented in that
endpoint's OAS. When an unsupported field is included in a filter request, the
response should be 400 Bad Request and the unsupported field listed in
invalid_parameters.
Boolean and null value matching are implemented using the string literals
true, false and null, and may only be applied to equal and
not equal filters:
?filter[is_admin]=true?filter[closed]=false?filter[name][neq]=null
Matching
The following operators should be available on all "list" endpoints that
support filtering, unless there are product or technical reasons not to.
Note: In the event a qualifier is omitted; e.g., ?filter[field]=value, it
should be assumed the filter will perform a exact equality match.
Equal
An equality filter returns records that exactly match the given value.
For string fields, APIs should prefer case-insensitive match. Any fields
that are case-sensitive must be explicitly stated so in their OAS.
Syntax: ?filter[field][eq]=value or ?filter[field]=value
Data types: string, number, datetime
Example: The filter ?filter[name]=Foo%20User would return records with
a namethat matched Foo User.
Not Equal
An inequality filter returns records that do not match the given value.
For string fields, APIs should prefer case-insensitive comparisons. Any fields
that are case-sensitive must be explicitly stated so in their OAS.
If a neq filter is provided, records with a null value for that field
must also be returned in the response.
Syntax: ?filter[field][neq]=value
Data types: string, number, datetime
Or Equal
An or-equal filter returns records that match any of the values in the
given list. For string fields, APIs should prefer case-insensitive match.
Note that commas cannot be provided in value, because , is a reserved
character for the oeq operator.
Syntax: ?filter[field][oeq]=value1,value2,value3
Data types: string, number, datetime
Example: The filter ?filter[city][oeq]=San%20Francisco,London would
return records where the city is San Francisco or London.
Contains
A containment filter returns records that contain the given string, even
if they do not match it exactly.
Syntax: ?filter[field][contains]=value
Data types: string
Example: The filter ?filter[email][contains]=@konghq.com would return
records with an email containing @konghq.com.
Or Contains
An or-containment filter returns records that contain one of the given
strings, even if they do not match exactly. Note that commas cannot be
provided in value, because , is a reserved character for the
ocontains operator.
Syntax: ?filter[field][ocontains]=value1,value2,value3
Data types: string
Example: The filter ?filter[email][contains]=smith,jones would return
records with an email containing either "smith" or "jones".
Mathematical Comparison Operators
The following filters apply to numeric or datetime fields and return
records that match the criteria.
- Less Than:
?filter[field][lt]=value
- Less Than or Equal:
?filter[field][lte]=value
- Greater Than:
?filter[field][gt]=value
- Greater Than or Equal:
?filter[field][gte]=value
Examples
The following examples assume an unfiltered response of:
{
"data": [
{
"name": "Bruce Wayne",
"preferred_name": "Batman",
"age": 83,
"created_time": "1939-03-30T07:20:50.52Z"
},
{
"name": "Thomas Wayne",
"preferred_name": "Dad",
"age": 52,
"created_time": "1939-05-30T07:20:50.52Z",
"deleted_time": "1939-11-37T07:20:50.52Z"
}
]
}
retrieved from a SQL database.
Single Filter
With a single filter parameter, ?filter[name][contains]=Bruce, the expected
results are:
{
"data": [
{
"name": "Bruce Wayne",
"preferred_name": "Batman",
"age": 83,
"created_time": "1939-03-30T07:20:50.52Z"
}
]
}
The underlying query:
SELECT * FROM users WHERE name LIKE '%Bruce%
With a single filter parameter, ?filter[name]=Bruce%20Wayne, the expected
results are the same:
{
"data": [
{
"name": "Bruce Wayne",
"preferred_name": "Batman",
"age": 83,
"created_time": "1939-03-30T07:20:50.52Z"
}
]
}
The underlying query:
SELECT * FROM users WHERE name = 'Bruce Wayne
Multiple Filters
When multiple filter parameters are present, the result set must contain
records that match all of the parameters. For example, given the query string
?filter[name][contains]=Wayne&filter[preferred_name]=Dad, the expected
results are:
{
"data": [
{
"name": "Thomas Wayne",
"preferred_name": "Dad",
"age": 52,
"created_time": "1939-05-30T07:20:50.52Z",
"deleted_time": "1939-11-37T07:20:50.52Z"
}
]
}
The underlying query:
SELECT * FROM users WHERE name LIKE '%Wayne%' AND preferred_name='Dad'
When one of the filters is an "or" filter (oeq or ocontains), the
query must be built to evaluate all of that filter's values.
Example: The filter ?filter[city][oeq]=Toronto,Paris&filter[name]=Bob
could be implemented with either of the following queries:
WHERE (city = 'Toronto' OR city = 'Paris') AND name = 'Bob'
or
WHERE city in ('Toronto', 'Paris') AND name = 'Bob'
Key Existence Filter
In order to verify a key in the schema is present, we can provide the following
filter parameters, ?filter[deleted_time]&filter[name][contains]=Wayne, the
expected results are:
{
"data": [
{
"name": "Thomas Wayne",
"preferred_name": "Dad",
"age": 52,
"created_time": "1939-05-30T07:20:50.52Z",
"deleted_time": "1939-11-37T07:20:50.52Z"
}
]
}
The underlying query:
SELECT * FROM users WHERE name LIKE '%Wayne%' AND deleted_time IS NOT NULL
Mix of Standard and Number Filters
To filter based on number equivalence, we can provide the following
filter parameters,
?filter[name]=Thomas%20Wayne&filter[age][lt]=60&filter[deleted_time],
the expected results are:
{
"data": [
{
"name": "Thomas Wayne",
"preferred_name": "Dad",
"age": 52,
"created_time": "1939-05-30T07:20:50.52Z",
"deleted_time": "1939-11-37T07:20:50.52Z"
}
]
}
The underlying query:
SELECT * FROM users WHERE name = 'Thomas Wayne' AND age < 60 AND deleted_time IS NOT NULL
Everything Together
All together now,
?filter[name][contains]=Wayne&filter[age][gt]=60&filter[created_time][lt]=1939-04-30T07:20:50.52Z,
the expected results are:
{
"data": [
{
"name": "Bruce Wayne",
"preferred_name": "Batman",
"age": 83,
"created_time": "1939-03-30T07:20:50.52Z"
}
]
}
The underlying query:
SELECT * FROM users WHERE name LIKE '%Wayne' AND age > 60 AND created_time < '1939-04-30T07:20:50.52Z'
Filtering Labels
Given the following entities:
{
"data": [
{
"name": "entity_one",
"labels": {
"key_1": "val_A",
"key_2": "val_B",
"key_3": "val_C"
}
},
{
"name": "entity_two",
"labels": {
"key_2": "val_D",
"key_3": "val_E",
"key_4": "val_F"
}
}
]
}
- Exact match on single label:
?filter[labels.key_1][eq]=val_A returns entity_one
- Partial match on a single label:
?filter[labels.key_2][contains]=E returns entity_two
- Partial match on a single label:
?filter[labels.key_2][contains]=e returns entity_two
- Multiple match on a single label:
?filter[labels.key_3][oeq]=val_C,val_E returns entity_one and entity_two
- Existence of a label:
?filter[labels.key_4] returns entity_two
- Match on multiple labels:
?filter[labels.key_1]=val_A&filter[labels.key_2]=val_B returns entity_one
[!NOTE]
The . is a valid character in the label key. Therefore, when filtering on labels, only the first . can be treated as a delimiter.
Limitations
While intentional to separate result filtering with searching against a given
resource, the feature set provided with basic filtering may not match the
expectations from customers until a dedicated search service, or endpoint, can
be provided.