clickhouseUsing ARRAY JOIN in queries
SELECT ts, list FROM tbl3 ARRAY JOIN listctrl + ctsdatetime column | listname of the array column to expand |
tbl3table with array column in it | ARRAY JOINwill duplicate each row for each element of array and return resulting flat table |
Usage example
SELECT * FROM tbl3;
SELECT ts, list FROM tbl3 ARRAY JOIN listoutput
┌──────────────────ts─┬─list──────────┐
│ 2022-01-14 17:41:32 │ ['a','b','c'] │
│ 2022-01-14 17:41:38 │ ['b','c','d'] │
└─────────────────────┴───────────────┘
┌──────────────────ts─┬─list─┐
│ 2022-01-14 17:41:32 │ a │
│ 2022-01-14 17:41:32 │ b │
│ 2022-01-14 17:41:32 │ c │
│ 2022-01-14 17:41:38 │ b │
│ 2022-01-14 17:41:38 │ c │
│ 2022-01-14 17:41:38 │ d │
└─────────────────────┴──────┘More of Clickhouse
- Convert array to rows
- How to execute SQL query from a file
- Get total database size
- How to restart Clickhouse server
- List all tables from all database
- Using variables in queries
- Select where date column is between dates
- Select sumIf query example
- Select countIf usage example
- Get table size
See more codes...