Clickhouse has a powerful feature, JOIN engines, that allows to prepare a table to be joined with better performance that a regular table (MergeTree, Log...). It also allows to use
joinGet to get table values using a key.
Somtimes you don't have a JOIN table but you'd like to use something with the joinGet performance. Unfortunately you can't use joinGet with something created on the fly (well, you could create a temporally join table but you need several SQL queries).
So there is a way to do that, using
with ( select (groupArray(key), groupArray(value)) from my_table ) as key_value select transform(column, key_value.1, key_value.2) from other_table
it's not as fast as joinGet but it's pretty fast.
There is also a ongoing PR (as I type this) that adds a Map type that migth be useful as well.