replace Array of Ids in clickhouse with information from another table in clickhouse

Question

how can I query a column with arrays and replace the array with a value from a corresponding table? In below example, i would want to replace the people column by an array of string names instead of id's

CREATE TEMPORARY TABLE somestats
(
    not_unique String,
    people Array(UInt32)
);

INSERT INTO somestats (not_unique, people) VALUES
('FUNEVENT', [1, 2, 3]),
('FUNEVENT', [1, 3]),
('OTHEREVENT', []);


CREATE TEMPORARY TABLE person
(
    id UInt32,
    name String
);

INSERT INTO person (id, name) VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Doe');

resulting query should output e.g.

(1, ["John", "Jane", "Doe"]),
(2, ["John", "Doe"]),
(3, []);

the solution should ideally use ArrayMap and not need a group by clause.

Answer 1

Consider using Dictionaries to access person by useful way:

CREATE DICTIONARY persons /*ON CLUSTER _name_*/
(
    id UInt64,
    name String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(host 'localhost' port 9000 db 'default' table 'person' user 'default'))
LAYOUT(FLAT())
LIFETIME(MIN 3600 MAX 3660);


SELECT
    *,
    arrayMap(x -> dictGetOrDefault('persons', 'name', toUInt64(x), '?'), people) AS names
FROM somestats

/*
┌─not_unique─┬─people──┬─names─────────────────┐
│ FUNEVENT   │ [1,2,3] │ ['John','Jane','Doe'] │
│ FUNEVENT   │ [1,3]   │ ['John','Doe']        │
│ OTHEREVENT │ []      │ []                    │
└────────────┴─────────┴───────────────────────┘
*/

Answer 2

Try this:

WITH a as
(
SELECT 
  groupArray(id) as ids
, groupArray(name) as names
FROM VALUES
(
'id UInt32, name String',
(1, 'John'),
(2, 'Jane'),
(3, 'Doe')
) as person
)
SELECT 
  s.*
, arrayFilter((n, i) -> has(s.people, i), a.names, a.ids) as n_arr
FROM VALUES 
(
'not_unique String, people Array(UInt32)',
('FUNEVENT', [1, 2, 3]),
('FUNEVENT', [1, 3]),
('OTHEREVENT', [])
) s, a

The result is:

|not_unique|people |n_arr                |
|----------|-------|---------------------|
|FUNEVENT  |[1,2,3]|['John','Jane','Doe']|
|FUNEVENT  |[1,3]  |['John','Doe']       |
|OTHEREVENT|[]     |[]                   |