Clickhouse - Output each array item and its index/position

Question

In Clickhouse I have an array column. Can I output each value in the array and its corresponding index/position (+1) within?

So given ['c','b','a'] I would like to output

key        position
c          1
b          2
a          3

Denny Crane · Accepted Answer · 2020-06-19 16:23:10Z

4

select a,i from 
     (select  ['c','b','a'] arr)
      array join arr as a, arrayEnumerate(arr) as i
┌─a─┬─i─┐
│ c │ 1 │
│ b │ 2 │
│ a │ 3 │
└───┴───┘



select  (arrayJoin(arrayZip(arr, arrayEnumerate(arr))) as x).1 a, x.2 i
from 
     (select  ['c','b','a'] arr)
┌─a─┬─i─┐
│ c │ 1 │
│ b │ 2 │
│ a │ 3 │
└───┴───┘

edited Jun 19, 2020 at 16:23

answered Jun 19, 2020 at 16:11

Denny Crane

13.7k2 gold badges25 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

redsquare Over a year ago

Thanks very much Denis

Collectives™ on Stack Overflow

Clickhouse - Output each array item and its index/position

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related