how to generate a map data structure dynamically in clickhouse

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 transform:

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.

Test this on tinybird

javisantana/til_dynamic_joinget.md