Skip to content

ADR: Additional attribute in Sort query to filter the records

Additional attribute in Sort query to filter the records

Status

  • Proposed
  • Trialing
  • Under review
  • Approved
  • Retired

Context & Scope

The search service accepts sort query as of now where the caller can specify 2 attributes.

  • field : This is a list of fields to sort the results.

  • order : This is a list of orders to sort the results. This requires fix value as either ASC or DESC.

More can be read from this search documentation.

The SortQuery model supported in the search can be seen here.

Elasticsearch supports filter attribute as well inside the sort field to filter the objects inside nested path, please refer the ES documentation here.

In the absence of support for 'filter' attribute in OSDU search, we have a limitation to sort results of nested fields as mentioned in this issue.

Tradeoff Analysis

This will be a non-breaking change and a feature supported in Elasticsearch will be supported with OSDU search as well.

Decision

The /query and /query_with_cursor APIs in search service accepts a SortQuery model like below:

  SortQuery:
    type: object
    properties:
    field:
      type: array
      description: 'The list of fields to sort the results.'
      items:
        type: string
    order:
      type: array
      description: 'The list of orders to sort the results. The element must be either ASC or DESC.'
      items:
        type: string

The proposal is to add an optional new attribute called 'filter' in the SortQuery model in OSDU search service. The filter string passed by the caller will be passed to underneath Elasticsearch.

If the filter string is not passed in the incoming request, existing behavior shall be maintained.

The 'filter' string will be used to filter records while searching and sorting the records based on attributes inside nested objects.

  SortQuery:
    type: object
    properties:
    field:
      type: array
      description: 'The list of fields to sort the results.'
      items:
        type: string
    order:
      type: array
      description: 'The list of orders to sort the results. The element must be either ASC or DESC.'
      items:
        type: string
    filter:
      type: array
      description: 'A filter that the inner objects inside the nested path should match with, in order for its field values to be taken into account by sorting.'
      items:
        type: string

Below is a sample query to sort records of kind osdu:wks:master-data--Wellbore:1.0.0.

The sorting is based on the values from VerticalMeasurements array where the VerticalMeasurementTypeID matches with the value in filter.

{
  "kind": "osdu:wks:master-data--Wellbore:1.0.0",
  "sort": {
        "field": [
             "nested(data.VerticalMeasurements, VerticalMeasurement, min)"
        ],
        "order": [
            "ASC"
        ],
        "filter":["nested(data.VerticalMeasurements, VerticalMeasurementTypeID:\"tenant1:reference-data--VerticalMeasurementType:KB\", match)"]
    }
}

This query will be transformed into an elastic search query like below:

"sort" : [
       {
          "data.VerticalMeasurements.VerticalMeasurement" : {
             "mode" :  "min",
             "order" : "asc",
             "nested": {
                "path": "data.VerticalMeasurements",
                "filter": {
                   "match" : { "data.VerticalMeasurements.VerticalMeasurementTypeID" : "tenant1:reference-data--VerticalMeasurementType:KB" }
                }
             }
          }
       }
    ]

The path for the filter would be taken from the nested field inside the 'filter'.

Consequences

  • Change in core-common to update the SortQuery model.
  • Change in search-core to set filter in nested sort queries.
  • Search service documentation and Open API specs need to be updated.
Edited by Chad Leong