mysqlsqlclickhouseclickhouse-clientclickhouse-go

Clickhouse: Want to extract data from Array(Tupple) column in Clickhouse


Query used to create the table:

CREATE TABLE default.ntest2(job_name String, list_data Array(Tuple(s UInt64, e UInt64, name String))) ENGINE = MergeTree ORDER BY (job_name) SETTINGS index_granularity = 8192;

Table Data:

job_name list_data.s list_data.e list_data.name
job1 [19,22] [38,92] ['test1','test2']
job2 [28,63] [49,87] ['test3''test4']

Expected Output:

job_name list_data.s list_data.e list_data.name
job1 19 38 'test1'
job1 22 92 'test2'
job2 28 49 'test3'
job2 63 87 'test4'

How can I achieve this with less query time?


Solution

  • ARRAY JOIN https://clickhouse.com/docs/en/sql-reference/statements/select/array-join/

    SELECT
        job_name,
        `list_data.s`,
        `list_data.e`,
        `list_data.name`
    FROM
    (
        SELECT
            c1 AS job_name,
            c2 AS list_data
        FROM values(('job1', ([19, 22], [38, 92], ['test1', 'test2'])), ('job2', ([28, 63], [49, 87], ['test3', 'test4'])))
    ) AS T
    ARRAY JOIN
        list_data.1 AS `list_data.s`,
        list_data.2 AS `list_data.e`,
        list_data.3 AS `list_data.name`
    
    ┌─job_name─┬─list_data.s─┬─list_data.e─┬─list_data.name─┐
    │ job1     │          19 │          38 │ test1          │
    │ job1     │          22 │          92 │ test2          │
    │ job2     │          28 │          49 │ test3          │
    │ job2     │          63 │          87 │ test4          │
    └──────────┴─────────────┴─────────────┴────────────────┘
    
    SELECT
        job_name,
        list_data.s,
        list_data.e,
        list_data.name
    FROM
    (
        SELECT
            c1 AS job_name,
            c2 AS `list_data.s`,
            c3 AS `list_data.e`,
            c4 AS `list_data.name`
        FROM values(('job1', [19, 22], [38, 92], ['test1', 'test2']), ('job2', [28, 63], [49, 87], ['test3', 'test4']))
    ) AS T
    ARRAY JOIN
        `list_data.s` AS `list_data.s`,
        `list_data.e` AS `list_data.e`,
        `list_data.name` AS `list_data.name`
    
    ┌─job_name─┬─list_data.s─┬─list_data.e─┬─list_data.name─┐
    │ job1     │          19 │          38 │ test1          │
    │ job1     │          22 │          92 │ test2          │
    │ job2     │          28 │          49 │ test3          │
    │ job2     │          63 │          87 │ test4          │
    └──────────┴─────────────┴─────────────┴────────────────┘