Search code examples
sqlclickhouse

generating duplicate unique IDs in clickhouse array join

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

  • Solution is as follows for the reproducible example:

    select
    generateUUIDv4(lst),
    *
from test
array join lst