How to search opensearch sorted by distance using sql plugin?

Question

We use SQL plugin for querying OpenSearch in order to simplify the queries (so many ANDs and ORs).

We are able to limit the results based on the given radius (which was quite hard to figure out), the SQL query looks like this:

SELECT name
FROM points_of_interest
WHERE status = 'active' AND GEO_DISTANCE(geometry, '10km', -123.4567, 49.3717)
LIMIT 5

Now we need to return results sorted by distance. If there is more than 5 results within 10km radius the order is basically random.

Tried different ORDER BY values but usually the OpenSearch wasn't even able to parse the query, e.g.:

SELECT name
FROM points_of_interest
WHERE status = 'active'
ORDER BY GEO_DISTANCE(geometry, -123.4567, 49.3717)
LIMIT 5

but getting an error:

QueryShardException[No mapping found for [GEO_DISTANCE(geometry,-123.4567,49.3717)] in order to sort on]

What I need is not documented but maybe it is possible (the GEO_DISTANCE for WHERE part was hard to find as well).

---

My current workaround is to:

• transform original SQL into OpenSearch DQL
• query "pure" OpenSearch with DQL that is modified with sth like this:

dql_explain = elastic.transport.perform_request('POST', '/_plugins/_sql/_explain', body={'query': sql_query})

r = elastic.search(
    index=INDEX_NAME,
    body={
        'query': dql_explain['query'],
        'sort': [{
            '_geo_distance': {
                'geometry': center,
                'order': 'asc',
            }
        }]
    },
)

Anyone OpenSearch / ElasticSearch -SQL experienced here?