Lateral flatten two columns without repetition in snowflake

Question

I have a query that groups by a two variables to get a total of another. In order to maintain my table structure for later computations I listagg() two other variables to save for the next stage of the query. However, when I attempt to do two later flatten's of the listagg() columns my data is repeated to many times.

Example: my_table

   id   |     list1       | code|   list2  | total
--------|-----------------|-----|----------|---
2434166 | 735,768,769,746 | 124 | 21,2,1,6 | 30


select
id,
list1_table.value::int as list1_val,
code,
list2.value::int as list2_val,
total

from my_table
lateral flatten(input=>split(list1, ',')) list1_table,
lateral flatten(input=>split(list2, ',')) list2_table

Result:

   id   |     list1       | code|   list2  | total
--------|-----------------|-----|----------|---
2434166 |      768        | 124 |     2    | 30
2434166 |      735        | 124 |     2    | 30
2434166 |      746        | 124 |     2    | 30
2434166 |      769        | 124 |     2    | 30
2434166 |      768        | 124 |     21   | 30
2434166 |      735        | 124 |     21   | 30
2434166 |      746        | 124 |     21   | 30
2434166 |      769        | 124 |     21   | 30
2434166 |      768        | 124 |     6    | 30
2434166 |      735        | 124 |     6    | 30
2434166 |      746        | 124 |     6    | 30
2434166 |      769        | 124 |     6    | 30
2434166 |      768        | 124 |     1    | 30
2434166 |      735        | 124 |     1    | 30
2434166 |      746        | 124 |     1    | 30
2434166 |      769        | 124 |     1    | 30

I understand what is going on but I'm just wonder how do I get my desired result:

   id   |     list1       | code|   list2  | total
--------|-----------------|-----|----------|---
2434166 |      768        | 124 |     2    | 30
2434166 |      735        | 124 |     21   | 30
2434166 |      746        | 124 |     6    | 30
2434166 |      769        | 124 |     1    | 30

Answer 1

As you noticed yourself, you want 4 records. There are 2 ways to do it, both exploit the index column produced by flatten, which represents the position of the produced value in the input (see the Flatten Documentation)

Using 2 flattens and index-selection

First way is to take the result of your query, and add these index column, here's an example:

select id,
list1_table.value::int as list1_val, list1_table.index as list1_index, code,
list2_table.value::int as list2_val, list2_table.index as list2_index, total
from my_table,
lateral flatten(input=>split(list1, ',')) list1_table,
lateral flatten(input=>split(list2, ',')) list2_table;
---------+-----------+-------------+------+-----------+-------------+-------+
   ID    | LIST1_VAL | LIST1_INDEX | CODE | LIST2_VAL | LIST2_INDEX | TOTAL |
---------+-----------+-------------+------+-----------+-------------+-------+
 2434166 | 735       | 0           | 124  | 21        | 0           | 30    |
 2434166 | 735       | 0           | 124  | 2         | 1           | 30    |
 2434166 | 735       | 0           | 124  | 1         | 2           | 30    |
 2434166 | 735       | 0           | 124  | 6         | 3           | 30    |
 2434166 | 768       | 1           | 124  | 21        | 0           | 30    |
 2434166 | 768       | 1           | 124  | 2         | 1           | 30    |
 2434166 | 768       | 1           | 124  | 1         | 2           | 30    |
 2434166 | 768       | 1           | 124  | 6         | 3           | 30    |
 2434166 | 769       | 2           | 124  | 21        | 0           | 30    |
 2434166 | 769       | 2           | 124  | 2         | 1           | 30    |
 2434166 | 769       | 2           | 124  | 1         | 2           | 30    |
 2434166 | 769       | 2           | 124  | 6         | 3           | 30    |
 2434166 | 746       | 3           | 124  | 21        | 0           | 30    |
 2434166 | 746       | 3           | 124  | 2         | 1           | 30    |
 2434166 | 746       | 3           | 124  | 1         | 2           | 30    |
 2434166 | 746       | 3           | 124  | 6         | 3           | 30    |
---------+-----------+-------------+------+-----------+-------------+-------+

As you can see, the rows you are interested are the ones with the same index.

So to get your result by selecting these rows after the lateral joins happen:

select id,
list1_table.value::int as list1_val, code,
list2_table.value::int as list2_val, total
from my_table,
lateral flatten(input=>split(list1, ',')) list1_table,
lateral flatten(input=>split(list2, ',')) list2_table 
where list1_table.index = list2_table.index;
---------+-----------+------+-----------+-------+
   ID    | LIST1_VAL | CODE | LIST2_VAL | TOTAL |
---------+-----------+------+-----------+-------+
 2434166 | 735       | 124  | 21        | 30    |
 2434166 | 768       | 124  | 2         | 30    |
 2434166 | 769       | 124  | 1         | 30    |
 2434166 | 746       | 124  | 6         | 30    |
---------+-----------+------+-----------+-------+

Using 1 flatten + lookup-by-index

An easier, more efficient, and more flexible way (useful if you have multiple arrays like that or e.g. array indices are related but not 1-to-1) is to flatten only on one array, and then use the index of the produced elements to lookup values in other arrays.

Here's an example:

select id, list1_table.value::int as list1_val, code, 
split(list2,',')[list1_table.index]::int as list2_val,  -- array lookup here 
total
from my_table, lateral flatten(input=>split(list1, ',')) list1_table;
---------+-----------+------+-----------+-------+
   ID    | LIST1_VAL | CODE | LIST2_VAL | TOTAL |
---------+-----------+------+-----------+-------+
 2434166 | 735       | 124  | 21        | 30    |
 2434166 | 768       | 124  | 2         | 30    |
 2434166 | 769       | 124  | 1         | 30    |
 2434166 | 746       | 124  | 6         | 30    |
---------+-----------+------+-----------+-------+

See how we simply use the index produced when flattening list1 to lookup the value from list2

Answer 2

To get elements from multiple arrays with the same index we could use array accessor:

SELECT t.id, t.code, t.total, s.ind,
     STRTOK_TO_ARRAY(t.list1, ',')[s.ind]::int AS list1_val,
     STRTOK_TO_ARRAY(t.list2, ',')[s.ind]::int AS list2_val
FROM t
,(SELECT ROW_NUMBER() OVER(ORDER BY seq4()) - 1 AS ind 
  FROM TABLE(GENERATOR(ROWCOUNT => 10))) s  -- here up to 10 elements
WHERE list1_val IS NOT NULL
ORDER BY t.id, s.ind;

The idea is to generate tally numbers and then access the elements.

Sample data:

CREATE OR REPLACE TABLE t(id INT, list1 TEXT, code INT, list2 TEXT, total INT) AS
SELECT 6, '735,768,769,746', 124, '21,2,1,6', 30 UNION
SELECT 7, '1,2,3'          , 1,   '10,20,30', 50;