I am building a query in Clickhouse that performs an array join in order to create a denormalized table for each record in the array. I would like to add a unique ID to each distinct record from that array, however I am unable to. Here is my query:
with base as (
select
t.id,
lst.*
from my_table t
array join my_list_column as lst
)
select
generateUUIDv4() as id,
base.*,
now64(9) as created_at
from base
The result of this is the same UUID across each row of the list.
How do I solve this?
EDIT: Adding a reproducible example.
create table test (
id Int,
lst Array(String)
)
ENGINE = MergeTree
ORDER BY id;
insert into test (id, lst) values (1, ['a', 'b', 'c']);
select
generateUUIDv4(),
*
from test
array join lst
Solution is as follows for the reproducible example:
select
generateUUIDv4(lst),
*
from test
array join lst