Prevent reordering after flattening a view

user_8952565 0
Viewed 38

My data look as follows

row | ID | string_array 
-------------------------
1   | abc | [ "0", "10", "0", "0" ]
2   | def | [ "0", "372", "0", "0"]

I want to flatten this view and write the values in string_array into distinct rows. Order is important, e.g. it makes a difference whether the "10" in the first row is on second or third order.

I do

SELECT ID,
       cast(f.value as integer) as values,
       row_number() OVER (ORDER BY ID) as n
FROM source,
     table(flatten(source.string_array)) f
;

The resulting view however looks like this:

ID | value | n
------------------------
abc | 0    | 1
abc | 0    | 2
abc | 0    | 3
abc | 10   | 4
def | 0    | 5
def | 0    | 6
def | 0    | 7
def | 372  | 8

I'd like to have the values (10, 372) at n=(2,6), respectively. How can this be done?

snowflake-cloud-data-platform sql
1 Answers

INDEX could be used to determine position of the element in the array:

SELECT ID,
       f.value::INTEGER AS val,
       f.INDEX,
       ROW_NUMBER() OVER(ORDER BY ID, f.INDEX) AS n
FROM source, TABLE(flatten(source.string_array)) f
ORDER BY ID, f.INDEX;
user_50708790
Related