Table-valued functions (TVFs) extend OpenSearch SQL with post-processing capabilities: grouping, ranking, sorting, and top-K filtering on search results.
Functions at a glance
| Function | Signature | What it does |
|---|---|---|
rankTvf | rankTvf("group_key", "sort_key", "reserved_count", (sql)) | Groups results by a key field, keeps the top N records per group, and preserves the original row order |
sortTvf | sortTvf("sort_key", "reserved_count", (sql)) | Returns the top K records sorted in the specified order |
topKTvf | topKTvf("sort_key", "reserved_count", (sql)) | Returns the top K records in an unordered result set |
enableShuffleTvf | enableShuffleTvf((sql)) | Routes SQL execution from the searcher to the query record searcher (QRS) |
rankTvf
rankTvf works like a SQL rank window function. It groups results by a key field, keeps the top N records per group, and removes the rest — while preserving the sort order of the original SQL result.
Syntax
rankTvf("group_key", "sort_key", "reserved_count", (sql))Parameters
| Parameter | Description |
|---|---|
group_key | The field used to group results. Accepts multiple comma-separated fields. Leave blank to skip grouping. |
sort_key | The field used to sort results within each group. Accepts multiple fields. Prefix with + for ascending order or - for descending order. Defaults to ascending. Cannot be blank. |
reserved_count | The number of records to keep per group. Set to a negative number to keep all records in each group. |
sql | The SQL statement whose results are grouped and filtered. |
Example
Keep the top 1 result per brand, sorted by size in descending order, then sort the final output by brand:
select * from table (
rankTvf('brand', '-size', '1', (SELECT brand, size FROM phone))
)
order by brand
limit 100sortTvf
sortTvf provides local top-K functionality. It sorts and truncates results at the searcher level before join operations, changing the row order of the original SQL result. If the query also includes an ORDER BY clause, that clause is pushed to the QRS for a global sort.
Syntax
sortTvf("sort_key", "reserved_count", (sql))Parameters
| Parameter | Description |
|---|---|
sort_key | The field used to sort results. Accepts multiple fields. Prefix with + for ascending order or - for descending order. Defaults to ascending. Cannot be blank. |
reserved_count | The number of records to keep. |
sql | The SQL statement whose results are sorted. |
Example
Return the top 3 phones by size in descending order:
select * from table (
sortTvf('-size', '3', (SELECT brand, size FROM phone))
)topKTvf
topKTvf provides local top-K functionality similar to sortTvf. The key difference: topKTvf returns an unordered result set. If the query includes an ORDER BY clause, that clause is pushed to the QRS for a global sort.
Syntax
topKTvf("sort_key", "reserved_count", (sql))Parameters
| Parameter | Description |
|---|---|
sort_key | The field used to select top-K records. Accepts multiple fields. Prefix with + for ascending order or - for descending order. Defaults to ascending. Cannot be blank. |
reserved_count | The number of records to keep. |
sql | The SQL statement whose results are filtered. |
Example
Get the top 3 phones by size (result order is not guaranteed):
select * from table (
topKTvf('-size', '3', (SELECT brand, size FROM phone))
)When to use sortTvf vs topKTvf
Both functions return local top-K results but differ in output ordering:
sortTvf | topKTvf | |
|---|---|---|
| Output order | Sorted (order is guaranteed) | Unordered |
| Use when | You need a sorted local result before a join | You only need the top-K records and order does not matter |
enableShuffleTvf
By default, SQL statements processed by rankTvf are pushed down to the searcher. Use enableShuffleTvf to route execution to the QRS instead.
Syntax
enableShuffleTvf((sql))Example
select * from table (
enableShuffleTvf((SELECT brand, size FROM phone))
)