Skip to main content

Ordering

orderBy sorts the entities a query returns. You can sort by an attribute's value, by one of the entity's own members, or by distance from a point. Without orderBy, entities come back in identifier order.

Ordering a query​

Name one or more terms in orderBy, separated by commas. Each term is a member or an attribute path, optionally followed by ; and a direction. The directions are asc, which is the default, desc, dist-asc and dist-desc.

GET /ngsi-ld/v1/entities?type=Building&orderBy=category,height;desc

The terms apply in order. The result follows category, and among buildings in the same category the tallest comes first. Entities that still tie come back in identifier order.

An entity without the attribute comes last, whichever direction you pick. So orderBy=name and orderBy=name;desc both put the unnamed entities at the end. The specification puts it as "null values last" (clause 4.23.2), and Andromeda keeps that promise in both directions.

Terms​

A term can be one of the following.

  • id, type, scope, createdAt or modifiedAt, the entity's own members.
  • An attribute name, such as height.
  • A path into an attribute, written as in q. name.observedAt sorts by a sub-attribute, address[city] by a member of a structured value, and sensor.rawdata[particulate] by both.
  • A LanguageProperty with a language, such as label[en], which sorts by the English string.

A path takes up to 32 segments after the attribute name, the same depth an attribute may nest to (see Query language). name.createdAt sorts by when the name attribute was created, not by when the entity was. speed.unitCode sorts by the text of the code, so HM comes before KMH. It is compared as a String under the request's collation, the same text a q term compares. An entity whose speed has no unitCode sorts as if the attribute didn't exist.

Ordering a POST query​

POST /ngsi-ld/v1/entityOperations/query takes the same ordering in the ordering member of the Query body.

{
    "type": "Query",
    "entities": [
        {
            "type": "Building"
        }
    ],
    "ordering": {
        "orderBy": [
            "category",
            "height;desc"
        ],
        "collation": "de-u-co-phonebk"
    }
}

orderBy is required in the ordering member. An ordering that holds only a collation or a reference geometry is refused.

How values compare​

Numbers compare by value and date-times as instants. Strings compare by collation. A Relationship sorts by the URI of its target, as text.

When entities hold values of different kinds, the kind decides before the value does. The kinds sort in this order.

  1. numbers
  2. strings
  3. objects
  4. arrays
  5. booleans
  6. Time
  7. Date
  8. DateTime
  9. entities without the attribute

A Time, Date or DateTime only counts as a time when it is declared as one (see Values). An untyped string such as "12:00:00Z" sorts with the strings, by its text. A string declared as an XSD number sorts with the numbers, and one declared as xsd:boolean with the booleans (see Values). Among the numbers, -INF comes first and INF after every finite number, followed by NaN.

An entity without a scope comes after every entity with one, the same way a missing attribute comes last.

Rare cases
  • An entity sorts by one value per term. For an attribute with several datasetId instances, that is the instance written first, whatever its datasetId. An instance with a datasetId counts even when the entity has no default instance.
  • An array sorts by its first element.
  • A JsonProperty member is addressed by its key as written, so blob[k] reads the member k of the raw JSON.
  • orderBy=id sorts identifiers by collation, like any string. That isn't the same as the identifier order you get without orderBy, which compares bytes. Under the root collation, urn:ngsi-ld:Room:aB sorts before urn:ngsi-ld:Room:Ab.
  • With the Target languages in q extension, a term can name a full tag such as label[en-GB], but never a range.

Collation​

Strings sort by the ICU root collation, und-x-icu, unless you name another with collation. The root order compares letters before it looks at case and accents. So alpha comes before Bravo, and Schäfer sits between Schafer and Schaffer.

To pick another collation, name it as a BCP 47 tag, with the settings of RFC 6067 in a -u- extension.

GET /ngsi-ld/v1/entities?type=Person&orderBy=name&collation=de-u-co-phonebk

The German phonebook order reads ä as ae, so Schäfer comes first, before Schafer. Other settings change the order in other ways.

  • und-u-kn-true sorts digits as numbers, so Room2 comes before Room10.
  • und-u-ks-level1 ignores case and accents.
  • und-u-ks-identic takes every difference into account.

A collation only changes how strings are sorted. Without orderBy, it changes nothing.

Andromeda accepts only the settings RFC 6067 registers for collation. ICU silently replaces an unregistered key with its default, so a misspelt setting would sort your strings in an order you never asked for. Andromeda refuses the query instead.

