Lateral Flatten two columns with different array length in snowflake

Question

i am new to snowflake and currently learning to use Lateral Flatten.

I currently have a dummy table which looks like this:

The data type used for "Customer_Number" & "Cities" is array.

I have managed to understand and apply the Flatten concept to explode the data using the following sql statement:

select c.customer_id, c.last_name, f.value as cust_num, f1.value as city
    from customers as c,
    lateral flatten(input => c.customer_number) f,
    lateral flatten(input => c.cities) f1
    where f.index = f1.index
    order by customer_id;

The output shown is:

As we can clearly see from the dummy table, in row 4 customer_id 104 has 3 numbers and i would like to see all three of it in my output and if there is no matching index value in cities i would like to just see "Null" in "City".

My expected output is: Is this possible to be done ?

Answer 1

The trick is to remove the second lateral, and use the index from the first to choose values from the second array:

  select c.customer_id, c.last_name, f.value as cust_num, cites[f.index] as city
    from customers as c,
    lateral flatten(input => c.customer_number) f
    order by customer_id;

Answer 2

As long as you can be sure the second record is going to be shorter, you can do:

select customer_id, last_name, list1_table.value::varchar as customer_number, 
split(cities,',')[list1_table.index]::varchar as city
from customers, lateral flatten(input=>split(customer_number, ',')) list1_table;

Otherwise you'd have to do union between the 2 sets of records (a regular union will eliminate duplicates)

Answer 3

You may want to use a LEFT OUTER JOIN for this task, but need to create a rowset version of the cities first.

select c.customer_id, c.last_name, f.value as cust_num, f1.value as city
    from customers as c
    cross join lateral flatten(input => c.customer_number) f
    left outer join (select * from customers, lateral flatten(input => cities)) f1
                 on f.index = f1.index
    order by customer_id;