Sorting
A Query can specify how rows should be sorted in the response.
The requested ordering can be found in the order_by field of the Query object.
Computing the Ordering
To compute the ordering from the order_by field, data connectors should implement the following ordering between rows:
- Consider each element of the
order_by.elementsarray in turn. - For each
OrderByElement:- If
element.target.typeiscolumn, then to compare two rows, compare the value in the selected column. See typecolumnbelow. - If
element.target.typeisaggregate, compare two rows by comparing aggregates over a related collection. See typeaggregatebelow.
- If
Type column
The property element.target.name refers to a column name.
If the connector supports capability query.nested_fields.order_by then the target may also reference nested fields within a column using the field_path property. If the column has arguments, the the arguments property is used to provide values for the arguments.
If element.order_direction is asc, then the row with the smaller column comes first.
If element.order_direction is asc, then the row with the smaller column comes second.
If the column values are incomparable, continue to the next OrderByElement.
The data connector should document, for each scalar type, a comparison function to use for any two values of that scalar type.
For example, a data connector might choose to use the obvious ordering for a scalar integer-valued type, but to use the database-given ordering for a string-valued type, based on a certain choice of collation.
For example, the following query requests that a collection of articles be ordered by title descending:
{
"collection": "articles",
"arguments": {},
"query": {
"fields": {
"id": {
"type": "column",
"column": "id"
},
"title": {
"type": "column",
"column": "title"
}
},
"order_by": {
"elements": [
{
"target": {
"type": "column",
"name": "title",
"path": []
},
"order_direction": "desc"
}
]
}
},
"collection_relationships": {}
}
The selected column can be chosen from a related collection by specifying the path property. path consists of a list of named relationships.
For example, this query sorts articles by their author's last names, and then by their first names, by traversing the relationship from articles to authors:
{
"collection": "articles",
"arguments": {},
"query": {
"fields": {
"id": {
"type": "column",
"column": "id"
},
"title": {
"type": "column",
"column": "title"
},
"author": {
"type": "relationship",
"arguments": {},
"relationship": "article_author",
"query": {
"fields": {
"first_name": {
"type": "column",
"column": "first_name"
},
"last_name": {
"type": "column",
"column": "last_name"
}
}
}
}
},
"order_by": {
"elements": [
{
"target": {
"type": "column",
"name": "last_name",
"path": [
{
"arguments": {},
"relationship": "article_author",
"predicate": {
"type": "and",
"expressions": []
}
}
]
},
"order_direction": "asc"
},
{
"target": {
"type": "column",
"name": "first_name",
"path": [
{
"arguments": {},
"relationship": "article_author",
"predicate": {
"type": "and",
"expressions": []
}
}
]
},
"order_direction": "asc"
}
]
}
},
"collection_relationships": {
"article_author": {
"arguments": {},
"column_mapping": {
"author_id": ["id"]
},
"relationship_type": "object",
"source_collection_or_type": "article",
"target_collection": "authors"
}
}
}
Nested relationships
If the connector enables the relationships.nested.ordering capability, it may receive path relationships where the relationship starts from inside a nested object. The path to descend through the nested objects before navigating the relationship is specified by the field_path property.
For example, this query sorts institutions by their location's country's area. The relationship starts from within the location nested object and joins its country_id column to the countries collection's id column.
{
"collection": "institutions",
"arguments": {},
"query": {
"fields": {
"name": {
"type": "column",
"column": "name"
},
"location": {
"type": "column",
"column": "location",
"fields": {
"type": "object",
"fields": {
"country_id": {
"type": "column",
"column": "country_id"
},
"country": {
"type": "relationship",
"arguments": {},
"relationship": "location_country",
"query": {
"fields": {
"id": {
"type": "column",
"column": "id"
},
"name": {
"type": "column",
"column": "name"
},
"area_km2": {
"type": "column",
"column": "area_km2"
}
}
}
}
}
}
}
},
"order_by": {
"elements": [
{
"order_direction": "desc",
"target": {
"type": "column",
"path": [
{
"field_path": ["location"],
"relationship": "location_country",
"arguments": {},
"predicate": null
}
],
"name": "area_km2",
"field_path": []
}
}
]
}
},
"collection_relationships": {
"location_country": {
"arguments": {},
"column_mapping": {
"country_id": ["id"]
},
"relationship_type": "object",
"target_collection": "countries"
}
}
}
Type aggregate
An ordering of type aggregate orders rows by aggregating rows in some related collection, and comparing aggregations for each of the two rows. The relationship path is specified by the path property. Connectors must enable the relationships.order_by_aggregate capability to receive this ordering type.
If the respective aggregates are incomparable, the ordering should continue to the next OrderByElement.
If the connector enables the relationships.nested.ordering capability, it may receive path relationships where the relationship starts from inside a nested object. The path to descend through the nested objects before navigating the relationship is specified by the field_path property.
Examples
For example, this query sorts article authors by their total article count:
{
"collection": "authors",
"arguments": {},
"query": {
"fields": {
"first_name": {
"type": "column",
"column": "first_name"
},
"last_name": {
"type": "column",
"column": "last_name"
},
"articles_aggregate": {
"type": "relationship",
"arguments": {},
"relationship": "author_articles",
"query": {
"aggregates": {
"count": {
"type": "star_count"
}
}
}
}
},
"order_by": {
"elements": [
{
"order_direction": "desc",
"target": {
"type": "aggregate",
"aggregate": {
"type": "star_count"
},
"path": [
{
"arguments": {},
"relationship": "author_articles",
"predicate": {
"type": "and",
"expressions": []
}
}
]
}
}
]
}
},
"collection_relationships": {
"author_articles": {
"arguments": {},
"column_mapping": {
"id": ["author_id"]
},
"relationship_type": "array",
"source_collection_or_type": "author",
"target_collection": "articles"
}
}
}
This query sorts article authors by their maximum article ID:
{
"collection": "authors",
"arguments": {},
"query": {
"fields": {
"first_name": {
"type": "column",
"column": "first_name"
},
"last_name": {
"type": "column",
"column": "last_name"
},
"articles_aggregate": {
"type": "relationship",
"arguments": {},
"relationship": "author_articles",
"query": {
"aggregates": {
"max_id": {
"type": "single_column",
"column": "id",
"function": "max"
}
}
}
}
},
"order_by": {
"elements": [
{
"order_direction": "asc",
"target": {
"type": "aggregate",
"aggregate": {
"type": "single_column",
"column": "id",
"function": "max"
},
"path": [
{
"arguments": {},
"relationship": "author_articles",
"predicate": {
"type": "and",
"expressions": []
}
}
]
}
}
]
}
},
"collection_relationships": {
"author_articles": {
"arguments": {},
"column_mapping": {
"id": ["author_id"]
},
"relationship_type": "array",
"source_collection_or_type": "author",
"target_collection": "articles"
}
}
}
This query sorts institutions first by those institutions that are in countries that have the most institutions in them, then by the institutions' name. This example navigates the nested relationship that begins in the location nested object and joins back onto the institutions collection, targeting the nested location.country_id property.
{
"collection": "institutions",
"arguments": {},
"query": {
"fields": {
"name": {
"type": "column",
"column": "name"
},
"location": {
"type": "column",
"column": "location",
"fields": {
"type": "object",
"fields": {
"country_id": {
"type": "column",
"column": "country_id"
},
"country": {
"type": "relationship",
"arguments": {},
"relationship": "location_institution_location_country",
"query": {
"fields": {
"name": {
"type": "column",
"column": "name"
},
"location": {
"type": "column",
"column": "location"
}
}
}
}
}
}
}
},
"order_by": {
"elements": [
{
"order_direction": "desc",
"target": {
"type": "aggregate",
"path": [
{
"field_path": ["location"],
"relationship": "location_institution_location_country",
"arguments": {},
"predicate": null
}
],
"aggregate": {
"type": "star_count"
}
}
},
{
"order_direction": "desc",
"target": {
"type": "column",
"name": "name",
"path": []
}
}
]
}
},
"collection_relationships": {
"location_institution_location_country": {
"arguments": {},
"column_mapping": {
"country_id": ["location", "country_id"]
},
"relationship_type": "array",
"target_collection": "institutions"
}
}
}
Requirements
- Rows in the response should be ordered according to the algorithm described above.
- The
order_byfield should not affect the set of collection which are returned, except for their order. - If the
order_byfield is not provided then rows should be returned in an unspecified but deterministic order. For example, an implementation might choose to return rows in the order of their primary key or creation timestamp by default.
See also
- Type
OrderBy - Type
OrderByElement - Type
OrderByTarget