populate a Nested column with data from another table

[Ofer Fort]

Hi all, how can I populate a nested column based on another table?

For example fill test_v1.user_attributes with data from test_attributes_v1? 

The list of optional attributes can be a few thousands but each user will have about a hundred.

I would like to later be able to query for user that have a specific combination of attributes, like hair_color:green AND voice:soprano

CREATE TABLE test_v1
(
    userID String,
    created DateTime,
    user_attributes Nested(
        name LowCardinality(String),
        value LowCardinality(String)
    ),
    repos Nested(
        repoID String,
        language LowCardinality(String),
        repo_attributes Nested(
            name LowCardinality(String),
            value LowCardinality(String)
        )
    )
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created)
ORDER BY (userID, created)

CREATE TABLE test_attributes_v1
(
    userID String,
    created DateTime,
    name LowCardinality(String),
    value LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created)
ORDER BY (userID, created)

Thanks!

[Denis Zhuravlev]

https://stackoverflow.com/questions/55764093/updating-rows-with-values-from-another-table-in-clickhouse/55775479#55775479
https://stackoverflow.com/questions/63287790/set-array-value-at-specific-index-in-clickhouse