In this post, I would like to share a way to rename fields in the TOPK output structure.
As you know, the function TOPK(sort_col, col0..., k) returns an array of STRUCT<sort_col, col0, col1, etc.>. To use the result in the future, it may not always be convenient to use these names.
But using the TRANSFORM function, it is possible to rename the fields of the structure as follows:
TRANSFORM(
TOPK(
x,
y,
z,
5
),
(res) => STRUCT(
x := res->"sort_col",
y := res->"col0",
z := res->"col1"
)
) AS top_xyz_array
This implementation can be used in the SELECT body.
If you need to get only the first record, then you can use:
TRANSFORM(
TOPK(
...
1
),
(res) => STRUCT(
...
)
)[1] AS top_xyz_struct
P.S: I was prompted to write this post by another message in which the correct reasoning was conducted. But the mistake was that the fields of the TOPK structure are named in lowercase, so they need to be wrapped in "".