Extension: select distinct

[hylkevds]

It is quite useful to give Entities common properties, like a "type". But when filtering on such a common property the client needs to know what the used values are. As it is, to find all the possible values for a field, the user either has to download all entities to find all values, or use a series of request, progressively excluding the values it already knows of.

It would be nice if we could do something like adding a distinct keyword to the $select request parameter, similar to what can be done in SQL. This would also require allowing $select to work on sub-properties. So $select=properties/type would not return the full properties object, but only the selected parts.

[mjacoby]

This seems like a valid use case to me. Regarding implementation the syntax must be clear and unambiguous even in cases where $select contains multiple fields, so maybe something like $select-distinct=... , ... or $select=distinct: ... , ...

NB This functionality does not seem to be present in OData but there seem to be a workaround using $apply and groupby EDIT forgot the link to an OData example https://stackoverflow.com/questions/3839073/select-distinct-values-with-odata/54357853#54357853

[hylkevds]

I think just adding the keyword as if it is a property would be fine:

$select=distinct,properties/type,properties/subtype

There is no field called distinct in any of our entities, so no ambiguity there. If multiple properties are in the select, then it will return all existing combinations of those properties, like SQL.

We could require the distinct keyword to be the first in the select-list...

This would return pseudo entities, since there are likely to be multiple entities that match each returned combination. As a result it can not be combined with $expand, but could be combined with all other query parameters ($top, $skip, $count, $filter) and could be used inside an expand:

v1.0/Things?$expand=Datastreams($select=distinct,properties/medium)

[mjacoby]

The detailed syntax can be up to discussion. Personally, I feel like using a different syntax/seperator than , may be clearer an ease (parser) implementation. As this functionality seems like a special case of grouping/aggregation, we might think about adopting the OData way of doing things (http://docs.oasis-open.org/odata/odata-data-aggregation-ext/v4.0/cs01/odata-data-aggregation-ext-v4.0-cs01.html), especially if we plan to ever bring STA and OData closer together in the future.

[hylkevds]

I've done a prototype implementation in FROST, for people that would like to play around with this:

https://lubw.k8s.ilt-dmz.iosb.fraunhofer.de/v1.1/Things?$select=distinct:properties/type https://lubw.k8s.ilt-dmz.iosb.fraunhofer.de/v1.1/Things?$select=distinct:properties/type,properties/subType https://lubw.k8s.ilt-dmz.iosb.fraunhofer.de/v1.1/Things?$select=id,name,properties/type