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

Question

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?

Denny Crane · Accepted Answer · 2023-01-31T19:41:13.690

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          │
└──────────┴─────────────┴─────────────┴────────────────┘

Thanks Denny, It's working. Just wondering, can we do similar with Array(Nested(s UInt64, e UInt64, name String))) as well? table will look something like this: ┌─job_name─┬─list_data────────────────────────┐ │ job1 │ [[(1,2,'name1')],[(4,5,'name2')]] │ │ job2 │ [[(22,33,'name3')],[(44,55,'name4')]] │ │ job5 │ [[(22,33,'name3'),(44,55,'name4')]] │ — Yash Chauhan, Jan 31 '23 at 19:47
Do you understand Nested is Array already? Basically you are asking about `Array(Array(s UInt64, e UInt64, name String)))`. Provide CREATE TABLE / INSERT, I will provide SELECT. — Denny Crane, Jan 31 '23 at 19:58
All details are mentioned in this question. can you please refer to this? https://stackoverflow.com/questions/75302499/clickhouse-want-to-extract-data-from-arraynested-column-in-clickhouse — Yash Chauhan, Jan 31 '23 at 20:02
my question remains: Do you understand Nested is Array already? — Denny Crane, Feb 01 '23 at 00:29
yes, a nested data structure is like a table inside a cell. Info from the website: "For an INSERT query, you should pass all the component column arrays of a nested data structure separately (as if they were individual column arrays). During insertion, the system checks that they have the same length." for more information: https://clickhouse.com/docs/en/sql-reference/data-types/nested-data-structures/nested/ — Yash Chauhan, Feb 01 '23 at 06:12

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

1 Answers1