SQL presto - cross join unnest with ordinality flattening

Why is my query not flattening the data as expected?

I am querying a table where columns contain arrays. My goal is to unnest the items in the arrays to find unique items and turn those into rows.

SELECT table1.tag_names, table1.tag_ids, rank_position FROM table1 CROSS JOIN UNNEST (tag_ids, tag_names) WITH ORDINALITY as T (tag_ids, tag_names, rank_position) ORDER BY tag_ids 

Results:

tag_namestag_idsrank_position
["red", "blue", "green"][111, 222, 333]1
["red", "blue", "yellow"][111, 222, 444]4

Desired Results:

tag_namestag_idsrank_position
"red"1111
"blue"2222
"green"3333
"yellow"4444

What am I missing?

Updated to use alias, new results below:

tag_namestag_idsrank_position
"red"1111
"red"11110
"red"1113
"red"11112
"yellow"4444

1 Answer

You should use alias introduced for the flattened data in the CROSS JOIN UNNEST in the select:

-- sample data WITH dataset (tag_names, tag_ids) AS ( VALUES (array['red', 'blue', 'green'], array[111, 222, 444]) ) -- query select T.tag_names, T.tag_ids, rank_position from dataset CROSS JOIN UNNEST (tag_ids, tag_names) WITH ORDINALITY as T (tag_ids, tag_names, rank_position) ORDER BY tag_ids 

Output:

tag_namestag_idsrank_position
red1111
blue2222
green4443

UPD

ORDINALITY does not work across multiple rows, one way to achieve desired result is to flatten the arrays, then use group by and row_number:

-- sample data WITH dataset (tag_names, tag_ids) AS ( VALUES (array['red', 'blue', 'green'], array[111, 222, 333]), (array['red', 'blue', 'yellow'], array[111, 222, 444]) ) -- query select *, row_number() over (order by tag_ids) rank_position from ( select T.tag_names, T.tag_ids from dataset CROSS JOIN UNNEST (tag_ids, tag_names) AS T (tag_ids, tag_names) GROUP BY T.tag_names, T.tag_ids ) ORDER BY tag_ids 

Output:

tag_namestag_idsrank_position
red1111
blue2222
green3333
yellow4444
2

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobmpwZ2uAcoWOrKilZaCnsrTAzmaaq6ejqHqru8inZK6mnprAtXnWoquhZZ%2Bnsaq6wKWgrbFdm7miwNOepaKmlw%3D%3D