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

Question

Query used to create the table:

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

Table Data:

job_name	list_data
job1	[[(1,2,'name1')],[(4,5,'name2')]]
job2	[[(22,33,'name3')],[(44,55,'name4')]]

Expected Output:

job_name	list_data.s	list_data.e	list_data.name
job1	1	2	'name1'
job1	4	5	'name2'
job2	22	33	'name3'
job2	44	55	'name4'

How can I achieve this with less query time?

Answer 1

use clickhouse array join

SELECT job_name, element.s, element.e, element.name
FROM default.ntest2
ARRAY JOIN element IN list_data