The rows I'm looking at have an array of ids, I would like to join to a table to retrieve the labels for those ids and include them in the result, without flattening the array.

Example

table: boxes

The rows I'm looking at have an array of ids, I would like to join to a table to retrieve the labels for those ids and include them in the result, without flattening the array.

Example

table: boxes

table: contents

query result

• sql
• snowflake-cloud-data-platform

• 1 Check this similar question stackoverflow/questions/75239830/… Answer uses static values but can be changed to check column values from boxes and contents join – demircioglu Commented 2 days ago

2 Answers 2

Since you mentioned you dont want to flatten, you can fetch the elements using the index, caveat is it would only work if the number of elements are static.

with box as 
(
select box_id, get(contents,0) ele_0,get(contents,1) ele_1,get(contents,2) ele_2 from boxes
)
select b.box_id,  ARRAY_AGG(c.label) AS contents
from box b
inner join contents c
on b.ele_0 = c.content_id 
or b.ele_1 = c.content_id 
or b.ele_2 = c.content_id 
GROUP BY b.box_id
order by b.box_id;

Output

You don't have to flatten the table the whole way, just include the flatten for the sake of the join, if you'd like.

SELECT
    boxes.box_id,
    ARRAY_AGG(contents.label)
FROM
    boxes,
    LATERAL FLATTEN(input => boxes.contents) AS item,
    contents
WHERE
    item.value = contents.content_id
GROUP BY boxes.box_id
ORDER BY boxes.box_id;

You can also order those elements in the array using ARRAY_AGG's fantastic WITHIN GROUP ORDER BY functionality.