Andromeda names the collation in every query that orders strings, so the answer never depends on the server's default. The storage layer follows the same rule, and so do subscriptions evaluated in memory.

Rare cases
  • The first query that names a collation creates it in the tenant's database, once. Later queries reuse it.
  • A collation and its settings are checked when the query is read. A locale ICU doesn't know, such as zz-ZZ, is refused when Andromeda first tries to create it.

Distance​

dist-asc and dist-desc sort by distance from a reference geometry. You give that geometry in orderFrom, as GeoJSON coordinates, and a Point is the default.

GET /ngsi-ld/v1/entities?type=Shop&orderBy=location;dist-asc&orderFrom=[13.40,52.52]

For another shape, name its type in orderGeometry, such as LineString. Distance is then measured from the nearest point of the line. A GeometryCollection isn't allowed.

orderFrom is always required for a distance term, even when the query has a geoquery. The geoquery decides which entities match, and orderFrom decides where distance is measured from. The two can name different places.

  • Distance is measured horizontally. Altitude doesn't count, in the reference or in the entity.
  • A GeoProperty with several datasetId instances is measured by its nearest instance.
  • GeoProperties sort first, nearest or farthest first by direction. Other values follow, by value.
  • Entities without the attribute come last.
  • A distance term measures the GeoProperty it names, so observationPoint;dist-asc ignores location.
  • A distance term can follow other terms. orderBy=name,location;dist-asc sorts by name, then by distance where names tie.

Where you can order​

  • Query Entities, by GET or POST, and the queries of a Snapshot take any term, as long as no context source registration takes part in the answer (see Distributed operations).
  • Query Temporal Evolution of Entities takes only id, ascending or descending, under the query's collation.

An ordered query pages by offset, so its links carry offset rather than a cursor (see Pagination). That has a cost. If an entity is created or deleted during the walk, an offset walk can skip or repeat entities, so take an ordered answer as a snapshot of one moment, not a stable stream.

What gets refused​

  • 400 BadRequestData for a distance term without orderFrom. A geoquery's geometry doesn't stand in for it.
  • 400 BadRequestData for orderBy on a query a registration takes part in. Send local=true, or leave orderBy out.
  • 400 BadRequestData for a malformed term, such as an unknown direction, a dangling dot, an unclosed or repeated bracket, the [*] of q, or a language range.
  • 400 BadRequestData for a term that follows a Relationship. rel{name} is a q construct, not an ordering term.
  • 400 BadRequestData for a collation that isn't one. Every -u- key and value must be one RFC 6067 registers for collation.
  • 400 BadRequestData for a -u- key that states no collation, such as de-u-nu-latn or de-u-ca-buddhist.
  • 400 BadRequestData for orderGeometry without orderFrom, a GeometryCollection, or coordinates that don't fit the type.
  • 400 BadRequestData for an orderBy other than id on a temporal query, or a distance term there.
  • 400 BadRequestData for cursor with orderBy. Ordered queries page by offset, so leave the cursor out.

When a registration takes part in the answer, an ordered query like this one is refused.

GET /ngsi-ld/v1/entities?type=Building&orderBy=height;desc
HTTP/1.1 400 Bad Request
Content-Type: application/json

{
    "type": "https://uri.etsi.org/ngsi-ld/errors/BadRequestData",
    "status": 400
}

Where Andromeda interprets the specification​

Entity ordering is defined by the specification. The grammar is the Entity Ordering Language, whose AttrName is the attribute path.

und-x-icu is the default string order, and distance sorting is limited to GeoProperties. The datatype order puts "null values last" and GeoProperties ahead of other values in a distance sort. Andromeda breaks the remaining ties by id, because the specification asks for a consistent order across pages.

orderFrom is mandatory for a distance ordering, which is why a geoquery's geometry is never used instead. orderBy is mandatory in an ordering member, and orderGeometry defaults to Point.

The specification names "a specific ICU collation order as defined by IETF RFC 6067", and refuses a collation setting that doesn't conform. RFC 6067 registers its keywords per purpose, so a keyword that isn't a collation setting doesn't conform.

An ordering is refused when the operation isn't limited to the local scope, and "Sort ordering is never applied to distributed operations". A temporal query is limited to ordering by id, and any other member is refused.

The specification doesn't say how two values of one kind compare when the kind is an object or an array. Andromeda sorts an array by its first element. It also leaves open which instance of a multi-valued attribute counts towards the sort, and Andromeda uses the one written first.

Last updated on