0

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?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Yash Chauhan
  • 174
  • 13
  • Best answer is honestly to not use array columns in the first place. They are inherently slow and error-prone. The expected output is how the data should be created in the first place. – Joel Coehoorn Jan 31 '23 at 20:02
  • use clickhouse array join https://clickhouse.com/docs/en/sql-reference/statements/select/array-join/ – Horaciux Jan 31 '23 at 20:09

1 Answers1

2

use clickhouse array join

SELECT job_name, element.s, element.e, element.name
FROM default.ntest2
ARRAY JOIN element IN list_data
Horaciux
  • 6,322
  • 2
  • 22
  • 41